Macro-replacement of strings that have ["] in them

[John Svendsen]

Hi All,

Does anyone know how to get around the problem of replacing strings that
have ["] in them? As the delimiter is itself an ["], if there is an ["] in
the middle of strings the replace does not work.

Thanks a lot!!!

 

[Jay Freedman]

Hi All,

Does anyone know how to get around the problem of replacing strings that
have ["] in them? As the delimiter is itself an ["], if there is an ["] in
the middle of strings the replace does not work.

Thanks a lot!!!

Hi John,

You can use either two quotes together or the function Chr$(34), which
refers to the fact that the ASCII value of the quote character is 34.

As an example, to find the string

Say "hello"

your macro might contain the code

With Selection.Find
.Text = "Say ""hello"""
' ...
End With

or the code

With Selection.Find
.Text = "Say " & Chr$(34) & "hello" & Chr$(34)
' ...
End With

If you're going to use this character a lot, you could define a
constant at the beginning of the macro and use that to save
keystrokes:

Const vbQt = """"
With Selection.Find
.Text = "Say " & vbQt & "hello" & vbQt
' ...
End With

 

[Jezebel]

I'd also suggest that you construct the search string in a variable, if only
to make debugging simpler --

Dim pSearch as string

pSearch = "Say " & Chr$(34) & "hello" & Chr$(34)
Debug.Print "Searching for: " & pSearch

With Selection.Find
.text = pSearch
:

Hi All,

Does anyone know how to get around the problem of replacing strings that
have ["] in them? As the delimiter is itself an ["], if there is an ["] in
the middle of strings the replace does not work.

Thanks a lot!!!

Hi John,

You can use either two quotes together or the function Chr$(34), which
refers to the fact that the ASCII value of the quote character is 34.

As an example, to find the string

Say "hello"

your macro might contain the code

With Selection.Find
.Text = "Say ""hello"""
' ...
End With

or the code

With Selection.Find
.Text = "Say " & Chr$(34) & "hello" & Chr$(34)
' ...
End With

If you're going to use this character a lot, you could define a
constant at the beginning of the macro and use that to save
keystrokes:

Const vbQt = """"
With Selection.Find
.Text = "Say " & vbQt & "hello" & vbQt
' ...
End With