Generate a six digit Number & increase by 1

[kbsudhir]

Hi All,

I want to add a request# to the subject line whenever I receive a mail.
For That I want to initiate it by generating a 6digit number lets say "000001" & then store the same in a database so that I can add 1 to it the previous number.

But the problem is how to make the number as "A00001" when I reach "999999" similarly make it "B00001" when I reach "A99999".

Please guide.

Regards
Sudhir

[kbsudhir]

Is it possible to increment the alphabets like "A" to "B"....???

[mdmackillop]

Put 0-9, A-Z in an array and append it to a 5 digit number as and when required.
Here's a simplified Excel example

Sub Numbers()
Dim num As String, i As Long, j As Long, k As Long
Columns(1).NumberFormat = "@"
arr = Array(0, 1, 2, 3, "A", "B", "C")
For i = 0 To 6
For j = 0 To 999
num = CStr(arr(i) & Format(j, "000"))
k = k + 1
Cells(k, 1) = num
Next
Next
End Sub