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.