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'!P650,'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'!P650,'Sign 2'!E10,"""")"

Post back if you still get an error.

Put this instead.

Range("B20) = "=IF('Sign 2'!P650,'Sign 2'!E10,"")"

Ryan

"Shane Wierenga" wrote:

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'!P650,'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.

This is the formula that ended up working thanks Aviashn


Range("B20").formula = "=IF('Sign 2'!P650,'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'!P650,'Sign 2'!E10,"""")"

End Sub

Ryan

"Aviashn" wrote:

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'!P650,'Sign 2'!E10,"""")"

Post back if you still get an error.

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

Range("B20").Formula = "=IF('Sign 2'!P650,'Sign 2'!E10," & Chr(34) &
Chr(34) & ")"


"Shane Wierenga" wrote in message
...
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'!P650,'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.