syntax error in vba formula

i have the following code that works inside a sheet. now need to adapt that formula so that is runs inside VBA

Please Login or Register  to view this content.

also have the same need for this formula as well

Please Login or Register  to view this content.

sadly i had the code working for ifna but excel crashed and didn't recover the update code.

any help on quote marks in formulas would be helpful

Originally Posted by dmcgov

any help on quote marks in formulas would be helpful

Text constants in VB are delineated by quote marks surrounding the text. If you tried to put a quote mark inside of the text like this...

"LeadInText"AfterText"

VB cannot tell if that middle quote mark was meant to end the text constant or if it was meant as a single quote mark character. I know it is fairly obvious as to intent by looking at it, but VB's parser has rigid rules it follows when interpreting code lines. To tell VB to treat that middle quote mark as a character instead of a text delimiter, you simply double it up like this...

"LeadInText""AfterText"

Now where some confusion comes in is when your text starts or ends with a quote mark character. But, if you remember this...

"LeadInText""AfterText"

then the rest is easy. If your text starts with a quote mark character, then there is no lead-in text, so removing it leaves this...

"""AfterText"

The first quote is the text constant delimiter and the next two is the quote mark character as VB sees it. You could do the same for a quote mark character at the end of the text constant, simply remove the text after it...

"LeadInText"""

And finally, if your text just consist of a single quote mark (usually to be concatenated with a variable), just remove the lead-in and after text leaving simply this...

""""

many thanks Rick for that detailed explanation. very helpful!

now lets see if i can apply this new knowledge.