Convert to contents

[AlexanderBB]

If I have a string e.g. "Sheet1!$B$2", is there a Excel function that will give me
the contents of that cell? Or must it be a UDF, in which case , no problem.

 

[Rick Rothstein]

You don't need Evaluate or Indirect in code, you can just use Range:

application.range("Sheet1!B2")

Well, what do you know, that does work!

It never occurred to me to see if the Range object would tolerate that reference format as its argument.

By the way, you can omit the Application keyword as using Range by itself also seems to work.

 

[RoryA]

By the way, you can omit the Application keyword as using Range by itself also seems to work.

It won't if the code is in a different worksheet. Although that won't be the case here, I prefer to always specify it.

 

[Rick Rothstein]

It won't if the code is in a different worksheet. Although that won't be the case here, I prefer to always specify it.

Interesting... and good to know.

 

[AlexanderBB]

Thanks Rory, That works very nicely , and was a big help.
And Eric, I will try your suggestion. Yes the string was typed in. It's just ranges aren't instinctive and strings are.