How many quotes to put around a formula that is sending an empty "" string-CodePudding

I have a formula that I am sending to a cell in the worksheet. the formula is:

=IF($V6>0,IF($G6>$S6,($S6-$H6)*$K6 $Y6,($G6-$H6)*$K6 $Y6),"")

to send it, I am using the following statement, where the variable "ir" stands for inserted row where the formula needs to go, like this:

Cells(ir, "O").Formula = "=IF($G" & ir & ">$S" & ir & ",($S" & ir & "-$H" & ir & ")*$K" & ir & " $Y" & ir & ",($G" & ir & "-$H" & ir & ")*$K" & ir & " $Y" & ir & ")," & """ "")"""

I think the problem is with the quotes around the empty string at the end (""). If I enclose it in double quotes, I get a syntax error. If I enclose it in triple quotes, I get an "application-defined or object-defined error". is there an easy solution? Can I use NIL for the empty string? Other simpler formulas run great for example this one:

Cells(ir, "N").Formula = "= $L" & ir & "- $M" & ir

Any help would be much appreciated

CodePudding user response：

If you're intention was to use null string - Try this, which removes the extra external quotes:

Cells(ir, "O").Formula = "=IF($G" & ir & ">$S" & ir & ",($S" & ir & "-$H" & ir & ")*$K" & ir & " $Y" & ir & ",($G" & ir & "-$H" & ir & ")*$K" & ir & " $Y" & ir & ")," & """ "")"

Try this, which removes the extra internal and external quotes:

Cells(ir, "O").Formula = "=IF($G" & ir & ">$S" & ir & ",($S" & ir & "-$H" & ir & ")*$K" & ir & " $Y" & ir & ",($G" & ir & "-$H" & ir & ")*$K" & ir & " $Y" & ir & ")," & "" ")"

If you're intention was to use empty string - Try this, which removes the extra external quotes:

Cells(ir, "O").Formula = "=IF($G" & ir & ">$S" & ir & ",($S" & ir & "-$H" & ir & ")*$K" & ir & " $Y" & ir & ",($G" & ir & "-$H" & ir & ")*$K" & ir & " $Y" & ir & ")," & """"")"

Try this, which removes the extra internal and external quotes:

Cells(ir, "O").Formula = "=IF($G" & ir & ">$S" & ir & ",($S" & ir & "-$H" & ir & ")*$K" & ir & " $Y" & ir & ",($G" & ir & "-$H" & ir & ")*$K" & ir & " $Y" & ir & ")," & """)"

Or maybe you had it right to begin with, just needed to delete the space to keep it an empty sting:

Cells(ir, "O").Formula = "=IF($G" & ir & ">$S" & ir & ",($S" & ir & "-$H" & ir & ")*$K" & ir & " $Y" & ir & ",($G" & ir & "-$H" & ir & ")*$K" & ir & " $Y" & ir & ")," & """"")"""