Range("R1C1") gives error

[Sebastian H]

Simple question: I want to address a cell by its R1C1 address (given as a string). I would have thought that should be easy with Range("R1C1"), but that fails with the error

Run-time error '1004':
Application-defined or object-defined error

By contrast, Range("A1") works nicely. So, apparently I need to tell VBA that "R1C1" is an R1C1 address. How to do that?

[Paul_Hossler]

An ugly way




Sub drv()


Application.ReferenceStyle = xlR1C1
MsgBox [R1C1].Value
Application.ReferenceStyle = xlA1




End Sub

[Sebastian H]

An ugly way
In principle, that isn't ugly at all; certainly not in my case: I should have used Application.ReferenceStyle = xlR1C1 all along, since I don't normally use A1 format.

However, sadly, if I replace the brackets with Range() to accommodate a variable, it gives a similar error:

Sub Display_Value(address_in_R1C1_format As String)
Dim previous_setting
previous_setting = Application.ReferenceStyle
Application.ReferenceStyle = xlR1C1
MsgBox Range(address_in_R1C1_format).Value
Application.ReferenceStyle = previous_setting
End Sub


Run-time error '1004':
Method 'Range' of object '_Global' failed

(In this code example, to make this sub useful for anyone who wants to keep it independent of the format used elsewhere, I save Application.ReferenceStyle in a dummy variable, and reset it to the same setting afterwards.)

[Paul_Hossler]

I Set it into a Range to make it easier to reference / use and it's (probably) independent of reference style

There's probably some cleanup that could be done depending on the use

May not even need to mess with Application.ReferenceStyle




Option Explicit

Sub drv()
Call Display_Value("R1C1")
End Sub

Sub Display_Value(address_in_R1C1_format As String)
Dim previous_setting As XlReferenceStyle
Dim r As Range

previous_setting = Application.ReferenceStyle
Application.ReferenceStyle = xlR1C1

MsgBox Application.ConvertFormula(address_in_R1C1_format, xlR1C1, xlA1)
Set r = Range(Application.ConvertFormula(address_in_R1C1_format, xlR1C1, xlA1))
MsgBox r.Value

Application.ReferenceStyle = previous_setting
End Sub

[Sebastian H]

Thanks, Paul – this solves it.

And your hunch was right: It's independent of reference style and works regardless of the Application.ReferenceStyle setting.

[snb]

Sub M_snb()
MsgBox ActiveCell.Address(, , 1) & vbLf & ActiveCell.Address(, , -4150) & vbLf & ActiveCell.Offset(5, 8).Address(, , 1) & vbLf & ActiveCell.Offset(5, 8).Address(, , -4150) & vbLf & Cells(10, 4).Address(, , 1) & vbLf & Cells(10, 4).Address(, , -4150)
End Sub

If you are used to R30C12 style, you'd better use cells(30,12).

[Sebastian H]

It's not a question of what I'm used to, but what's given. Given is a string.

But no worries, the thread has already been solved.