Padding with 0's overflow

[okrob]

This sub works to add 1 to an invoice #, complete with padding 0's,
but when I get to invoice # C99999, I hit an overflow error. Can I
make the formatting floating but with a minimum of 5 places?
Rob

Sub test()
Dim x As String
Dim y As Integer
Dim pref As String
x = Sheet1.[A1].Value
y = Evaluate(Mid(x, 2, Len(x) - 1))
y = y + 1
x = "C" & Format(y, "00000")
Sheet1.[A1].Value = x
End Sub

 

[JE McGimpsey]

The maximum integer is 32767. You can use the routine to generate
numbers from 32768-99999 if you Dim y as a Long

 

[Tom Ogilvy]

y = 99999
? Format(y, "#00000")
99999
y = 100000
? Format(y, "#00000")
100000

 

[okrob]

y = 99999
? Format(y, "#00000")
99999
y = 100000
? Format(y, "#00000")
100000
--
Regards,
Tom Ogilvy

This sub works to add 1 to an invoice #, complete with padding 0's,
but when I get to invoice # C99999, I hit an overflow error. Can I
make the formatting floating but with a minimum of 5 places?
Rob

Sub test()
Dim x As String
Dim y As Integer
Dim pref As String
x = Sheet1.[A1].Value
y = Evaluate(Mid(x, 2, Len(x) - 1))
y = y + 1
x = "C" & Format(y, "00000")
Sheet1.[A1].Value = x
End Sub- Hide quoted text -

- Show quoted text -

Thanks once again Tom...

 

[Tom Ogilvy]

JE was a bit more astute than me in recognizing the fact you were using an
integer rather than a long. Make sure you read his post.

--
Regards,
Tom Ogilvy

y = 99999
? Format(y, "#00000")
99999
y = 100000
? Format(y, "#00000")
100000
--
Regards,
Tom Ogilvy

This sub works to add 1 to an invoice #, complete with padding 0's,
but when I get to invoice # C99999, I hit an overflow error. Can I
make the formatting floating but with a minimum of 5 places?
Rob

Sub test()
Dim x As String
Dim y As Integer
Dim pref As String
x = Sheet1.[A1].Value
y = Evaluate(Mid(x, 2, Len(x) - 1))
y = y + 1
x = "C" & Format(y, "00000")
Sheet1.[A1].Value = x
End Sub- Hide quoted text -

- Show quoted text -

Thanks once again Tom...

 

[OKROB]

JE was a bit more astute than me in recognizing the fact you were using an
integer rather than a long. Make sure you read his post.

--
Regards,
Tom Ogilvy

y = 99999
? Format(y, "#00000")
99999
y = 100000
? Format(y, "#00000")
100000
--
Regards,
Tom Ogilvy
:
This sub works to add 1 to an invoice #, complete with padding 0's,
but when I get to invoice # C99999, I hit an overflow error. Can I
make the formatting floating but with a minimum of 5 places?
Rob
Sub test()
Dim x As String
Dim y As Integer
Dim pref As String
x = Sheet1.[A1].Value
y = Evaluate(Mid(x, 2, Len(x) - 1))
y = y + 1
x = "C" & Format(y, "00000")
Sheet1.[A1].Value = x
End Sub- Hide quoted text -
- Show quoted text -

Thanks once again Tom...- Hide quoted text -

- Show quoted text -

I did actually change it to long...