Setting an Excel Range from Address and Offset Strings

Question

I'd like to set a Range Variable from two String Variables; a Range Address and an Offset

For example, the following code sets Ran to R4C6

Dim Str1 As String, Str2 As String, Ran As Range

Str1 = "R1C2"
Str2 = "(3,4)"

Set Ran = Evaluate(Str1).Offset(MID(Str2, 2, 1), MID(Str2, 4, 1))

Debug.Print Ran.Address(, , xlR1C1)

Is it possible to do this directly from the Strings, rather than converting the Offset String into it's Row and Column components as my example does?

I've tried various syntaxes such as

Set Ran = Evaluate(Str1 & ".Offset" & Str2)

but can't find anything that works

I would rather use Cells(intRow,intColumn). so you dont have to take extra step transforming the number to string. — Peicong Chen
– Peicong Chen, Commented Apr 12, 2019 at 21:34

Ron Rosenfeld · Accepted Answer · 2019-04-12 21:31:50Z

You have to somehow extract the numbers. If you are using a string as an argument for the Range object, the string needs to be in A1 style. And, of course, if you are using the Cells object, you can use either numbers (for rows or columns) or strings (in A1 style) for the column names.

Here's another method, using Regular Expressions to extract the numeric values, but there are many methods available to do that:

Option Explicit

Sub due()
    Dim Str1 As String, Str2 As String, Ran As Range
    Dim RE As Object, MC As Object

Str1 = "R1C2"
Str2 = "(3,4)"

Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .Pattern = "\d+"
    Set MC = .Execute(Str1)
        Set Ran = Cells(MC(0), MC(1))
    Set MC = .Execute(Str2)
        Set Ran = Ran.Offset(MC(0), MC(1))
End With

Debug.Print Ran.Address(ReferenceStyle:=xlR1C1)

End Sub

Collectives™ on Stack Overflow

Setting an Excel Range from Address and Offset Strings

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related