This is just a favorite trick that I've used in the past to avoid having to parse range references in VBA just so I can navigate to some range. I was working on a project this morning and the situation presented itself once again; figured I'd take a moment to share it.
I have a range reference like this: "Apr!$L$72:$O$76"
...and I need my macro to goto or operate on that address.
Assuming the string:
If we try to use either of the following, the code fails:
So frequently (perhaps always?) you see people parsing the string address into seperate references for the sheet and then the range, this way they can select the sheet and with the sheet selected get to the range.
For instance, either of the following would be acceptable to VBA:
...but of course we have to parse the original string to break it up into the necessary components.
There is a handy little trick I've used in the past to avoid the parsing. For whatever reason, the Goto method does allow R1C1 sheet/range references, it just doesn't like them in A1 format. We also have a nifty little command in VBA called "ConvertFormula" that we can use to easily switch between these two reference styles.
So now comes the neat trick...
Granted, if you can get the range string reference in R1C1 format first, you don't even need to convert it.