Excel VBA adding 2 cells with leading zeros

[longhair]

I have a vba macro in excel that takes the contens of a cell from column "B" and adds it to the contents of column "C" (puts it in a cell in sheet2) based on the contents of a cell in column "A" & "B". The issue is that the contents of column "C" my be prefaced with leading zeros ie - "001" or "085" etc. Column "B" is a six digit lot number & column "C" is always a 3 digit counter to produce a 9 digit number. The problem is that my VBA code is truncating any leading zeros. I think this may be because I have the cells set to a custom format of "000" for column "C" (had to do this to get the leading 0's to appear. Can anyone suggest a solution? My code is at work but I can provide on Monday if need be.

Regards,

longhair

 

[rmikesmith]

longhair,

The problem is that you want to combine two numeric strings but Excel is treating them as numbers. The following uses the Text property to read the cells in column B & C as text strings and the concatenation operator (&) to combine them:

Sub CombineNumericStrings()
  With Worksheets("Sheet1")
    Worksheets("Sheet2").Cells(1, 1).Value = .Cells(1, 2).Text & .Cells(1, 3).Text
  End With
End Sub

Obviously, this is just an example and only operates on cells B1 and C1. Just incorporate the technique into your VBA routine. You could alternatively use the Value property if the columns were formatted as Text prior to entering the data. If you are copying the counter values (001, 085, etc.) from another source, however, this probably won't work, assuming the source is also treating them as numbers.

HTH
M. Smith

 

[sfvb]

If I unsderstand this correctly, you could just multiply column B by 1000 then add column C. This will give you the nine digit number without any type conversions, and without losing the leading zeroes in column C.