Sumproduct with double quoted strings

[avi]

Hello,
I have procedure that reads strings from a file
LayerC = Chr(34) & Layer & Chr(34)

Then Evaluate a count
CountIn = _
xlApp.Evaluate("SUMPRODUCT((" & _
MainDataRange.Address & "=" & TvahC & ")*(" &
RangeLayers.Address & "=" & LayerC & "))")

The problem is if the variable Layer contains double quotes (For
Example N"Y) , the new variable LayerC is now "N"Y", which the 2nd
statement does not accept

Any idea?
Thanks
Avi

 

[Dave Peterson]

You can modify the double quote to two double quotes:

Option Explicit
Sub testme()

Dim LayerC As String
LayerC = "zzz" & """" & "xxx" & """" & "zzz"

MsgBox LayerC
LayerC = Replace(LayerC, Chr(34), Chr(34) & Chr(34))
MsgBox LayerC

LayerC = Chr(34) & LayerC & Chr(34)

MsgBox LayerC

End Sub

You may want to consider doing the same kind of thing with other special/wild
characters:
*,?,~
asterisk, question mark, and tilde