SUMPRODUCT in VBA

[lukasz_rz]

Guys.

This is quite urgent, but I hope you can easily solve my problem.

I try to put formula:

=IF(AND(COUNTIF(AT!$B:$B;$B2)=0;$E2=List!$B$6);"X";IF(AND(COUNTIF(Closed;$E2)>0;SUMPRODUCT((AT!B:B=B2)*(AT!H:H=""))>0);"XX";""))

into VBA code like this to place that formula in multiple cells in one column:

Range("Q2:Q" & Range("B" & Rows.Count).End(xlUp).Row).FormulaR1C1 = _
 "=IF(AND(COUNTIF(AT!C2:C2,R[0]C2)=0,R[0]C5=List!R6C2),""X"",IF(AND(COUNTIF(Closed,R[0]C5)>0,[SIZE=3][B]SUMPRODUCT((AT!C2:C2=R[0]C2)*(AT!C8:C8=""""))>0)[/B][/SIZE],""XX"",""""))"

Unfortunately, the SUMPRODUCT part is causing a problem - excel is not responding when I run the macro. Any suggestions what I should modify to make that thing work?

Thx in advance!

 

[CalcSux78]

Have you attempted using the Macro Recorder to record your formula? It will give you the R1C1 formula equivalent to the worksheet formula.

Forgive me if this is trivial. Start with our formula in the correct cell. Click the macro recorder in the lower status bar. Change a character in your formula (i.e. change the "S" in SUMPRODUCT to a lower case "s".) Stop the recorder and view the newly created VBA Code.