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

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
(...)

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

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).add ress

Robin Hammond
www.enhanceddatasystems.com

"Tobias" wrote in message
...
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 26
ThisCol = Mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ", i, 1)
(...)

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

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)

"Tobias" wrote in message
...
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 26
ThisCol = Mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ", i, 1)
(...)

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

Thank you Bob and Robin

Now it works.