VBA Type Mismatch

[John]

I am trying to create a VBA function that looks at a Range of data on Sheet2,
and uses the SumProduct function to return the number of occurances in that
range. I keep getting a Type Mismatch error, but I can't figure out why. Any
suggestions?

Sub TermsMarketBreakdown()
' Breaksdown the number of terms by Market
Dim CentralTerms
Dim Terms_Market
Set Terms_Market = Sheet2.Range("E2:E300")
CentralTerms = Application.WorksheetFunction.SumProduct((Terms_Market =
"Central") * 1)
Cells(1, 1) = CentralTerms
End Sub

 

[Jon Peltier]

Use CountIf:

CentralTerms = Application.WorksheetFunction.CountIf(Terms_Market,"Central")

- Jon

 

[JE McGimpsey]

Because just as within XL, VBA doesn't pass (Terms_Market = "Central")
to SumProduct. Instead it evaluates it, but unlike XL's calculation
engine, which evaluates it as an array, VBA doesn't.


Why not

CentralTerms = Application.WorksheetFunction.Countif( _
Terms_Market, "Central")

instead?

 

[John]

Ok, I used the CountIf function and it worked. However, I will need to use
the SumProduct function to compare multiple ranges in the future. Is there
anyway around this problem?

 

[JE McGimpsey]

One way:

If you're using XL07, you could use COUNTIFS().

or, you could enter the array formula in the worksheet, then read the
value back, e.g., for

=SUMPRODUCT(--(A1:A40="A"),--(B1:B40=1))

use

Const sFORMULA As String = _
"=SUMPRODUCT(--(^1=^2),--(^3=^4))"
Dim rRange1 As Range
Dim rRange2 As Range
Dim nResult As Long
Dim sCrit1 As String
Dim sCrit2 As String

Set rRange1 = Range("A1:A40")
sCrit1 = """A"""
Set rRange2 = Range("B1:B40")
sCrit2 = "1"
With Range("Z1")
.FormulaArray = Replace(Replace(Replace(Replace( _
sFORMULA, "^4", sCrit2, 1), "^3", rRange2.Address, 1), _
"^2", sCrit1, 1), "^1", rRange1.Address, 1)
nResult = .Value
.Clear
End With
MsgBox nResult

Or you could do it all in a variant array. Using the example above:

Dim vArr As Variant
Dim i As Long
Dim sCrit1 As String
Dim nCrit2 As Long
Dim nResult As Long

sCrit1 = "A"
nCrit2 = 1
vArr = Range("A1:B40").Value
For i = 1 To UBound(vArr, 1)
If vArr(i, 1) = sCrit1 Then _
nResult = nResult - (vArr(i, 2) = nCrit2)
Next i
MsgBox nResult

 

[Jon Peltier]

Or you could read the ranges into VBA arrays, and do the necessary
calculations on them. I wouldn't bother with sumproduct I don't think. I'd
just loop, do my comparisons within the loop, and keep score with a counting
variable.

- Jon

 

[JE McGimpsey]

That's essentially what the third example does, though there are more
efficient ways, especially if the columns aren't contiguous.

 

[Jon Peltier]

Doh! I was reading the newsgroup in a busy lobby, and forgot to read your
whole post.

- Jon