#1004 Application-Defined Error

[vatrobo1]

Hi,


I am facing #1004 Application-Defined Error issue when try to apply below formula with VBA. The actual formula is :


=IFERROR(
IF(INDEX('Source data'!$D$4:$ES$40003,MATCH($G5,'Source data'!$EF$4:$EF$40003,0),MATCH(H$3,'Source data'!$D$4:$ES$4,0))="N",
INDEX('Source data'!$D$4:$ES$40003,MATCH($G5,'Source data'!$EF$4:$EF$40003,0),MATCH(H$2,'Source data'!$D$4:$ES$4,0)),
INDEX('Source data'!$D$4:$ES$40003,MATCH($G5,'Source data'!$EF$4:$EF$40003,0),MATCH(H$2,'Source data'!$D$4:$ES$4,0))/INDEX('Source data'!$D$4:$ES$40003,MATCH($G5,'Source data'!$EF$4:$EF$40003,0),MATCH(H$1,'Source data'!$D$4:$ES$4,0))
),0)


After recording the formula with macro , I am getting the following :


ActiveCell.FormulaR1C1 = _
"=IFERROR(" & Chr(10) & "IF(INDEX('Source data'!R4C4:R40003C149,MATCH(RC7,'Source data'!R4C136:R40003C136,0),MATCH(R3C,'Source data'!R4C4:R4C149,0))=""N""," & Chr(10) & "INDEX('Source data'!R4C4:R40003C149,MATCH(RC7,'Source data'!R4C136:R40003C136,0),MATCH(R2C,'Source data'!R4C4:R4C149,0))," & Chr(10) & "INDEX('Source data'!R4C4:R40003C149,MATCH(RC7,'Source data'!R4C136:R40003C136,0),MATCH(R2C,'Source data'!" & _
"149,0))/INDEX('Source data'!R4C4:R40003C149,MATCH(RC7,'Source data'!R4C136:R40003C136,0),MATCH(R1C,'Source data'!R4C4:R4C149,0))" & Chr(10) & "),0)"


If I try to run the macro with above formula, I am getting #1004 Application-Defined or Object-Defined Error
Can some one please help me to fix this?

 

[Fluff]

Hi & welcome to MrExcel
When recording large formula like that the recorder will sometimes lose part of the formula.
Try

   Selection.FormulaR1C1 = "=IFERROR(IF(INDEX('Source data'!R4C4:R40003C149,MATCH(R[-22]C7,'Source data'!R4C136:R40003C136,0),MATCH(R3C[-4],'Source data'!R4C4:R4C149,0))=""N""," & _
      "INDEX('Source data'!R4C4:R40003C149,MATCH(R[-22]C7,'Source data'!R4C136:R40003C136,0),MATCH(R2C[-4],'Source data'!R4C4:R4C149,0)),INDEX('Source data'!R4C4:R40003C149," & _
      "MATCH(R[-22]C7,'Source data'!R4C136:R40003C136,0),MATCH(R2C[-4],'Source data'!R4C4:R4C149,0))/INDEX('Source data'!R4C4:R40003C149," & _
      "MATCH(R[-22]C7,'Source data'!R4C136:R40003C136,0),MATCH(R1C[-4],'Source data'!R4C4:R4C149,0))),0)"

 

[vatrobo1]

Hey Fluff,

Thanks much for the response and quick solution. Based on your input , I corrected the macro generated incorrect formula.

After recording the formula with macro , I am getting the following :

ActiveCell.FormulaR1C1 = _
"=IFERROR(" & Chr(10) & "IF(INDEX('Source data'!R4C4:R40003C149,MATCH(RC7,'Source data'!R4C136:R40003C136,0),MATCH(R3C,'Source data'!R4C4:R4C149,0))=""N""," & Chr(10) & "INDEX('Source data'!R4C4:R40003C149,MATCH(RC7,'Source data'!R4C136:R40003C136,0),MATCH(R2C,'Source data'!R4C4:R4C149,0))," & Chr(10) & "INDEX('Source data'!R4C4:R40003C149,MATCH(RC7,'Source data'!R4C136:R40003C136,0),MATCH(R2C,'Source data'!" & _
"149,0))/INDEX('Source data'!R4C4:R40003C149,MATCH(RC7,'Source data'!R4C136:R40003C136,0),MATCH(R1C,'Source data'!R4C4:R4C149,0))" & Chr(10) & "),0)"

Now I corrected it to :

ActiveCell.FormulaR1C1 = _
"=IFERROR(" & Chr(10) & "IF(INDEX('Source data'!R4C4:R40003C149,MATCH(RC7,'Source data'!R4C136:R40003C136,0),MATCH(R3C,'Source data'!R4C4:R4C149,0))=""N""," & Chr(10) & "INDEX('Source data'!R4C4:R40003C149,MATCH(RC7,'Source data'!R4C136:R40003C136,0),MATCH(R2C,'Source data'!R4C4:R4C149,0))," & Chr(10) & "INDEX('Source data'!R4C4:R40003C149,MATCH(RC7,'Source data'!R4C136:R40003C136,0),MATCH(R2C,'Source data'!" & _
"R4C4:R4C149,0))/INDEX('Source data'!R4C4:R40003C149,MATCH(RC7,'Source data'!R4C136:R40003C136,0),MATCH(R1C,'Source data'!R4C4:R4C149,0))" & Chr(10) & "),0)"
ActiveSheet.Range("H5").Select


It is working perfectly now. Thanks again

 

[Fluff]

You're welcome & thanks for the feedback