Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

Fill Serial - AA, AB,AC...,BA,BB,BC...ZZ

Posted by manoj on September 06, 2001 10:50 PM
Hi

I want to fill
AA
AB
AC
.
.
.
BA
BB
BC
.
.
.
CA
CB
CC

till ZZ

Typing AA in A1 & the next will be filled automatically by just dragging the cells

How can i do it?

Regards,
Manoj


Check out our Excel Resources

Re: Fill Serial - AA, AB,AC...,BA,BB,BC...ZZ

Posted by Juan Pablo on September 07, 2001 7:36 AM
Copy this formula in A2

=CHAR(IF(CODE(RIGHT(R[-1]C,1))=90,CODE(LEFT(R[-1]C,1))+1,CODE(LEFT(R[-1]C,1))))&CHAR(IF(CODE(RIGHT(R[-1]C,1))=90,65,CODE(RIGHT(R[-1]C,1))+1))

and Copy down till you get the ZZ.

Juan Pablo


Alternatively... (Re: Fill Serial - AA, AB,AC...,BA,BB,BC...ZZ)

Posted by Aladin Akyurek on September 07, 2001 10:50 AM
following Juan Pablo's idea of using a formula,

in A1 enter: AA
in A2 enter: =IF(RIGHT(A1)="Z",CHAR(CODE(LEFT(A1))+1),LEFT(A1))&IF(RIGHT(A1)<>"Z",CHAR(CODE(RIGHT(A1))+1),CHAR(65)) [ copy down this as far as needed ]

Aladin


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.