Using Sumproduct in a subroutine

[jcvoth]

Dear all,

I am working on an automation subroutine (aren't we all?) that wil
allow me to speed up some lookups. A sumproduct is the appropriat
function, except I would like to keep the calculation within VB instea
of in a cell.

What I have so far is causing a type mismatch or an undefined procedur
(depending on how I tinker with it.) I have a feeling there is
problem with the syntax of my sumproduct.

Any ideas?

Thank you in advance for your assistance!
-Jarrod

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("Pric
Lists").Range("a2:a9000")
Set ENDrange = Workbooks("DFI Master File.xls").Worksheets("Pric
Lists").Range("b2:b9000")
Set PLrange = Workbooks("DFI Master File.xls").Worksheets("Pric
Lists").Range("c2:c9000")
Set ITrange = Workbooks("DFI Master File.xls").Worksheets("Pric
Lists").Range("d2:d9000")
Set DPrange = Workbooks("DFI Master File.xls").Worksheets("Pric
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

 

[Trevor Shuttleworth]

Jarrod

can't test your code but first thought is that range doesn't have a String
Property ... try changing this to Text or maybe Value (the default)

Regards

Trevor

 

[Tom Ogilvy]

There is no property of a range called String.

also, even if you use value, i doubt this will work. Sumproduct pretty much
works as it was designed (multiplies arrays together) in VBA.


for example,
this works: application.Sumproduct(Range("A1:A10"),Range("B1:B10"))

but this does not application.Sumproduct(Range("A1:A10") *
Range("B1:B10")) ' syntax error

and this certainly doesn't application.Sumproduct((Range("A1:A10" )=1) *
Range("B1:B10")) ' type mismatch error
nor this application.Sumproduct(--(Range("A1:A10" )=1), Range("B1:B10"))
' type mismatch error again

If you want to do what you are attemption in VBA, you will have to build a
formula identical to what you would use in a cell, then use Evaluate to get
the answer.

 

[Frank Kabel]

Hi
though this kind of SUMPRODUCT syntax works in the worksheet (using
comparisons) it does NOT work used with Application.worksheetfunctions.
this only accepts the 'standard' SUMPRODUCT syntax
(SUMPRODUCT(rng1,rng2,....))

 

[Martin]

Hi,
try entering it as a string -
dim mystring as string
mystring ="Sumproduct.........."
Cells(myrw, mycol).value=mystring