Passing string arguments that have quotation marks in them

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi.

I have a function that builds and evaluates an array formula SUM(IF based on
a number of arguments. Some of these arguments are string values that may
have "" within them.

For example, I might have an item number description (one of the arguments)
that looks like this: "Left side "H" Bracket".

The function works fine for every item except items with this type of
description. I'd like to avoid requiring users to remove the "" in the
description if possible.

Can anyone help me out?

Thanks!
 
Guess you need to modify your code to account for the possible existence of
embedded double quotes.

without seeing your code, there isn't much else that can be said.
 
Thanks Tom.

Does this help?

Dim eRow As Integer
Dim platRng As Range, pldcRng As Range, partRng As Range, prd1Rng As Range
Dim prd2Rng As Range, colmRng As Range
Dim dBook As Workbook
Dim dShet As Worksheet
Dim eString As String, cString As String

Function Results(xShet As String, xCol As Integer, xPlat As Variant, xPlDc
As Variant, _
xPart As Variant, Optional xPrD1 As Variant = "", Optional xPrD2 As
Variant = "") As Double

'Pulls the corresponding value for the unique combination of Platform,
Platform description, Part, Part description 1, and Part description 2

Set dBook = ActiveWorkbook
Set dShet = dBook.Worksheets(xShet)

eRow = dShet.Cells(65536, 1).End(xlUp).Row

On Error Resume Next

If eRow > 1 Then
Set colmRng = Range(dShet.Cells(2, xCol).Resize(eRow - 1, 1).Address)
Set platRng = Range(dShet.Cells(2, 7).Resize(eRow - 1, 1).Address)
Set pldcRng = Range(dShet.Cells(2, 8).Resize(eRow - 1, 1).Address)
Set partRng = Range(dShet.Cells(2, 9).Resize(eRow - 1, 1).Address)
Set prd1Rng = Range(dShet.Cells(2, 10).Resize(eRow - 1, 1).Address)
Set prd2Rng = Range(dShet.Cells(2, 11).Resize(eRow - 1, 1).Address)

eString = "=SUM(IF(('" & dShet.Name & "'!" & platRng.Address & "=""" &
xPlat & """)*('" & dShet.Name & "'!" & pldcRng.Address & _
"=""" & xPlDc & """)*('" & dShet.Name & "'!" & partRng.Address &
"=""" & xPart & """)*('" & dShet.Name & "'!" & prd1Rng.Address & _
"=""" & xPrD1 & """)*('" & dShet.Name & "'!" & prd2Rng.Address &
"=""" & xPrD2 & """),'" & dShet.Name & "'!" & colmRng.Address & ",0))"
Results = Evaluate(eString)
End If

On Error GoTo 0

End Function
 
Back
Top