Autofill A B C D...AA AB...

[cmendes]

Hello,

I remember that excel could complete a sequence is we gave it the first elements.
In this case I'm writing in three different cells (same column) A, B C and I want excel to do the rest as I drag it down: D E F and so on. But if do it I get A B C again...
Is there anything that i have to do??
I'm using Excel for mac 2011
Thanks in advance,
C

 

[Tetra201]

Here is my take on it -- provides letter strings up to "ZZZZ":

A correction to my previously posted formula:

=SUBSTITUTE(CHAR((ROWS(A$1:A1)>18278)*(MOD(FLOOR(ROWS(A$1:A1)-703,17576)/17576-1,26)+1)+64)&CHAR((ROWS(A$1:A1)>702)*(MOD(FLOOR(ROWS(A$1:A1)-27,676)/676-1,26)+1)+64)&CHAR((ROWS(A$1:A1)>26)*(MOD(FLOOR(ROWS(A$1:A1)-1,26)/26-1,26)+1)+64)&CHAR(MOD(ROWS(A$1:A1)-1,26)+65),"@","")

 

[Learn14]

Not working...

 

[Learn14]

Its' not working..

 

[Learn14]

Great work but not working..

 

[ukmikeb]

Your work is great but this is work only up to XFD character it should be work up to ZZZ, can you revise it?

Excels column limit is 16384 ie XFD.

Are you really referencing outside that limit?

 

[Tetra201]

Its' not working..

Could you please provide some details on what exactly is not working?

 

[mikerickson]

You could try this UDF approach
Put "A" in A1 (no quotes) and =NEXTSTRING(A1) in A2 and drag down as needed.

Function NextString(ByVal aString As String) As String
    Dim suffix As String
    If aString = vbNullString Then
        NextString = "A"
    ElseIf Right(aString, 1) = "Z" Then
        NextString = NextString(Left(aString, Len(aString) - 1)) & "A"
    Else
        Mid(aString, Len(aString), 1) = Chr(Asc(Right(aString, 1)) + 1)
        NextString = aString
    End If
End Function

 

[shg]

Another way:

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

Function ColNum(ByVal sCol As String) As Long
    ' shg 2012
    ' Good to column FXSHRXW (2147483647)
    If Len(sCol) > 0 And _
       (Len(sCol) < 7 Or UCase(sCol) <= "FXSHRXW") Then
        ColNum = Asc(UCase(Right(sCol, 1))) - 64 _
                 + 26 * ColNum(Left(sCol, Len(sCol) - 1))
    End If
End Function

e.g., =ColLtr(337973), =ColNum("Mike")

 

[Learn14]

Excels column limit is 16384 ie XFD.

Are you really referencing outside that limit?

If i have required some products name from any sheet to excel than we write code in VB but in excel should be character From AAAAAAA to ZZZZ....... up to 26 character so we can import all data in excel.
So i have to write A,B,C,C.....AAAAAAAAA UP TO 26 CHARACTER OF A,B,C,D....

LIKE,
A
B
C
D
.
.
.
.Z
AA
AB
AC
.
.
.
AZ
AAA
AAB

.
.
.
AZZ
..
SAME LIKE UP TO WHOLE ABCD
AAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAB
.
.
.
.
ZZZZZZZZZZZZZZZZZZZZZZZZZZ

 

[shg]

Within the limits of the double-precision floating-point numbers used by Excel, names can have a max length of 10-letters to be able to convert bidirectionally to numbers. So you need another plan.