Excel VBA - loop columns named AA, AB..etc

[Guest]

Hello
I have a problem, I am looping through this sequence of columns, see below, in order to change my list into a list more suited for pivottables; everything worked fine until now, when i also have columns AA,AB... until AO. If I just write them after column z (the z in the string) it will take three A:s then a B then A then C etc.... How can I fix this

(...
For i = 3 To 2
ThisCol = Mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ", i, 1
(...)

 

[Robin Hammond]

Tobias,

Can't you just use the columns object.

For nIndex = 1 to 35
msgbox activesheet.columns(nIndex).address
next nIndex

you can do a lot more this way...

msgbox ActiveSheet.Columns(1).cells(1,1).address
msgbox ActiveSheet.Columns(1).cells(1,1).Resize(10,1).address

Robin Hammond
www.enhanceddatasystems.com

Hello,
I have a problem, I am looping through this sequence of columns, see

below, in order to change my list into a list more suited for pivottables;
everything worked fine until now, when i also have columns AA,AB... until
AO. If I just write them after column z (the z in the string) it will take
three A:s then a B then A then C etc.... How can I fix this?

 

[Bob Phillips]

To keep it simple, why not just add another loop


For i = 1 To 15
ThisCol = "A" & Mid("ABCDEFGHIJKLMNO", i, 1)

(...)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hello,
I have a problem, I am looping through this sequence of columns, see

below, in order to change my list into a list more suited for pivottables;
everything worked fine until now, when i also have columns AA,AB... until
AO. If I just write them after column z (the z in the string) it will take
three A:s then a B then A then C etc.... How can I fix this?

 

[Guest]

Thank you Bob and Robin

Now it works.