I'm trying to write a VBA function to count the number of matches to two
criteria.
In a worksheet I can use SUMPRODUCT(--(range1="value1"),--(range2="value2"))
but I can't make it work in VBA.

range1 and range2 and value1 are variables, value2 is a string constant.

answer = Application.WorksheetFunction.SumProduct("--(" & range1 & "=""" &
value1 & """)", "--(" & range2 & "=""value2"")")

I am getting type mismatch errors. I suspect the problem is range1 and
range2 ; on a worksheet they are in the form A2:A300 but in VBA they aren't.

How to sort this out? Or is there a better method?

Dim sformula As String

sformula = "SumProduct(--(" & range1.Address & "=""" & value1 & """)," &
_
"--(" & range2.Address & "=""" & value2 & """))"

answer = Evaluate(sformula)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Adrian" wrote in message
...
I'm trying to write a VBA function to count the number of matches to two
criteria.
In a worksheet I can use
SUMPRODUCT(--(range1="value1"),--(range2="value2"))
but I can't make it work in VBA.

range1 and range2 and value1 are variables, value2 is a string constant.

answer = Application.WorksheetFunction.SumProduct("--(" & range1 & "=""" &
value1 & """)", "--(" & range2 & "=""value2"")")

I am getting type mismatch errors. I suspect the problem is range1 and
range2 ; on a worksheet they are in the form A2:A300 but in VBA they
aren't.

How to sort this out? Or is there a better method?

Array evaluation of this type is not supported in VBA. You have to use the
evaluate function to ask Excel to evaluate the formula for you:


answer = Evaluate("SumProduct(--(" & range1.Address & "=""" & _
value1 & """),--(" & range2.Address & "=""value2"")")

This will evaluate the activesheet using the array formula

--
Regards,
Tom Ogilvy



"Adrian" wrote in message
...
I'm trying to write a VBA function to count the number of matches to two
criteria.
In a worksheet I can use
SUMPRODUCT(--(range1="value1"),--(range2="value2"))
but I can't make it work in VBA.

range1 and range2 and value1 are variables, value2 is a string constant.

answer = Application.WorksheetFunction.SumProduct("--(" & range1 & "=""" &
value1 & """)", "--(" & range2 & "=""value2"")")

I am getting type mismatch errors. I suspect the problem is range1 and
range2 ; on a worksheet they are in the form A2:A300 but in VBA they
aren't.

How to sort this out? Or is there a better method?

Tom, and Bob,
Many thanks to you both.

"Tom Ogilvy" wrote:

Array evaluation of this type is not supported in VBA. You have to use the
evaluate function to ask Excel to evaluate the formula for you:


answer = Evaluate("SumProduct(--(" & range1.Address & "=""" & _
value1 & """),--(" & range2.Address & "=""value2"")")

This will evaluate the activesheet using the array formula

--
Regards,
Tom Ogilvy



"Adrian" wrote in message
...
I'm trying to write a VBA function to count the number of matches to two
criteria.
In a worksheet I can use
SUMPRODUCT(--(range1="value1"),--(range2="value2"))
but I can't make it work in VBA.

range1 and range2 and value1 are variables, value2 is a string constant.

answer = Application.WorksheetFunction.SumProduct("--(" & range1 & "=""" &
value1 & """)", "--(" & range2 & "=""value2"")")

I am getting type mismatch errors. I suspect the problem is range1 and
range2 ; on a worksheet they are in the form A2:A300 but in VBA they
aren't.

How to sort this out? Or is there a better method?