VBA puts formula into dynamic range

[helpexcel]

Hi,

I have no idea why this won't work. I get an application-defined/object-defined error. I have wsC defined earlier in the code. Range1/2/3 are Defined Name Ranges in the workbook.
Any help is appreciated.

Dim lastrow2, LastColumn As Long
LastColumn = wsC.Cells(2, Columns.Count).End(xlToLeft).Column
lastrow2 = wsC.Cells(Rows.Count, "A").End(xlUp).Row


Range(Cells(3, 26), Cells(lastrow2, LastColumn)).FormulaR1C1 = "=IF(SUMPRODUCT((Range1=Sheet2!$A3)+0,(Range2=Sheet2!Z$2)+0,(Range3))=0,"""",SUMPRODUCT((Range1=Sheet2!$A3)+0,(Range2=Sheet2!AA$2)+0,(Range3))"

 

[Norie]

Are Range1, Range2 etc named ranges?

If they are then all you are missing is a closing parentheses right at the end.

 

[helpexcel]

Thanks, they are named ranges. I updated the formula and checked it on the worksheet and it works, but it still errors out in the macro.

Range(Cells(3, 26), Cells(lastrow2, LastColumn)).FormulaR1C1 = "=IF(SUMPRODUCT((Range1=Sheet2!$A3)+0,(Range2=Sheet2!Z$2)+0,(Range3))=0,"""",SUMPRODUCT((Range1=Sheet2!$A3)+0,(Range2=Sheet2!Z$2)+0,(Range3)))"

 

[Norie]

Which sheet are the formulas meant to be going on?

Does this work?

With wsC
    .Range(.Cells(3, 26), .Cells(lastrow2, LastColumn)).FormulaR1C1 = "=IF(SUMPRODUCT((Range1=Sheet2!$A3)+0,(Range2=Sheet2!Z$2)+0,(Range3))=0,"""",SUMPRODUCT((Range1=Sheet2!$A3)+0,(Range2=Sheet2!AA$2)+0,(Range3)))"
End With

 

[helpexcel]

Still errors out.

The formulas are going into Sheet2. I wonder if wsC is messing it up, because I have code to say wsC=Sheet2 (this is the shorthand version).

 

[helpexcel]

GOT IT! I took the R1C1 out.

 

[Norie]

Oops, didn't notice the R1C1 there.

 

[helpexcel]

any thoughts on how i change the index/match that sums a dynamic range in a column to sum a dynamic range in a row?