• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumproduct VBA

M

Manuel998

Member
I have a large data set that i have setup with the following formulae: SUMPRODUCT(--('2015'!$B$25:$B$13771&'2015'!$D$25:$D$13771=Master!$A30&Master!$B30),INDEX('2015'!$F$25:$Q$13771,,MATCH(Master!J$4,'2015'!$F$22:$Q$22,0))).

The sheet is become very slow and i was wondering if i can embed the formulae in VBA and the result is displayed as text / values only in the cell to speed up the dashboard and charts? Thanks for any help.
 
I'd recommend uploading sample workbook along with expected result.

In most cases, formula is faster than VBA, when properly set up. One of few exception is to use Variant Array, Collection/Dictionary to perform calculation operation.

Alternate approach is to use one of following methods...
1. Pivot Table to summarize data, and slicers/filters to serve as criteria field.
2. Advanced Filter Copy to extract smaller data set that match criteria, and to perform calculation on.
 
You must log in or register to reply here.
Back
Top