can i use sumproduct this way in VB?

[Guest]

Can i use sumproduct in VB the same way in a cell of excel. The only
difference being i am using
application.worksheetfunction.sumproduct(--(Worksheets("A").Range("A2:A9") _
=Worksheets.Range("A1),--(Worksheets("A").Range("b2:b9") _
=Worksheets.Range("b1),--(Worksheets("A").Range("c2:c9"))


Is that possible, or do i need to use a different method to accomplish the
same task?

 

[Dave Peterson]

You've got a few typos in your code--"a1 and "b1 are missing ending quotes. And
each of those cells is missing the worksheet name:

Worksheets.Range("A1)



You can use application.evaluate to do what you want.

I like this style:

Option Explicit
Sub testme02()

Dim RngA As Range
Dim RngB As Range
Dim RngC As Range
Dim CellA As Range
Dim CellB As Range

Dim myFormula As String

With Worksheets("a")
Set RngA = .Range("a2:A9")
Set RngB = .Range("b2:b9")
Set RngC = .Range("c2:c9")
Set CellA = .Range("a1")
Set CellB = .Range("B1")
End With

myFormula = "sumproduct(--(" & RngA.Address(external:=True) _
& "=" & CellA.Address(external:=True) & ")," _
& "--(" & RngB.Address(external:=True) _
& "=" & CellB.Address(external:=True) & ")," _
& "(" & RngC.Address(external:=True) & "))"

Debug.Print myFormula 'nice for testing!

MsgBox Application.Evaluate(myFormula)

End Sub

 

[Guest]

Andy,

Can't quite work out your code, but I wrote this little function that works:

Function a()
a = Application.WorksheetFunction.SumProduct( _
Worksheets("A").Range("A2:A9"), _
Worksheets("A").Range("b2:b9"), _
Worksheets("A").Range("c2:c9"))
End Function

if you are trying to do the sumproduct of the above three ranges.

 

[Guest]

Is there anyway to put that into a function script that always updates itself?

 

[Dave Peterson]

Maybe...

What do you mean?

But if you're writing a VBA function to replace the =sumproduct() worksheet
function, you'll find that the =sumproduct() worksheet function that's built
into excel is much, much faster.