Increment Alpha Character

[Dan]

All,

I have a macro which sends data out to a .csv file. My
problem is that I need each line
of the .csv file to have a unique four digit alpha
character starting with AAAA and working all the way to
ZZZZ. I have no clue how to do this.

 

[Jake Marx]

Hi Dan,

I have a macro which sends data out to a .csv file. My
problem is that I need each line
of the .csv file to have a unique four digit alpha
character starting with AAAA and working all the way to
ZZZZ. I have no clue how to do this.

Probably not the most efficient way, but this method should work to generate
the strings from AAAA through ZZZZ:

Sub test()
Dim anBase26(1 To 4) As Integer

anBase26(1) = 0
anBase26(2) = 0
anBase26(3) = 0
anBase26(4) = 0

Do While anBase26(1) <= 25
Debug.Print Chr$(anBase26(1) + 65) & _
Chr$(anBase26(2) + 65) & _
Chr$(anBase26(3) + 65) & _
Chr$(anBase26(4) + 65)
If anBase26(4) < 25 Then
anBase26(4) = anBase26(4) + 1
Else
anBase26(4) = 0
If anBase26(3) < 25 Then
anBase26(3) = anBase26(3) + 1
Else
anBase26(3) = 0
If anBase26(2) < 25 Then
anBase26(2) = anBase26(2) + 1
Else
anBase26(2) = 0
anBase26(1) = anBase26(1) + 1
End If
End If
End If
Loop
End Sub


--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Bernie Deitrick]

Dan,

You can create a string that you can use in your code, as below:

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim myName As String

For i = 1 To 26
For j = 1 To 26
For k = 1 To 26
For l = 1 To 26
myName = Chr(i + 64) & _
Chr(j + 64) & _
Chr(k + 64) & _
Chr(l + 64)
'Then use myName in your code
MsgBox myName
Next l
Next k
Next j
Next i
End Sub

 

[Bernie Deitrick]

Dan,

If you have a counter, then it may be easier if you use a function like
this:

Dim myName As String

'For Counter Values of 0 To 456975
myName = FileName(Counter)

Function FileName(inVal As Double) As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer

i = (inVal Mod 26) + 1
j = Int((inVal Mod 676) / 26) + 1
k = Int((inVal Mod 17576) / 676) + 1
l = Int((inVal Mod 456976) / 17576) + 1

FileName = Chr(l + 64) & _
Chr(k + 64) & _
Chr(j + 64) & _
Chr(i + 64)

End Function

HTH,
Bernie
MS Excel MVP

 

[Dan]

They worked.

Thank You for responding.