Evaluate SumProduct in VBA

mpham168

New Member
Joined
Jul 21, 2011
Messages
7
Hi all,

I am attempting to run Evaluate Sumproduct in VBA across different sheets

I have one sheet that stores all the data called "DATA"

I have attempted to set the ranges so that the function can reference these more easily

Code:
Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, Rng4 As Range
Set Rng1 = Sheets("DATA").Range("C:C") ' Stock
Set Rng2 = Sheets("DATA").Range("A:A") ' Account number
Set Rng3 = Sheets("DATA").Range("E:E") ' Incoming
Set Rng4 = Sheets("DATA").Range("H:H") ' On hand

Sheets("CustomerX").select

Range("E2") = Evaluate("=SUMPRODUCT((Rng2 = 2471)*(rng1 = [U]Sheets("customerX").Range("A2")[/U],Rng3)")

Eventually I will need to run this formula for every item requested in column A.... for Sheet CusomterX

Any pointers in where I've gone wrong or astray would be greatly appreciated.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Evaluate requires a formula as it would appear on a worksheet. You are passing it arguments as if you are using WorksheetFunction.SumProduct. Why don't you put the formula in the cell and convert it to a value?
 
Upvote 0
Thanks for the reply.

I'm not particularly the best person explaining what I'm doing in excel but here it goes...

I put the code is as it would appear in the cell.. but I have over 300 Rows... (and this changes from customer to customer).

So I was hoping to be able to feed it some defined Ranges and pull up the Values. The reason I have opted for the evaluate is that I have 8 customer sheets at the moment and this will grow. I only want to trigger the Evaluate function on a particular sheet when I need to, rather then having the sumproduct formulas sitting there and refreshing each time. Each customer sheet has a different number of rows....

Not sure how else to explain but hopefully it sheds light on what I'm trying to do?
 
Upvote 0
I understand. In your macro put the formula in the cell, then convert it to a value (Cell.Value = Cell.Value).
 
Upvote 0
\
Code:
Range("E2") = Evaluate("=SUMPRODUCT((Rng2 = 2471)*(rng1 = [U]Sheets("customerX").Range("A2")[/U],Rng3)")
There is a syntax problem with the above code line (missing a closing parenthesis) and the formula's intent is not entirely clear so I'm not sure where it should go. Show us the actual formula that is in the worksheet cell (which I presume actually works correctly) so that we can convert it to VB code for you.
 
Upvote 0
To use evaluate, you build a formula using a text string.
That formula has to be a valid formula as if you entered it in a cell

Now a formula in a cell doesn't know what Rng2 and Rng3 are...
It's a variable you created in previous code.
YOu have to concatenate the values of those variables into the text string.
However, since the variables are range objects, you need to use Rng2.Address
Also, since the the Sheets("customerX").Range("A2") is NOT a vriable, then you need to write that as you would in a cell formula
CustomerX!A2

Try
Range("E2") = Evaluate("=SUMPRODUCT((" & Rng2.Address & "=2471)*(" & rng1.Address & "= customerX!A2)," & Rng3.Address & ")")
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top