Attn Jezebel: Search and Replace in String

[Eternally Grateful]

Jezebel,

Your earlier response to my issue works perfectly by itself. But when I add
it to an IF statement, the macro completely runs over it bringing back a
negative response, therefore treating the line of code as if it had been
commented out.

Would you have any idea as to what the problem could be...

My code is...


If InStr(1, MyData, Chr(150)) > 0 Then

MyData = Replace$(OldData, Chr$(150), "-")

End If


*******************************************************


em-dash is chr(150)

BTW, the string versions of these functions are about 10 times faster than
the variant equivalents --

MyData = Replace$(OldData, chr$(150), "-")

 

[Jonathan West]

Shouldn't that first line use OldData instead of MyData?

 

[Jezebel]

First, as Jonathan has picked up -- the function argument is MyData or
OldData or whatever variable you are using. The fact that you didn't get an
error on this means that you don't have an 'Option Explicit' statement at
the top of your code module. Put one in immediately, and set the option so
that it's automatic in future. You'll save yourself a heap of anguish.

However, a critical problem is that I led you astray. Chr$(150) is an EN
dash. EM dash is 151.

Also, there's no need for the IF statement. Replace looks for one string and
replaces it with another if found. If not found it does nothing.

 

[Jay Freedman]

Besides that, the If is probably unnecessary. If the old string
doesn't contain any em dashes, the result of the Replace is the same
as the old string, so that's all you need. The only reason to have the
If statement would be that you don't want to assign anything to MyData
if there are no em dashes in OldData. I'll bet that isn't your
intention.