sumproduct and array formulas into VBA

**j.farr3ll** · 08-29-2013, 06:13 AM

Hi all,

I have a workbook which is only 2MB in size. However it has 5000 rows of data. Since I added a number of sumproduct formulas to the sheet the processing speed has dropped to unbareable levels when making any changes to the data.

I was wondering if anyone could help convert these formulas into a single code which I can trigger from a button?

The formulas are:

Sheet 1.

Cell AA5 (and copied down to AA5005).

Formula:

Please Login or Register  to view this content.

Cell AB5 (and copied down to AB5005)

Formula:

Please Login or Register  to view this content.

Cell AC5 (and copied down to AC5005)

Formula:

Please Login or Register  to view this content.

Cell AD5 (and copied down to AD5005)

Formula:

Please Login or Register  to view this content.

Sheet 2

Cell C5

Formula:

Please Login or Register  to view this content.

Cell C8

Formula:

Please Login or Register  to view this content.

Cell C14

Formula:

Please Login or Register  to view this content.

Cell C17

Formula:

Please Login or Register  to view this content.

Cell C27

Formula:

Please Login or Register  to view this content.

Cell C28

Formula:

Please Login or Register  to view this content.

Cell C29

Formula:

Please Login or Register  to view this content.

I know this is a big ask, but am hoping that if 1 formula can be converted into vba then they all can easily enough. Where I am completely stuck is whether it can be done with the two arrays in cells C5 and C14 on sheet 2?

Many Thanks,
James

**:) Sixthsense :)** · 08-29-2013, 06:17 AM

Your formula's can be much more simplified if you show the real data with your current formulas.

Because some of your sumproduct formula's can be converted to sumif(s) and the indirect in sumproducts are another hectic's which consumes more processing time.

So better show a sample workbook to do changes in formula

**j.farr3ll** · 08-29-2013, 07:58 AM

Ok I will try and produce a clean version of the workbook. The other problem I have is that this needs to run on excel2003 or combatibility mode... so speed improving formulas such as countifs can't be used. Hence why I thoguht I could convert to vba.

**:) Sixthsense :)** · 08-29-2013, 08:16 AM

If so please attach a sample workbook based on that we will suggest the suitable method

**j.farr3ll** · 08-29-2013, 09:47 AM

I've attached the full dataset, but stripped out any sensitive information and populated some cells to show the formulas working. I'm sure that a lot of my codes/formulas aren't the most efficient as I've pieced them together with help of users of this formum and information I've found elsewhere on the internet.

Any help speeding this workbook up, would be great.

Thanks,
James