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

On Mar 23, 1:19 pm, Tom Ogilvy
wrote:
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



"okrob" wrote:
On Mar 23, 9:41 am, Tom Ogilvy
wrote:
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- Hide quoted text -

- Show quoted text -

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

- Show quoted text -

I did actually change it to long...