# Convert excel formula to vba

#### loopa1

##### Board Regular
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...

\$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.

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### AMAS

##### Active Member
Hi,

This should get you started:

Code:
``````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

##### Board Regular
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

##### Active Member
Hi,

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

AMAS

Yep.

#### loopa1

##### Board Regular
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.

Replies
1
Views
345
Replies
1
Views
199
Replies
8
Views
203
Replies
3
Views
399
Replies
2
Views
235

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,559
Messages
5,832,500
Members
430,136
Latest member
Asir Jefferson

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back