Search code examples
excelvbamergeleading-zero

Leading zeros won´t paste when merging columns VBA

I need to use information of two columns to build numbers as below:

-4 24296 -4.24296 -4 20320 -4.20320 -4 16356 -4.16356 -4 12410 -4.12410 -4 08500 -4.08500 -4 04619 -4.04619 -4 00739 -4.00739

This is the routine I´m using to merge the values:

Range("R3").Select

Do While ActiveCell <> ""
    ActiveCell.Offset(0, 1).FormulaR1C1 = _
        ActiveCell.Offset(0, -1) & "." & ActiveCell.Offset(0, 0)
    ActiveCell.Offset(1, 0).Select
Loop

The problem is that this routine is ignoring the leading zeros even though I made the program display them with:

Worksheets("Data").Columns("R").NumberFormat = "00000"

This is the output my program is giving me:

-4 24296 -4.24296 -4 20320 -4.20320 -4 16356 -4.16356 -4 12410 -4.12410 -4 08500 -4.85000 -4 04619 -4.46190 -4 00739 -4.73900

(Last three rows are wrong)

Can anyone help me solve this?

Thanks,

Solution

  • Instead of ActiveCell.Offset(0, -1) & "." & ActiveCell.Offset(0, 0) use this instead:

    ActiveCell.Offset(0, -1) - ActiveCell.Offset(0, 0)/100000