Formula To Increment Alphabetical Letters ?

[Aitch]

I use this to drag down alphabetical letters:
=CHAR(CODE(A1)+1)
It works, but only up to Z!

What do I use to continue the sequence through to double letters like this:
AA
AB
AC
AD

Thanks for your help!

 

[alansidman]

Maybe.

Option Explicit


Sub AlphaFill()
    Dim rn As Integer
    Dim cn As String
    cn = Application.InputBox(prompt:="Which column to fill")
    rn = Application.InputBox(prompt:="How many lines do you wish to fill?")
    Range(cn & "1:" & cn & rn).Formula = "=SUBSTITUTE(ADDRESS(1,ROW(),4),""1"","""")"
End Sub

 

[jtakw]

Hi,

Use this formula copied down.

=SUBSTITUTE(ADDRESS(1,ROWS($1:1),4),1,"")

 

[Rick Rothstein]

Give this formula a try...

=SUBSTITUTE(ADDRESS(1,ROWS($1:27),4),1,"")

 

[Aitch]

Awesome, much appreciated!

Is there a way to modify the formula so that it uses the first cell at the top as a reference?

So if I change the top cell, all the cells below change automatically

 

[alansidman]

Have you tried my VBA solution?

 

[Aitch]

Have you tried my VBA solution?

I have no idea how to use VBA bro - I literally only started learning this stuff starting this year haha!

I can only cope with single cell formulas, for the time being anyway...

 

[Tetra201]

... Is there a way to modify the formula so that it uses the first cell at the top as a reference?..

Is this what you need?

=SUBSTITUTE(ADDRESS(1,COLUMN(INDIRECT(A1&1))+1,4),1,"")

 

[alansidman]

Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

 

[Aitch]

Is this what you need?

=SUBSTITUTE(ADDRESS(1,COLUMN(INDIRECT(A1&1))+1,4),1,"")

Perfect! Thank you bro!