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

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

In article . com,
"okrob" wrote:

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

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



"okrob" wrote:

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