Error 1004

[Ramux]

Hi All

Im creating a macro which inserts formulas and then autofill the column to last the last row. All formulas works except this one:

Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[28]="""",IF(RC[-1]=""Complete"",RC[-3]+IFERROR(VLOOKUP(CONCATENATE(RC[-2],""-"",RC[-5],""-"",,RC[-4]),MasterData!C[-9]:C[-4],6,FALSE),""""),IF(RC[1]="""","""",IF(RC[-3]="""","""",IF(IFERROR(VLOOKUP(C[-6],'Priority Sheet'!C[-9]:C[-6],3,FALSE),0)>0,VLOOKUP(C[-6],'Priority Sheet'!C[-9]:C[-6],3,FALSE),RC[-3]+IFERROR(VLOOKUP(CONCATENATE(RC[-2],""-"",RC[-5],""-"",,MasterData!C1:C6,6,FALSE),""""))))),RC[28])"
Selection.AutoFill Destination:=Range("J2:J" & lastRow)

This would give the 1004 error: "Application-defined or Object-defined error"

Can anyone help med understand why it dosent work?

Thanks in advance!

 

[JoeMo]

I haven't looked carefully at your formula so I may be missing some error in it, but assuming its correct it may be that it has too many characters and exceeds an allowed limit for passing a string. My memory is fuzzy here, but that limit may be 255 (or 256?). Try breaking the formula string in half (to two strings) like:

sForm1 = "first half of formula (starting with "= ...)"
sForm2 = "second half"
ActiveCell.FormulaR1C1 =sForm1 & sForm2

 

[Ramux]

I haven't looked carefully at your formula so I may be missing some error in it, but assuming its correct it may be that it has too many characters and exceeds an allowed limit for passing a string. My memory is fuzzy here, but that limit may be 255 (or 256?). Try breaking the formula string in half (to two strings) like:

sForm1 = "first half of formula (starting with "= ...)"
sForm2 = "second half"
ActiveCell.FormulaR1C1 =sForm1 & sForm2

I tried to do this:

sForm1 = "=IF(RC[28]="""",IF(RC[-1]=""Complete"",RC[-3]+IFERROR(VLOOKUP(CONCATENATE(RC[-2],""-"",RC[-5],""-"",,RC[-4]),MasterData!C[-9]:C[-4],6,FALSE),""""),"

sForm2 = "IF(RC[1]="""","""",IF(RC[-3]="""","""",IF(IFERROR(VLOOKUP(C[-6],'Priority Sheet'!C[-9]:C[-6],3,FALSE),0)>0,VLOOKUP(C[-6],'Priority Sheet'!C[-9]:C[-6],3,FALSE),RC[-3]+"

sForm3 = "IFERROR(VLOOKUP(CONCATENATE(RC[-2],""-"",RC[-5],""-"",,MasterData!C1:C6,6,FALSE),""""))))),RC[28])"


Range("J2").Select
ActiveCell.FormulaR1C1 = sForm1 & sForm2 & sForm3
Selection.AutoFill Destination:=Range("J2:J" & lastRow)

Im still getting the same error

 

[JoeMo]

Have you tested your formula by manually entering it in a worksheet?