Using Range & Cell
 

I wish to create a macro that will require processing several columns in an
incremental way.
As far as I can tell, I have 2 options :-
1) Use, for example, Cells(x,colnum) with something like an increment
counter [e.g. colnum = colnum+1] but then I need to know how to translate,
say, column CW into a number.
2) Use, for example, Range("CW1") but then how do I increment from CW to CX
to CY etc.


Help appreciated & many thanks

Jack

Using Range & Cell
 

Hi Jack,

Perhaps try something like:

'=============
Public Sub Tester()
Dim col As Range

For Each col In Range("C:H").Columns
'do something, e.g,:
MsgBox col.Address
Next col
End Sub
'<<=============


---
Regards,
Norman


"Jack" wrote in message
...
I wish to create a macro that will require processing several columns in an
incremental way.
As far as I can tell, I have 2 options :-
1) Use, for example, Cells(x,colnum) with something like an increment
counter [e.g. colnum = colnum+1] but then I need to know how to translate,
say, column CW into a number.
2) Use, for example, Range("CW1") but then how do I increment from CW to
CX to CY etc.


Help appreciated & many thanks

Jack

Using Range & Cell
 

See below for answers to your two specific questions. The
best way would probably depend on how you specify the
"several columns". One approach might be:

Dim topCell as Range, colNo as integer
For Each topCell in Range ("CW1:DD1")
colNo = topCell.Column
' do stuff with the column
Next

Andrew

Jack wrote:
I wish to create a macro that will require processing several columns in an
incremental way.
As far as I can tell, I have 2 options :-
1) Use, for example, Cells(x,colnum) with something like an increment
counter [e.g. colnum = colnum+1] but then I need to know how to translate,
say, column CW into a number.

Range("CW1").Column gives you the column number.

2) Use, for example, Range("CW1") but then how do I increment from CW to CX
to CY etc.

Dim myRange as Range
Set myRange = Range("CW1")
'' do stuff with CW1
Set myRange = Range("CW1").Offset(0,1) ' sets myRange to CX1



Help appreciated & many thanks

Jack

Using Range & Cell
 

Jack Wrote:
I wish to create a macro that will require processing several columns in
an
incremental way.
As far as I can tell, I have 2 options :-
1) Use, for example, Cells(x,colnum) with something like an
increment
counter [e.g. colnum = colnum+1] but then I need to know how to
translate,
say, column CW into a number.
2) Use, for example, Range("CW1") but then how do I increment from CW
to CX
to CY etc.


Help appreciated & many thanks

Jack
Hope this will get you started

Code:
--------------------

Dim y As Long, i As Long
y = Range("CW1").Column
For i = 0 To 10
Cells(x, y + i) = i
Next
' Using Offset
With Range("CW" & x)
For i = 0 To 10
.Offset(, i) = i
Next
End With
--------------------


--
jindon
------------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=551315