Using ColumnNumber in Range syntax

[Hari Prasadh]

Hi,

If I want to select Column A programmatically then

Columns(A:A).select would work.

Suppose I have a variable column to select and the counter for this column
number is from a FOR loop then how do I use this counter to select that
column.

For ColumnCounter = 4 to 54

Columns (ColumnCounter & ":" & ColumnCounter).select
'do required stuff
'do required stuff

Next ColumnCounter

Now when i run the above, I would get an error as within Columns syntax
above, ColumnCounter should be a string (from "A" to "IV").

So, how do I Translate/Map Column Numbers in to equivalent column letter/s.

Can that be done programmatically?

(One way is in my Personal.xls I can build a mapping with column A
containing numbers from 1 to 256 and corresponding rows in column B having A
to IV and then do a vlookup of ColumnCounter against A1 to B256. But is
there a more efficient way?)

Thanks a lot,
Hari
India

 

[Norman Jones]

Hi Hari,

Try:

Sub Tester05()
Dim Columncounter
For Columncounter = 4 To 54

With Columns(Columncounter)
'do required stuff
'do required stuff
End With
Next Columncounter

End Sub

 

[Tom Ogilvy]

columns(54).Select

replace 54 with your integer/long variable.

 

[Hari Prasadh]

Hi Norman and Tom,

Thanks for your elegant solutions.

I would like to know whether "similar shortcuts" could be used with range
syntax as well.

In Range we have to specify column LETTER and row number and I would like to
have the ability to use range syntax within a FOR loop whose counter is the
Column number. I know one can use Range(Cells(i,j),Cells(a,b)) but is there
any other method. (I did go through Help for Range Collection.

Thanks a lot,
Hari
India

 

[Norman Jones]

Hi Hari,

Perhaps you mean:

Sub Tester06()
Dim i As Long

For i = 1 To 20
'do something. e.g.:
Range("A" & i).Value = i ^ 2
Next i

End Sub

 

[Hari Prasadh]

Hi Norman,

I wanted to use something similar to


Thanks a lot,
Hari
India

 

[Hari Prasadh]

Hi Norman,

Pressed the send button a little too fast.

I wanted to use something similar to

Sub Tester06()
Dim i As Long

For i = 1 To 20

for J = 3 to 40
'do something. e.g.:
Range(j & i).Value = i ^ j
Next i

End Sub


I know Cells syntax can be used in the above simple case and for complex
cases we can use Range(Cells(i,j),Cells(a,b)).

But for complex case I would like to know whether I can do
Range(ColumnCounter1 & RowCounter1 & ":" & ColumnCounter2 & RowCounter2)
(that is I dont want to use Cells within Range syntax)

Am I talking sense?

Thanks a lot,
Hari
India
news:[email protected]...

 

[Norman Jones]

Hi Hari,

If numeric counters are being used, why depart from the elegantly, simple
Cells method?

Sub Tester07()
Dim i As Long, j As Long
Dim col As Long

For i = 1 To 20
For j = 3 To 40
Cells(i, j).Value = i ^ j
Next j
Next i

End Sub