VBA Dropping Leading Zero

[Tsmith25]

I have a string of test that I am trying to just copy out item numbers and such. Each item number has differing lengths and some of the item numbers have leading zeros. When I run the macro it is dropping some of the leading zeros off some of the items. I am sure it is the way I have written the macro, see blow.

Sub Button1_Click()



For x = 1 To 60000

A = Sheet2.Cells(1048576, 1).Value

If Sheet1.Cells(x, 2).Value > 1 And Sheet1.Cells(x, 3).Value > 1 And Sheet1.Cells(x, 4).Value > 1 And Sheet1.Cells(x, 2).Value <> "PART" And Sheet1.Cells(x, 2).Value <> "NO.#" Then


Sheet2.Cells(A + 1, 1).Value = Sheet1.Cells(x, 2).Value
Sheet2.Cells(A + 1, 2).Value = Sheet1.Cells(x, 3).Value
Sheet2.Cells(A + 1, 3).Value = Sheet1.Cells(x, 4).Value
Sheet2.Cells(A + 1, 4).Value = Sheet1.Cells(x, 5).Value

End If

Next x


End Sub

 

[Joe4]

Welcome to the Board!

If you want to copy it over, and not drop the leading zeroes, don't convert it to Value when copying, i.e.
change all references like:

Sheet2.Cells(A + 1, 1).Value = Sheet1.Cells(x, 2).Value

to

Sheet2.Cells(A + 1, 1) = Sheet1.Cells(x, 2)

 

[Tsmith25]

Ok, I will try that.

 

[Tsmith25]

That seemed to work, thank you!

 

[Joe4]

You are welcome.
Glad I was able to help.
The ".Value" coerces the text value to a numeric one, dropping the leading zeroes.

 

[joeu2004]

The ".Value" coerces the text value to a numeric one, dropping the leading zeroes.

Apparently so. But that highlights an idiosyncracy of VBA and an error in VBA documentation.

According to VBA documentation, the default member of a Range object is the Value property. So we wouldn't expect a difference.

But obviously, VBA does indeed treat them differently in some contexts.

(Not a complete surprise. I've noticed differences before. I just didn't expect __this__ particular difference.)