How to autofill excel with an alphabetic sequence (aaa, aab, etc..

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want excel to auttofill an alphabetic sequence. AAA, AAB, AAC, etc. all
the way to ZZZ.
 
dogisnuts said:
I want excel to auttofill an alphabetic sequence. AAA, AAB, AAC, etc. all
the way to ZZZ.
Click to expand...

I think it would be easier to use a macro. This one will fill the range
A1:A17576 with the sequence you want,. You can easily modify it if you want
to use a different range.

Public Sub Autofill()
Dim A As Long
Dim B As Long
Dim C As Long
Dim s As String
Dim row As Long

For A = 65 To 90
For B = 65 To 90
For C = 65 To 90
row = row + 1
s = Chr(A) & Chr(B) & Chr(C)
Range("A" & CStr(row)).Value = s
Next C
Next B
Next A
End Sub

/Fredrik
 
You need a macro to do this. E.g.,

Sub AAA()

Dim Ndx1 As Integer
Dim Ndx2 As Integer
Dim Ndx3 As Integer
Dim Dest As Range
Set Dest = Range("A1")

For Ndx1 = Asc("A") To Asc("Z")
For Ndx2 = Asc("A") To Asc("Z")
For Ndx3 = Asc("A") To Asc("Z")
Dest.Value = Chr(Ndx1) & Chr(Ndx2) & Chr(Ndx3)
Set Dest = Dest(2, 1)
Next Ndx3
Next Ndx2
Next Ndx1
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



message
news:[email protected]...
 
Type the number 65 on cells A1, B1, and on cell C1

on cell A2 type
=IF(MOD(ROW()-1,676)=0,A1+1,A1)
on cell B2 type
=IF(MOD(ROW()-1,676)=0,65,IF(MOD(ROW()-1,26)=0,B1+1,B1))
on cell C2 type
=IF(MOD(ROW()-1,26)=0,65,C1+1)
on cell d2 type
=CHAR(A2)&CHAR(B2)&CHAR(C2)

And now copy the range A2:D2 and paste all the way through cells A17576 (the
total possible outcomes = 26 ^ 3 =17,576)

Column D is your result.
You can now copy and paste special -values - column D to eliminate the
formulas if you need to do so
 
Back
Top