Convert string variable to range variable

[jerrykern]

Why doesn't this work? I've got a string variable populated with "$A$1", and need to convert that to a range object:

Dim Rng As Range
Dim RngString As String

RngString = "$A$1"

Set Rng = Range(RngString)

Rng.AddComment("Hello World")

If I debug.print RngString and Rng, I get "$A$1" and " ", respectively.

EDIT: This is in a UDF, if that affects your response at all.

 

[Herakles]

It works for me.

Showing Rng in the Debug window as "" is correct.

You would need to show a property of the object to see something such as the Name property.

 

[Peter_SSs]

If I debug.print RngString and Rng

Try Debug.Print RngString and Rng.Address

 

[Herakles]

It works for me.

Showing Rng in the Debug window as "" is correct.

You would need to show a property of the object to see something such as the Name property.

View attachment 91482

I did actually mean the Address propety.

 

[bebo021999]

Try to remove the $ sign

RngString = "A1"

 

[jerrykern]

I'm not sure what I did wrong if it was working for you guys, but when I declared RngString as a Variant instead of a String, it started working.

 

[Peter_SSs]

Perhaps it was related to the rest of the UDF code that you didn't show us.
In any case, thanks for letting us know that you have it sorted.

 

[jerrykern]

Unfortunately, the entire UDF is very long and embarrassingly ugly, so I didn't post it. No doubt the code you didn't see was interfering with the code you did see somehow.