Correct syntax for replace command

[dflak]

Here is the code

Please Login or Register  to view this content.

I get a syntax error on both of the attempted Replace commands. What is the correct syntax?

[dflak]

Never mind, I got it I have to make a dummy Boolean value to assign the return to.

RTN = Range("Table_Shortage_Report").Replace("(blank)","")

Where RTN is defined as Boolean/

[Rick Rothstein]

Range("Table_Shortage_Report").Replace("(blank)","")

Actually, your problem is with your use of parentheses. This would work fine...

Range("Table_Shortage_Report").Replace "(blank)", ""

but when you surrounded the arguments in parentheses, VBA attempted to do what it always does with parentheses that are not required by syntax... it attempted to evaluate the contents of the parentheses in order to produce a single value. For subroutines and methods (Replace is a method), parentheses are not required for by syntax. If there is just a single argument, surrounding it with parentheses is not a problem (a value evaluates to itself); however, when there is two or more arguments, surrounding them with parentheses asks VBA to create a single evaluated value from them... that can't be done because, well, there are two or more independent values and VBA can't evaluate them into a single value so it raises an error instead. By the way, your solution method worked because you used Replace as a function (which it is designed to act as if desired) and functions require parentheses around its argument list by syntax.

[jolivanes]

And in your first post there is a spelling error that has been changed I assume (what:="(balnk)"
If you copied the code from your Module, that would raise an error also