11

I can't seem to find a way to save leading zeros in my VBA code. The zeros are necessary since they correspond to unique IDs.

I've tried changing the number format to text and 0000.... in excel and the same approach in my actual code: ActiveSheet.Cells(i, j).NumberFormat = "00000"

Any other suggestions? (If I manually type these numbers into VBE it strips the leading zeros too). EDIT:

Sheets.Add.Name = "Temp"
Sheets("Sheet1").Select
ActiveSheet.Cells(2, 2).NumberFormat = "000"
cid = Cells(2, 2)
MsgBox cid
Sheets("Sheet2").Select
ActiveSheet.Cells(6, 1).NumberFormat = "00000"
sid = Cells(6, 1)
Sheets("Temp").Select
Url = _
"URL;" & _
"http......asp?" & _
"X1=" & cid & "&" & _
"X2=" & sid & "&"

This is inside a loop ultimately but I'm debugging as individual iterations.

programming_user1221
  • 113
  • 1
  • 1
  • 5

2 Answers2

12

All below deliberations were about writing to a cell, not reading from it. Changing the Numberformat doesn't work that way. See if you can figure out how this code works:

dim v as integer
v = val(Sheets("Sheet1").Cells(2, 2))
dim cid as string
cid = format(v, "000")

You should never use Select and ActiveSheet that way, it is not necessary; and never use Cells without the sheet it should be referring to.
(Val can be omitted if the cell is really already numeric and not text).

Earlier answer... :

This code works perfectly fine for me

Worksheets(1).Columns("A").NumberFormat = "@"
Worksheets(1).Cells(1, "A").Value = "00023"

And also does

Worksheets(1).Columns("A").NumberFormat = "000000"
Worksheets(1).Cells(1, "A").Value = "0023"

(Here the string is converted to a number and displayed with 6 digits)

EDIT:
If that fails - although I could not explain that - I'd still bet that the ' should really work:
(No numberformat required.)

Dim s As String
s = "0002"
Worksheets(1).Cells(1, "A").Value = "'" & s
user4035
  • 22,508
  • 11
  • 59
  • 94
KekuSemau
  • 6,830
  • 4
  • 24
  • 34
0

Setting the format to text or "00000" should work. The small snippet below gives the results expected. With the first storing a value of 0001 and the second storing a value of 1 that is formatted to look like 0001.

This also works if the cell value was stored in a string variable.

Sub set_value()

Range("A1").NumberFormat = "@"
Range("A1").Value2 = "0001"

Range("B1").NumberFormat = "0000"
Range("B1").Value2 = "1"

End Sub
gtwebb
  • 2,981
  • 3
  • 13
  • 22