View original (French)

Automatic division of a column by 1000

Solved
PAtrick -  
 PAtrick -
Hello,

I often need to divide numbers by 1000. Generally, I use the special paste menu, but it takes time.
Therefore, I would like to create a shortcut in the top bar of Excel so I can automatically divide by 1000 any cells of my choice?

Thank you for your help

Configuration: Windows Vista / Internet Explorer 7.0

3 answers

  1.
    eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello,

    Do you really want to divide by 1000?
    If so, and if special paste bores you, it will be in VBA.

    Otherwise, if it's for display purposes and you can keep your values, you can create a custom format: a space after the format divides by 1000 (2 spaces for 1,000,000, etc.)
    You can also add the unit in the format, for example:
    0 " k€"

    If you prefer a macro, please specify it in the next post.

    Eric
    1
    1. PAtrick
       
      Thank you for your response.
      Yes, if it's possible to create a macro, that's perfect. But I don't know how to do it...

      Thanks in advance.
      0
  2. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Re,

    A macro to put in the sheet or in a module: 
    Sub div1000() Dim c As Range If MsgBox("Division by 1000 of the selected cells?", vbYesNo + vbExclamation, "WARNING") = vbYes Then For Each c In Selection If IsNumeric(c) Then c = c / 1000 Next c End If End Sub


    Using the same template, you can create one that multiplies by 1000 in case of an accident.
    In a macro, there is no undo in Excel.

    eric
    1