Change the value of all cells to a value with only 4 decimals RRS feed

  • Question

  • I have a datasheet with costs per unit, but some of those have over 10 decimals.

    eg. cost for 13 units = 1587€ => cost per unit = 122,0769230769231

    I have to copy the columns with the costs to another excel-sheet to read them into our ERP system.

    But, it can only work with max 5 decimals. If I enter numbers with more then 5 decimals, it blocks.

    I cannot make any changes in the formulas (it is password protected) 

    So once I have copied them into the sheet, I would like to run a macro to change the number of decimals in a specific range.

    I have to do this multiple times, so using the ROUND-function each time would take way too long.

    Thanks in advance

    Friday, September 18, 2015 1:52 PM

All replies

  • Hi

    You're not giving any specifics beside wanting 4 decimal so...

    This will format a range with 4 decimal. Adjust the range to your needs.

    Sub FormatCells()
        Range("C2:C1600").NumberFormat = "0.0000"
     End Sub

    Or if you have more then one range :

    Sub FormatCells()
            Range("C2:C16,D2:D16,G4:G60").NumberFormat = "0.0000"
    End Sub 


    Friday, September 18, 2015 3:11 PM