Count match of two criteria

[Adrian]

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?

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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

 

[Adrian]

Tom, and Bob,
Many thanks to you both.

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