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

"Dave Peterson" wrote:

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

Andy wrote:

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