Formatting A Number In VBA

[Ark68]

I have the number 0 in cell O3 of my workksheet. I am trying to place that number into another cell in another workbook using the following VBA line of code. I wish for the number to take on the format of ##### in this cell. So the result for O3 = 0 would be "00000" in the destination cell. This line of code isn't formatting the value, it's displaying as simply 0.

.Range("J" & drow) = Format(ws_form.Range("O3"), "00000")

 

[HaHoBe]

Hi Ark68,

if the cell is formatted as General you just get a 0. What about

With .Range("J" & drow)
  .Value = Range("O3").Value
  .NumberFormat = "00000"
End With

as that would choose the proper format for the target cell.

Ciao,
Holger

 

[HaHoBe]

Hi Ark68,

the above code will display the value in the cell like 00000 while in the formula bar you would see only 0.

You can convert the number to text by using

.Range("J" & drow) = "'" & Format(ws_form.Range("O3"), "00000")

This would display 00000 with a green triangle in the cell and '00000 in the formula bar with ' referencing that it is a text.

Ciao,
Holger

 

[Ark68]

Great stuff Holger, thank you! I opted to use the second option. No calculations will