Macro to execute Sumifs or other formula faster

[ALNER]

Hi Everyone,

I attached an excel file which I hope it can demonstrate the problem.

The main objective is to match items based on various criteria located in different columns. I have created various "SUMIFS" depending on these criteria. All matched items are marked as "YES" and then user will opt to move out the items to different sheet.

My problem is, at the time if copying the sumif formula, it takes very long time and sometimes excel will no longer respond. Is there a way we can code this with macros so that it will run faster?

I use the below code to update/copy the SUMIF function to all rows for one of the criteria. This code is repeated with different criteria and cell where the formula is copied from.

Please Login or Register  to view this content.

I hope someone can find solution.

[ferday]

the short answers are

yes, it freezes currently
yes, VBA will be better
yes, you can use your formulas in VBA with some modifications

i would write each formula into a function, then call the correct function based on the user input.

[ALNER]

Hi ferday,

I'm a newbie in VBA. Can you write me how to write each formula into a function?