Range in R1C1 format in VBA

[Wook]

What I want to do is loop though a row using "i" as the column number. However because the column are A, B, C.... etc and not 1, 2, 3,.... I am finding this hard to do. I don't want to get into Chr(65), Chr(66),... because this becomes a problem when you hit AA. I would think there should be an easy way to use R1C1 in a range ie. Range("R1C5") being the same as Range("E1"), but I can't seem to see anything useful in the help files.

Cheers, Wook

 

[kskinne]

instead of:
Range("E1")

use:
Cells(1, 5)

where '1' represents the row and '5' represents the column (in this example, E)

hth
kevin

 

[Yogi Anand]

Hi Wook:

Have you considered using the Cells Property? It uses Row and Column numbers like in R1C1 style rather than column labels and rows numbers in A1 style.

 

[Wook]

Perfect, cheers Guys!

 

[HiOctane]

just a note:

if you want to refer to a range, then delimit two cell references in Range() by a comma, e.g.

Range(Cells(1,1).Address,Cells(5,5).Address).Address ' is:
$A$1:$E$5