• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 57
  • Last Modified:

Excel VBA Tell if string is R1C1 Reference

Hi

I have a string sRange that holds a range. If it holds eg R[-1]C[+2] what VBA would I use
to test if it is an R1C1 reference?
0
Murray Brown
Asked:
Murray Brown
  • 2
  • 2
1 Solution
 
Rgonzo1971Commented:
Hi,

if you are already in R1C1 mode
Sub Macro()
srange = "R[-1]C[+2]"
res = Evaluate("=isref(" & srange & ")")
End Sub

Open in new window

Regards
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks. If I run that code and hover over res I see the value "Error 2015"
0
 
Rgonzo1971Commented:
then try
Sub Macro1()
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    srange = "R[-1]C[+2]"
    With regex
        .Global = True
        .IgnoreCase = True
        .Pattern = "^R((\[[+-]?\d+\])|\d+)?C((\[[+-]?\d+\])|\d+)?"
    End With
    res = regex.Test(srange)
    
    Set regex = Nothing

End Sub

Open in new window

0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
I am amazed! Thank you very much. Fantastic answer!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now