quotation marks in ranges used to build a formula

[Jerry Hansen]

In visual basic, I have a line of code:

combitem.Formula = "=if($G$11=1," & Chr(34) & NatUnit & Chr(34) & ","
& Chr(34) & LPUnit & Chr(34) & ")"

combitem refers to cell on a spreadsheet, NatUnit refers to a cell on
a spreadsheet, LPUnit refers to a cell on a spreadsheet

If the cell referenced by NatUnit contains the text AAAA and cell
referenced by LPUnit contains the text BBBB then the resulting formula
that is put into the cell referenced by combitem is:

=if($G$11=1,"AAAA","BBBB")
Which is exactly what it is supposed to happen. Now comes the problem:

If either of the cells referenced by NatUnit or LPUnit contains a
quotation mark, I get a runtime error "1004".

For example, if the cell referenced by NatUnit is Unit with
1'-9" flange

I can't change the data in the NatUnit or LPUnit cells since I'm
importing them from another source.

I thought of searching NatUnit and LPUnit for quotation marks before I
got to this line of code, then having a subroutine to change them to
three quotation marks in a row. However, I can't figure out how to
find a quotation mark using the InStr function.

Any workarounds or ideas?

 

[J.E. McGimpsey]

In visual basic, I have a line of code:

combitem.Formula = "=if($G$11=1," & Chr(34) & NatUnit & Chr(34) & ","
& Chr(34) & LPUnit & Chr(34) & ")"

combitem refers to cell on a spreadsheet, NatUnit refers to a cell on
a spreadsheet, LPUnit refers to a cell on a spreadsheet

If the cell referenced by NatUnit contains the text AAAA and cell
referenced by LPUnit contains the text BBBB then the resulting formula
that is put into the cell referenced by combitem is:

=if($G$11=1,"AAAA","BBBB")
Which is exactly what it is supposed to happen. Now comes the problem:

If either of the cells referenced by NatUnit or LPUnit contains a
quotation mark, I get a runtime error "1004".

For example, if the cell referenced by NatUnit is Unit with
1'-9" flange

I can't change the data in the NatUnit or LPUnit cells since I'm
importing them from another source.

I thought of searching NatUnit and LPUnit for quotation marks before I
got to this line of code, then having a subroutine to change them to
three quotation marks in a row. However, I can't figure out how to
find a quotation mark using the InStr function.

Any workarounds or ideas?

One way:

combitem.Formula = "=if($G$11=1," & Chr(34) & _
Application.Substitute(NatUnit, """", """""") & Chr(34) & _
"," & Chr(34) & _
Application.Substitute(LPUnit, """", """""") & Chr(34) & ")"

or, a bit more compactly:

combitem.Formula = "=if($G$11=1,""" & _
Application.Substitute(NatUnit, """", """""") & """,""" & _
Application.Substitute(LPUnit, """", """""") & """)"

Since VBA treats "" within a string as a single quote, """" will
match a single " and """""" will replace it with "".