VBA Find and Replace isn't working on apostrophe within cell value

[Jon]

Hi,

I have an Excel 2007 app that writes data to SQL Server. Whenever I
have an apostrophe in a word within a cell, I replace it with double
quotes to prevent it from hosing my query. For some reason, in some
cells the find and replace code doesn't work. I entered the word
"that's" into each cell in a selected range to test the following
code:

Selection.Replace What:="'", Replacement:="''", LookAt:=xlPart, _
SearchOrder:=xlByRows

Most instances of the apostrophe are replaced correctly, but some are
unchanged. I modified the code to look for ascii characters with the
same result:

Selection.Replace What:=chr(39), Replacement:=chr(34), LookAt:=xlPart,
_
SearchOrder:=xlByRows

When I manually select the range and invoke the Find and Replace
command through the Excel UI, all instances of the apostrophe get
replaced.

How can I force the code to replace the apostrophes?

TIA,
-Jon

 

[Jon]

I gave up on using the Excel native find and replace. I think it's an
Excell 2007 bug.

I'm now passing the value of the cell into a string and replacing the
apostrophe there.

Call EscapeApostrophe(Activecell.Value)

Function EscapeApostrophe(strComment)

 

[Jon]

Function EscapeApostrophe(strComment)
strComment = Replace(strComment, "'", "''")
ActiveCell.Value = strComment
End Function