"If" Formula in VBA

  • Thread starter Thread starter Shane Wierenga
  • Start date Start date
S

Shane Wierenga

I am attempting to add a formula to a cell when a sheet is added to my
workbook. I get the following error when I run the program
"Application-defined or object-defined error". Here is the line of
code that is throwing up the error.

Range("B20").formula = "=IF('Sign 2'!P65>0,'Sign 2'!E10,"")"

I believe it has to do with the if in the formula but I am unable find
any thine on the subject.
Other formulas such as the formula below are added with no problems.

Range("L20").formula = "=('Sign 1'!P65+'Sign 1'!P70)/'Sign 1'!E10"

Please help if you can.
 
Try doubling your double quotes within the string. VBA does not like
double quotes inside a string and the workaround is to use two double
quotes wherever there was one.

Range("B20").formula = "=IF('Sign 2'!P65>0,'Sign 2'!E10,"""")"

Post back if you still get an error.
 
This is the formula that ended up working thanks Aviashn


Range("B20").formula = "=IF('Sign 2'!P65>0,'Sign 2'!E10,"""")"
 
It may help to put this under the Workbook_NewSheet Event. Thought it may
help.

Private Sub Workbook_NewSheet(ByVal Sh As Object)

Sh.Range("B20").Formula = "=IF('Sign 2'!P65>0,'Sign 2'!E10,"""")"

End Sub

Ryan
 
It's your double quotes. Try using chr(34) instead.

Range("B20").Formula = "=IF('Sign 2'!P65>0,'Sign 2'!E10," & Chr(34) &
Chr(34) & ")"
 
Back
Top