Code for "go to next column"

[pdbowling]

Hi everyone.

I have data in 2 rows and 40 columns in Excel.

I need to walk through the data performing tasks, but I'm not sure how to write a loop that steps through columns since they are named with letters instead of numbers.

Is there a 'NextColumn' keyword or anything like that?

Currently, the only way I can think of to do it is to use a giant 40 member select case statement ie

for i = 1 to 40
Select case i
Case 1
column = "A"
Case 2
column = "B"
...
Case 40
column = "AN"
end select

cell = column & "1"

Just seems like there should be a better way.
Thanks
PB

 

[xlbo]

Columns can also be referred to by number

For i = 1 to 40
msgbox cells(2,i).address
next i

If you use the CELLS object instead of the RANGE object, you can use pure numerics
Cells(row,column) is the syntax where column is an integer from 1 to 256
So A1 = cells(1,1)
A2 = cells(2,1)
B1 = cells(1,2)
etc etc Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[RobBroekhuis]

Instead of referencing your cells with something like

range(strColumn & strRow),

use

cells(iRow, iColumn) (in which iRow and iColumn are integers), or:

set rngCell=rngCell.offset(0,1) to move a range object horizontally through columns.

In VBA there is hardly ever a need to refer to columns by their letter designation.


Rob

 

[xlbo]

<snigger> you getting slow in your old age Rob ?? Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[pdbowling]

I thought there 'had' to be a numeric version. It's just that the help files are HUGE and you just can't dig out what you're looking for. Thanks so much.
PB

 

[RobBroekhuis]

slooooooo

http://wwwwwwww?

meeeeeeee?
Rob