MrExcel Publishing
Your One Stop for Excel Tips & Solutions

R1C1 Cell References in Macros

Posted by Krista on March 27, 2001 11:02 AM

How do I utilize the R1C1 Style of cell notation in a macro? I am trying to adjust the column number by way of a variable, but am running into the error: "Method 'Range' of object'_Global' failed." The line the debugger highlights is as follows:
k = Range("R1C" & col).Value
I have tried the same line without the quotation marks and even with out the variable (R1C1) and nothing seems to work. What am I doing wrong?

Posted by Dave Hawley on March 27, 2001 1:12 PM

Hi Krista

Use the Cells for this, like below:

Sub TryThis()
Dim K
Dim Col As Integer
Col = 1
K = Cells(1, Col).Value
MsgBox K
End Sub

OzGrid Business Applications

Posted by mseyf on March 27, 2001 1:27 PM


if you're open to approaching the problem from a different angle, you might try:

k = Range("A1").offset(0,col).value