Sumproduct with double quoted strings
 

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

Sumproduct with double quoted strings
 

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






On 01/05/2012 02:56, avi wrote:
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