Formula in macro gives "Run-time error '1004':"
 

I'm trying to set a macro so that if a person changes something in column D
on any row it automatically adds the formula in the corresponding cell in Row
E to do the maths.

This is to make it more idiot proof so if somebody adds a row etc. it sorts
out the formula when they start filling in.

But the below now gives me the above error code and "Application-defined or
Object-defined error" message.

I have other macros in the excel spreadsheet as well....

Help!


Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range(Target.Address), Range("A:E")) _
Is Nothing And Target.Row < 1 Then

Dim r As Long
r = Target.Row

If Cells(r, "D").Value < "" Then

' the below adds in the formula in column E


Cells(r, "E").FormulaR1C1 =
"=IF(IF(RC[-1]="",RC[-2],RC[-1])=0,"",IF(RC[-1]="",RC[-2],RC[-1]))"

End If
End If


End Sub

Formula in macro gives "Run-time error '1004':"
 

You have to double up your quotes to send a " in a string:

"=IF(IF(RC[-1]="""",RC[-2],RC[-1])=0,"""",IF(RC[-1]="""",RC[-2],RC[-1]))"

As you had it:

"=IF(IF(RC[-1]="",RC[-2],RC[-1])=0,"",IF(RC[-1]="",RC[-2],RC[-1]))"

would be treated as:

"=IF(IF(RC[-1]=" then another string ",RC[-2],RC[-1])=0," etc.

Sam


"raphiel2063" wrote:

I'm trying to set a macro so that if a person changes something in column D
on any row it automatically adds the formula in the corresponding cell in Row
E to do the maths.

This is to make it more idiot proof so if somebody adds a row etc. it sorts
out the formula when they start filling in.

But the below now gives me the above error code and "Application-defined or
Object-defined error" message.

I have other macros in the excel spreadsheet as well....

Help!


Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range(Target.Address), Range("A:E")) _
Is Nothing And Target.Row < 1 Then

Dim r As Long
r = Target.Row

If Cells(r, "D").Value < "" Then

' the below adds in the formula in column E


Cells(r, "E").FormulaR1C1 =
"=IF(IF(RC[-1]="",RC[-2],RC[-1])=0,"",IF(RC[-1]="",RC[-2],RC[-1]))"

End If
End If


End Sub