Formula To Increment Alphabetical Letters ?

Aitch

Board Regular
Joined
Jan 27, 2019
Messages
119
Office Version
  1. 2010
Platform
  1. Windows
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!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Maybe.

Code:
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
 
Upvote 0
Hi,

Use this formula copied down.

=SUBSTITUTE(ADDRESS(1,ROWS($1:1),4),1,"")
 
Last edited:
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top