Best way to increment by letters after number 26

[gravanoc]

A window designer wants to number each window and use letters for each panel in that window. For example, he wants 4 windows: Window #1 has 2 panels, #2 has 4, #3 has 4, #4 has 8. This would produce the following series:

1A, 1B, 2A, 2B, 2C, 2D, 3A, 3B, 3C, 3D, 4A, 4B, 4C, 4D, 4E, 4F, 4G, 4H.

Though it's unlikely to exceed 26 panels, or even multiples of 26, I still want to be prepared for such. Using VBA to generate the number & letter combinations as follows:

For i = 0 To windowRng.Rows.count - 1
    
            panelCount(i) = panelRng.Cells(i + 1, 1).Value
        
            For j = 0 To panelCount(i) - 1
            
                  quot = WorksheetFunction.RoundDown(j / 26, 0)
                  Debug.Assert (quot = 0)
                  
                  prodRng.Cells(j + 1, 1).Value = i + 1
                  For x= 0 To quot
                  
                    prodRng.Cells(j + 1, 2).Value = prodRng.Cells(j + 1, 2).Value & Chr(65 + (j Mod 26))
                
                Next x

                ' More code not shown

             Next j

Next i

The problem with the above code is that it will not know to increment the subsequent concatenated letters, and they will subsequently be AA, BB, CC, or AAA, BBB, CCC, and so on. The desired pattern would be AA, AB, AC, etc. Please assist me in that regard, thank you.

 

[JoeMo]

Here's a UDF which can generate the letter sequence Excel uses to label columns. Example below shows first 52 letters. Sequence covers "A" through "XFD".

Book4
	A	B
1	Sequence No.	Letter(s)
2	1	A
3	2	B
4	3	C
5	4	D
6	5	E
7	6	F
8	7	G
9	8	H
10	9	I
11	10	J
12	11	K
13	12	L
14	13	M
15	14	N
16	15	O
17	16	P
18	17	Q
19	18	R
20	19	S
21	20	T
22	21	U
23	22	V
24	23	W
25	24	X
26	25	Y
27	26	Z
28	27	AA
29	28	AB
30	29	AC
31	30	AD
32	31	AE
33	32	AF
34	33	AG
35	34	AH
36	35	AI
37	36	AJ
38	37	AK
39	38	AL
40	39	AM
41	40	AN
42	41	AO
43	42	AP
44	43	AQ
45	44	AR
46	45	AS
47	46	AT
48	47	AU
49	48	AV
50	49	AW
51	50	AX
52	51	AY
53	52	AZ
3

Cell Formulas
Range	Formula
A2:A101	A2	=SEQUENCE(100,1,1,1)
B2:B53	B2	=PERSONAL.XLSB!colno2colref(A2)
Dynamic array formulas.

Function ColNo2ColRef(ColNo As Integer) As String
'Returns column letter reference (e.g. AAB) when col number is the argument
    If ColNo < 1 Or ColNo > Cells.Columns.Count Then
        ColNo2ColRef = "#VALUE!"
        Exit Function
    End If
    ColNo2ColRef = Split(Cells(1, ColNo).Address, "$")(1)
End Function

 

[shg]

This will return columns letters from A to FXSHRXW:

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