How to Modify a Range Format

[Cyberdude]

I have a macro with an argument that is a range (address), which can be any of the standard formats (with or without "$"). I would like to use no more than 3 statements to make sure that the format has a "$" between each column and row (.Address(,False)) or the simple ".Address" format with "$" in front of the columns too. I want only one of these two formats, not both.
If the unknown address (which can be a scalar or array) arg is named "UnknownAddr", then a statement like

UnknownAddr = UnknownAddr.Address
would be nice (although invalid). Can that be done?

[Bob Phillips]

I have a macro with an argument that is a range (address), which can be any of the standard formats (with or without "$"). I would like to use no more than 3 statements to make sure that the format has a "$" between each column and row (.Address(,False)) or the simple ".Address" format with "$" in front of the columns too. I want only one of these two formats, not both.
If the unknown address (which can be a scalar or array) arg is named "UnknownAddr", then a statement like

UnknownAddr = UnknownAddr.Address
would be nice (although invalid). Can that be done?

For example



inputFormula = "=SUM(B10:B15)"
MsgBox Application.ConvertFormula(Formula:=inputFormula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, _
ToAbsolute:=xlRelRowAbsColumn)

[Cyberdude]

Hey, xld, that's got to be the clutziest solution I've ever seen, and by gawd it works like a dream. In fact, with a little testing, I found that I didn't have to use a formula-looking argument. I am able to just use the variable that contains the range address. Works like a charm. Who would have guessed? I can't thank you enough. I was able to remove a LOT of statements as a result. http://vbaexpress.com/forum/images/smilies/notworthy.gif

[Bob Phillips]

Hey, xld, that's got to be the clutziest solution I've ever seen, and by gawd it works like a dream. In fact, with a little testing, I found that I didn't have to use a formula-looking argument. I am able to just use the variable that contains the range address. Works like a charm. Who would have guessed? I can't thank you enough. I was able to remove a LOT of statements as a result. http://vbaexpress.com/forum/images/smilies/notworthy.gif

Clutziest or cutest? Anyway, we aim to please, even when we only poin t the direction http://vbaexpress.com/forum/images/smilies/001.gif

[Sir Babydum GBE]

Did someone say "solution"?

Doh! Anyone from legal know how I get the ? sign after the word "Solution" mandatory?