Autofill AAAA-AAAB etc

[Peter621]

Hi one and all. I'm new to Excel, so fairly inept with formula etc. I had done a search of questions asked, and found a question that nearly but not quite hit the point. The question posed by 'cmendes on May 18th 2011. What I'm trying to do is to populate cell A1 with AAAA, then autofill (vertically) with AAAB all the way to ZZZZ. Why, I here you ask, I'm trying to set up a spread sheet of all UK aircraft registrations from AAAA-ZZZZ.
MTIA
Peter

 

[Eric W]

If you just want a native formula, put this in A1 and copy it down to A456976:

=CHAR(65+INT((ROW()-1)/26^3))&CHAR(65+MOD(INT((ROW()-1)/26^2),26))&CHAR(65+MOD((ROW()-1)/26,26))&CHAR(65+MOD(ROW()-1,26))


Or a bit more succinctly if you have Excel 365:

=CONCAT(CHAR(65+MOD(INT((ROW()-1)/26^{3,2,1,0}),26)))

 

[Peter621]

Hi to you all.
Sorry for delay in replying, I was away for the day. My smacked is gobbed! Brilliant, all working wonderfully, I owe you all a pint.
Now all I have to do is learn to understand code and formula, it's like hieroglyphics to me. Any literature or on-line tutorials you would recommend?
Would there be a simple way of putting a blank row at A1, I've tried 'insert row', but that then auto populates to 'AAAA', reason being, I'd like to put in column headers.
Thanks again.
Regards
Peter

 

[Fluff]

For my code simply make the change in red

Sub Peter621()
   Dim i As Long, j As Long, k As Long, l As Long, r As Long
   Dim Ary As Variant
   
   ReDim Ary(1 To 26 ^ 4, 1 To 1)
   For i = 65 To 90
      For j = 65 To 90
         For k = 65 To 90
            For l = 65 To 90
               r = r + 1
               Ary(r, 1) = Chr(i) & Chr(j) & Chr(k) & Chr(l)
            Next l
         Next k
      Next j
   Next i
   Range("[COLOR=#ff0000]A2[/COLOR]").Resize(r).Value = Ary
End Sub

To learn more about arrays have a look here
https://excelmacromastery.com/excel-vba-array/

 

[Eric W]

For my formulas, change ROW()-1 to ROW()-2 everywhere.

 

[Peter621]

Many thanks Eric & Fluff
Will implement later today.
Many thanks

 

[DanteAmor]

Hi to you all.
Sorry for delay in replying, I was away for the day. My smacked is gobbed! Brilliant, all working wonderfully, I owe you all a pint.
Now all I have to do is learn to understand code and formula, it's like hieroglyphics to me. Any literature or on-line tutorials you would recommend?
Would there be a simple way of putting a blank row at A1, I've tried 'insert row', but that then auto populates to 'AAAA', reason being, I'd like to put in column headers.
Thanks again.
Regards
Peter

In fact my macro in post #4 starts in A2