I was hoping someone can help me.

I am writing a script that parses though an excel file and extract the
unique entries and sums the quantities with respect to certain criteria (ie
Month or Year). I currently have an excel formula that does what I am looking
for, but I need to convert it to a VB script.

The formula looks like:

=SUMPRODUCT(($C$2:$C$30="boxes")*($E$2:$E$30="Janu ary")*($D$2:$D$30))

Where C2:C30 contains the item, E2:E30 is the month, and D2:D30 contains
the quantity of the items. So this example counts the number of boxes from
January in this data range.


In my script, I am trying to write:

Sheets("Summary").Cells(n, 4) =
Application.WorksheetFunction.SumProduct((Range("C 2:C30") = "boxes")
*(Range("E2:E30") = "January")* (Range("D2:D60")))

However, when I run it, I get a "Run-time 13 error : Type mismatch" error. I
have been unable to debug this statement. So I was hoping someone could help
me with this.

I greatly appreciate any assistance you could provide.

Thanks,

-Michael

Use Evaluate

Sheets("Summary").Cells(n, 4) =
Activesheet.Evaluate("SUMPRODUCT(($C$2:$C$30=""box es"")*" & _

"($E$2:$E$30=""January"")*($D$2:$D$30))")


--

HTH

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


"Michael" wrote in message
...
I was hoping someone can help me.

I am writing a script that parses though an excel file and extract the
unique entries and sums the quantities with respect to certain criteria
(ie
Month or Year). I currently have an excel formula that does what I am
looking
for, but I need to convert it to a VB script.

The formula looks like:

=SUMPRODUCT(($C$2:$C$30="boxes")*($E$2:$E$30="Janu ary")*($D$2:$D$30))

Where C2:C30 contains the item, E2:E30 is the month, and D2:D30 contains
the quantity of the items. So this example counts the number of boxes from
January in this data range.


In my script, I am trying to write:

Sheets("Summary").Cells(n, 4) =
Application.WorksheetFunction.SumProduct((Range("C 2:C30") = "boxes")
*(Range("E2:E30") = "January")* (Range("D2:D60")))

However, when I run it, I get a "Run-time 13 error : Type mismatch" error.
I
have been unable to debug this statement. So I was hoping someone could
help
me with this.

I greatly appreciate any assistance you could provide.

Thanks,

-Michael

Michael,

Use, for example:

Dim myCount As Integer
myCount = Evaluate("=SUMPRODUCT(($C$2:$C$30=""boxes"")*($E$2 :$E$30=""January"")*($D$2:$D$30))")
MsgBox "There were " & myCount & " boxes shipped in January."

HTH,
Bernie
MS Excel MVP


"Michael" wrote in message
...
I was hoping someone can help me.

I am writing a script that parses though an excel file and extract the
unique entries and sums the quantities with respect to certain criteria (ie
Month or Year). I currently have an excel formula that does what I am looking
for, but I need to convert it to a VB script.

The formula looks like:

=SUMPRODUCT(($C$2:$C$30="boxes")*($E$2:$E$30="Janu ary")*($D$2:$D$30))

Where C2:C30 contains the item, E2:E30 is the month, and D2:D30 contains
the quantity of the items. So this example counts the number of boxes from
January in this data range.


In my script, I am trying to write:

Sheets("Summary").Cells(n, 4) =
Application.WorksheetFunction.SumProduct((Range("C 2:C30") = "boxes")
*(Range("E2:E30") = "January")* (Range("D2:D60")))

However, when I run it, I get a "Run-time 13 error : Type mismatch" error. I
have been unable to debug this statement. So I was hoping someone could help
me with this.

I greatly appreciate any assistance you could provide.

Thanks,

-Michael

Bernie,

Thank you. I do have one more question for you. How can I do the same thing,
but be a little more modular:

Like:
Evaluate("=SUMPRODUCT(($C$2:$C$30=Sheets("Inventor y").cells(n,1))*($E$2:$E$30=""January"")*($D$2:$D$ 30))")

I want to use the same function for each item. I was playing around with it,
but i could not get it to return the right value.

Thanks!

-Michael

"Bernie Deitrick" wrote:

Michael,

Use, for example:

Dim myCount As Integer
myCount = Evaluate("=SUMPRODUCT(($C$2:$C$30=""boxes"")*($E$2 :$E$30=""January"")*($D$2:$D$30))")
MsgBox "There were " & myCount & " boxes shipped in January."

HTH,
Bernie
MS Excel MVP


"Michael" wrote in message
...
I was hoping someone can help me.

I am writing a script that parses though an excel file and extract the
unique entries and sums the quantities with respect to certain criteria (ie
Month or Year). I currently have an excel formula that does what I am looking
for, but I need to convert it to a VB script.

The formula looks like:

=SUMPRODUCT(($C$2:$C$30="boxes")*($E$2:$E$30="Janu ary")*($D$2:$D$30))

Where C2:C30 contains the item, E2:E30 is the month, and D2:D30 contains
the quantity of the items. So this example counts the number of boxes from
January in this data range.


In my script, I am trying to write:

Sheets("Summary").Cells(n, 4) =
Application.WorksheetFunction.SumProduct((Range("C 2:C30") = "boxes")
*(Range("E2:E30") = "January")* (Range("D2:D60")))

However, when I run it, I get a "Run-time 13 error : Type mismatch" error. I
have been unable to debug this statement. So I was hoping someone could help
me with this.

I greatly appreciate any assistance you could provide.

Thanks,

-Michael

Evaluate("=SUMPRODUCT(($C$2:$C$30=""" & Sheets("Inventory").cells(n,1) &
""")*($E$2:$E$30=""January"")*($D$2:$D$30))")

assuming that it is text

--

HTH

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


"Michael" wrote in message
...
Bernie,

Thank you. I do have one more question for you. How can I do the same
thing,
but be a little more modular:

Like:

Evaluate("=SUMPRODUCT(($C$2:$C$30=Sheets("Inventor y").cells(n,1))*($E$2:$E$3
0=""January"")*($D$2:$D$30))")

I want to use the same function for each item. I was playing around with
it,
but i could not get it to return the right value.

Thanks!

-Michael

"Bernie Deitrick" wrote:

Michael,

Use, for example:

Dim myCount As Integer
myCount =
Evaluate("=SUMPRODUCT(($C$2:$C$30=""boxes"")*($E$2 :$E$30=""January"")*($D$2:
$D$30))")
MsgBox "There were " & myCount & " boxes shipped in January."

HTH,
Bernie
MS Excel MVP


"Michael" wrote in message
...
I was hoping someone can help me.

I am writing a script that parses though an excel file and extract the
unique entries and sums the quantities with respect to certain
criteria (ie
Month or Year). I currently have an excel formula that does what I am
looking
for, but I need to convert it to a VB script.

The formula looks like:

=SUMPRODUCT(($C$2:$C$30="boxes")*($E$2:$E$30="Janu ary")*($D$2:$D$30))

Where C2:C30 contains the item, E2:E30 is the month, and D2:D30
contains
the quantity of the items. So this example counts the number of boxes
from
January in this data range.


In my script, I am trying to write:

Sheets("Summary").Cells(n, 4) =
Application.WorksheetFunction.SumProduct((Range("C 2:C30") = "boxes")
*(Range("E2:E30") = "January")* (Range("D2:D60")))

However, when I run it, I get a "Run-time 13 error : Type mismatch"
error. I
have been unable to debug this statement. So I was hoping someone
could help
me with this.

I greatly appreciate any assistance you could provide.

Thanks,

-Michael