Using ColumnNumber in Range syntax
 

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

Using ColumnNumber in Range syntax
 

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

Using ColumnNumber in Range syntax
 

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

Using ColumnNumber in Range syntax
 

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

Using ColumnNumber in Range syntax
 

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

Using ColumnNumber in Range syntax
 

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

Using ColumnNumber in Range syntax
 

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

Using ColumnNumber in Range syntax
 

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

Using ColumnNumber in Range syntax
 

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

Using ColumnNumber in Range syntax
 

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