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

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

---
Regards,
Norman



"Hari Prasadh" wrote in message
...
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

columns(54).Select

replace 54 with your integer/long variable.
--
Regards,
Tom Ogilvy

"Hari Prasadh" wrote in message
...
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

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

"Tom Ogilvy" wrote in message
...
columns(54).Select

replace 54 with your integer/long variable.
--
Regards,
Tom Ogilvy

"Hari Prasadh" wrote in message
...
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

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

---
Regards,
Norman



"Hari Prasadh" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
columns(54).Select

replace 54 with your integer/long variable.
--
Regards,
Tom Ogilvy

"Hari Prasadh" wrote in message
...
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

Hi Norman,

I wanted to use something similar to


Thanks a lot,
Hari
India
"Norman Jones" wrote in message
...
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

---
Regards,
Norman



"Hari Prasadh" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
columns(54).Select

replace 54 with your integer/long variable.
--
Regards,
Tom Ogilvy

"Hari Prasadh" wrote in message
...
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

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
"Norman Jones" wrote in message
...

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


---
Regards,
Norman



"Hari Prasadh" wrote in message
...
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
"Norman Jones" wrote in message
...

Hi Norman,

Sometimes using cells method to cut/paste etc. fails (I dont know why) but
If i change the same to Range(cells(i,j)) the same works fine. Hence wanted
to know if I can directly use ColumnCounter within Range.

Thanks a lot,
Hari
India

"Norman Jones" wrote in message
...
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


---
Regards,
Norman

Hi Hari,

Sometimes using cells method to cut/paste etc. fails

I am not aware of this - except, perhaps, where cells references have not
been properly qualified.

Could you give an example?

---
Regards,
Norman



"Hari Prasadh" wrote in message
...
Hi Norman,

Sometimes using cells method to cut/paste etc. fails (I dont know why) but
If i change the same to Range(cells(i,j)) the same works fine. Hence
wanted to know if I can directly use ColumnCounter within Range.

Thanks a lot,
Hari
India

"Norman Jones" wrote in message
...
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


---
Regards,
Norman