Convert excel formula to vba

[loopa1]

Hi all

I want to use sumproduct formula in approx. 50,000 cells, but asking excel to calculate this "kills" excel.

Is there anyway I can use this formula in VBA so instead of adding the formula to each cell and then calculating the value for each cell, it just calculates the value in vba and places it in the appropriate cell.

Current working excel formula is as follows...

=IF($A5="","",SUMPRODUCT(--($A5=Sheet1!$CC$2:$CC$5028)*(C$2=Sheet1!$AD$2:$AD$5028)*($B5=Sheet1!$H$2:$H$5028))+SUMPRODUCT(--($A5=Sheet1!$CC$2:$CC$5028)*(C$2=Sheet1!$AW$2:$AW$5028)*($B5=Sheet1!$H$2:$H$5028)))

$A5 will be over the range of $A5 to $A3716
$B5 will be over the range of $B5 to $B3716
C$2 will be over the range of C$2 to ES$2
and the result of this calculation will be placed in C5:ES3716

Thanks.

 

[AMAS]

Hi,

This should get you started:

Sub Trial()
With Range("C5:ES3716")
.Formula = "=IF(R[4]C1="""","""",SUMPRODUCT(--(R[4]C1=Sheet1!R2C81:R5028C81)*(R2C[2]=Sheet1!R2C30:R5028C30)*(R[4]C2=Sheet1!R2C8:R5028C8))+SUMPRODUCT(--(R[4]C1=Sheet1!R2C81:R5028C81)*(R2C[2]=Sheet1!R2C49:R5028C49)*(R[4]C2=Sheet1!R2C8:R5028C8)))"
.Value = .Value
End With
End Sub

AMAS

 

[loopa1]

Thanks AMAS, but somethings not right here...it's not producing the right results. I'm trying to understand why, but struggling to understand the syntax of some of your formula.

 

[AMAS]

Hi,

Which is the first cell where your forumla goes (e.g. C5)?

AMAS

 

[loopa1]

Yep.

 

[loopa1]

It's ok - managed to do it with a series of if statements, probably not the best way of doing it, but it's working.

Thanks for your help.