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_Ma rket =
"Central") * 1)
Cells(1, 1) = CentralTerms
End Sub

Use CountIf:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"John" wrote in message
...
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_Ma rket =
"Central") * 1)
Cells(1, 1) = CentralTerms
End Sub

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?

In article ,
John wrote:

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_Ma rket =
"Central") * 1)
Cells(1, 1) = CentralTerms
End Sub

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" wrote:

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?

In article ,
John wrote:

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_Ma rket =
"Central") * 1)
Cells(1, 1) = CentralTerms
End Sub

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



In article ,
John wrote:

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?

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"JE McGimpsey" wrote in message
...
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



In article ,
John wrote:

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?

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

In article ,
"Jon Peltier" wrote:

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.

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"JE McGimpsey" wrote in message
...
That's essentially what the third example does, though there are more
efficient ways, especially if the columns aren't contiguous.

In article ,
"Jon Peltier" wrote:

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.