Sumproduct within a ub

[jcvoth]

This a continuation of my post in

http://www.mrexcel.com/board2/viewtopic.php?t=83204

but because the question is unrelated to For Each, I think it is more appropriately in another topic.

My goal is to do a sumproduct within a sub. The following formula works correctly.

=SUMPRODUCT(($C$2:$C$9=H3)*($D$2:$D$9=I3)*($A$2:$A$9<=G3)*($B$2:$B$9>=G3)*($E$2:$E$9))

However, the problem gets more complicated when the lookups reside in another document. I felt it would be more appropriate to automate the task in a sub. What follows is where I am so far:

Dim cctr As Integer
mytimer = Timer
master = "E:\Sales Accounting\D F I\DFI Master File.xls"
'Workbooks.Open Filename:=(master)
'ActiveWindow.ActivateNext
lastrow = Range("A65536").End(xlUp).Row
cctr = InputBox("What Cost Center should be updated?")
Set BEGrange = Workbooks("DFI Master File.xls").Worksheets("Price Lists").Range("a2:a9000")
Set ENDrange = Workbooks("DFI Master File.xls").Worksheets("Price Lists").Range("b2:b9000")
Set PLrange = Workbooks("DFI Master File.xls").Worksheets("Price Lists").Range("c2:c9000")
Set ITrange = Workbooks("DFI Master File.xls").Worksheets("Price Lists").Range("d2:d9000")
Set DPrange = Workbooks("DFI Master File.xls").Worksheets("Price Lists").Range("e2:e9000")
For i = 2 To lastrow

If Range("v" & i).Value = cctr Then Range("H" & i).Value = Application.WorksheetFunction.SumProduct((PLrange = Range("p" & i).String) * (ITrange = Range("f" & i).String) * (BEGrange <= Range("e" & i).Value) * (ENDrange >= Range("e" & i).Value) * (DPrange))

Next i

I am defining the lookup paths for each portion of the array to simplify the application code. The error I am receiving is a runtime error. Object doesn't support this product or method.

I interpret this error to mean there is a problem with the equation syntax, but for the life of me, I cannot figure out what it is.

Thank you for taking the time to read this far!

-Jarrod

[polly]

Maybe try "record macro" while building the formula in a practice sheet (cell) to see how the syntax turns out. I did that with sumproduct and the syntax was very different than what your post showed.

[jcvoth]

well, the format for entering a formula in a cell vs entering it in VB seems to be different.

As an example, in a A5, I could enter:
=a3+a4

but in VBA, I would need to enter
range("a5") = Application.WorksheetFunction.Sum(range("a3") + range("a4")

I modified my code to not have a type declaration in the code, but it is now calling it a type mismatch... Still frustrated...

[polly]

Just a thought, the format is different in VBA.
I Macro record (Tools|Macro|Record new macro) while I built this formula in excel =SUMPRODUCT(A12:A18=7,B12:B18,E13:E19) stop record.

Then I open VBA to look in the module for the worksheet & the VBA version of the same formula looks like this

ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(R[11]C:R[17]C=7,R[11]C[1]:R[17]C[1],R[12]C[4]:R[18]C[4])"

Which is what I was trying to say earlier

[jcvoth]

Oh, I underdtand. Yeah, that is R1C1 format, which although makes more logical sense, is much more difficult to look at. Esentially, it says that A1 = R1C1 and B32 = R32C2.

I did come up with a workaround, although I will probably get hammered for being inefficient...

I pushed the equation into range("h" & i) and then copied over the whole column and pasted values. Right now it is taking approximately 600 seconds to evaluate 5000 rows of data, which seems slow to me, but if it is the best I can do, then it is the best i can do.

I found an article explaining that boolean arrays cannot be entered in VB. So instead of allowing VB to calculate it, you have to use evaluate and then print the result in the desired range, but for some reason that didn't work either. The only way that worked was doing range("h" & i).formula = "=sumproduct((blah)*(blah)*(blah))"

Thanks for your help!
-Jarrod