Set variable as cell text with leading zero

[Gimics]

Hello,

I am trying to set a variable to equal the contents of a cell that has to always have two digits, "00". I've formatted the cell as a number with two digits (cell formatting is 00), and have tried pulling the .text and or the .value of the cell, but the variable always ends up just equally the single digit (if the value is less than 10).

Code:

Sub Test

Dim GLMonth as Long
GLMonth = ThisWorkbook.Sheets("SLICE").Range("L17").Text

End Sub

I have L17 set at 9 right now (or 09, with formatting), but in my local window, GLMonth always ends up at just '9'. Thoughts? I need both digits as I intend to use the value for another lookup.

 

[Rick Rothstein]

Code:

Sub Test

Dim GLMonth as [COLOR=#FF0000][B]Long[/B][/COLOR]
GLMonth = ThisWorkbook.Sheets("SLICE").Range("L17").Text

End Sub

I have L17 set at 9 right now (or 09, with formatting), but in my local window, GLMonth always ends up at just '9'. Thoughts? I need both digits as I intend to use the value for another lookup.

Numbers, in and of themselves, have no format (9, 09, 009, 9.0, 9.00, etc. are all just the number 9), so when you declared GLMonth as Long, you made sure only the numerical value would be stored in it. If you declare that variable as String, then it will hold the characters you assign to it.

 

[Gimics]

then it will hold the characters you assign to it.

Hey Rick - the issue is that the 'characters I assign to it' will never have a leading zero (or I would have to be typing '0 in front of single digit text, and then removing the apostrophe, which is likely more difficult and requires user data entry consistency). Assuming GLMonth is a string, and L17 may only have one digit entered, is there a way to store it as two, with a leading zero?

 

[Rick Rothstein]

Hey Rick - the issue is that the 'characters I assign to it' will never have a leading zero (or I would have to be typing '0 in front of single digit text, and then removing the apostrophe, which is likely more difficult and requires user data entry consistency). Assuming GLMonth is a string, and L17 may only have one digit entered, is there a way to store it as two, with a leading zero?

Okay, I think this may be what you are looking for then...

Sub Test
  Dim GLMonth as [COLOR=#0000FF][B]String[/B][/COLOR]
  GLMonth = [B][COLOR=#0000ff]Format([/COLOR][/B]ThisWorkbook.Sheets("SLICE").Range("L17").Text[COLOR=#0000ff][B], "00")[/B][/COLOR]
End Sub

 

[Gimics]

So simple! There were a lot of posts in a lot of places that definitely overdid this.

Thanks Rick!