Using Sumproduct in a subroutine

  • Thread starter Thread starter jcvoth
  • Start date Start date
J

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
 
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
 
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.
 
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,....))
 
Hi,
try entering it as a string -
dim mystring as string
mystring ="Sumproduct.........."
Cells(myrw, mycol).value=mystring
 
Back
Top