Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Autofill AAAA-AAAB etc

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Autofill AAAA-AAAB etc

    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

  2. #2
    Board Regular jkpieterse's Avatar
    Join Date
    Dec 2007
    Location
    Weert
    Posts
    900
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Autofill AAAA-AAAB etc

    You do know that means about half a million rows? This can be done using PowerQuery. Which Excel version are you using?
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  3. #3
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Autofill AAAA-AAAB etc

    Try this for results in column "A":-
    456976 Rows !!!!
    Code:
    Sub MG06Aug23
    Dim a As Long, b As Long, c As Long, d As Long, n As Long
    For a = 65 To 90
        For b = 65 To 90
            For c = 65 To 90
                For d = 65 To 90
                    n = n + 1
                    Cells(n, 1) = Chr(a) & Chr(b) & Chr(c) & Chr(d)
                Next d
            Next c
        Next b
    Next a
    End Sub
    Regards Mick

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,993
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Autofill AAAA-AAAB etc

    Try this, I think it is faster.

    Code:
    Sub Autofill_AAAA_AAAB()
      Dim i As Long, j As Long, k As Long, l As Long, n As Long
      Dim a()
      Application.ScreenUpdating = False
      n = 0
      Range("A:E").ClearContents
      For i = 1 To 26
        Range("B" & i & ":E" & i).Value = Evaluate("=SUBSTITUTE(ADDRESS(1," & i & ",4),""1"","""")")
      Next
      For i = 1 To 26
        For j = 1 To 26
          For k = 1 To 26
            For l = 1 To 26
              ReDim Preserve a(n)
              a(n) = Cells(i, 2) & Cells(j, 3) & Cells(k, 4) & Cells(l, 5)
              n = n + 1
            Next
          Next
        Next
        Range("A" & Rows.Count).End(xlUp)(2).Resize(n).Value = Application.Transpose(a)
        n = 0
        ReDim a(n)
      Next
      Range("B:E").ClearContents
      MsgBox "End"
    End Sub
    Last edited by DanteAmor; Aug 6th, 2019 at 11:39 AM.
    Regards Dante Amor

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Autofill AAAA-AAAB etc

    How about
    Code:
    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("A1").Resize(r).Value = Ary
    End Sub
    Beaten 2it
    Very similar to MickG' method
    Last edited by Fluff; Aug 6th, 2019 at 11:47 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,639
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Autofill AAAA-AAAB etc

    UDF?

    A
    B
    1
    AAAA A1: =ColLtr(ROW() + 18278)
    2
    AAAB
    3
    AAAC
    4
    AAAD
    5
    AAAE
    6
    AAAF
    7
    AAAG
    8
    AAAH
    9
    AAAI
    10
    AAAJ



    Code:
    Function ColLtr(ByVal iCol As Long) As String
      ' shg 2012
      ' Good for any positive Long
      If iCol > 0 Then ColLtr = ColLtr((iCol - 1) \ 26) & Chr(65 + (iCol - 1) Mod 26)
    End Function

  7. #7
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Autofill AAAA-AAAB etc

    Hi jkpieterse, thanks for your reply. Yes, I know there will be one or two rows, but then there are one or two aircraft on the British register. Excel 2013 is the version on my laptop.
    Regards
    Peter

  8. #8
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Re: Autofill AAAA-AAAB etc

    Hi to you all, and thank you for your reply.
    I see that your answers are a 'code' solution. Umm, so what do I do with it, so to speak?
    Regards
    Peter

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Autofill AAAA-AAAB etc

    Have a look here to see how to add macros to a project & run them
    https://www.ablebits.com/office-addi...a-macro-excel/
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,993
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Autofill AAAA-AAAB etc

    HOW TO INSTALL MACROs
    ------------------------------------
    If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (Autofill_AAAA_AAAB) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •