sumif or sumthing else?

Hello all,
I have learned an incredible amount here and would like to thank
everyone for that. Unfortunately, I have come up with something that I
can't find a clear enough answer for.

I have a macro that takes four seperate csv files exported from another
program and compiles them onto a single Excel worksheet. The csv files
always have a different number of rows and, depending on whose machine
they were exported on, the columns are in different orders and possibly
different amounts. As a result I have had to keep the macro as
"generic" as possible to keep it from breaking when used by different
users. I had originally put the output from the csv files into pivot
tables but determined that countif would be faster and more compact. So
far it has been, now that I have figured out how to make most of it
work. I even figured out how to find the right column to look at no
matter where it was in the file.

The problem is with the last of the four csv files. The details are
that I have x number of products (varies by day) stored in 2 locations.
I need to find out how many of product A are in location 1, how many in
location 2, how many of product B are in location 1, how many in
location 2, etc, until I run out of products. I have tried a few of the
examples posted here in the past and either get a mismatch error or a
zero sum.

What I need is VB code that will use variables for the range(s) due to
the changing nature of the source, that will use variables for the
criteria since it will be looping through the product list that is
subject to change, and will produce two seperate variables that can be
used as cells(r,12).value and cells(r,13).value (location 1 and
location 2).

I would rather start "fresh" but will post my current broken code if
requested to.

Thanks in advance.
Bill

Instead of sumif, use sumproduct.

=sumproduct(--($A$1:$A$100=M1),--($B$1:$B$100=N1))

all the combinations of products and location in column M and N

Product1 Location1
Product1 Location2
Product2 Location3
Product3 Location2
Product3 Location3

the formula above assumes your data is in column A (product) and Column B
(location).

In code you would do something like:

Dim rng1 as Range, rng2 as Range, cell as Range
Dim cnt as Long
set rng1 = Range("A1:A100")
set rng2 = Range("B1:B100")
for each cell in Range("M1:M20")
cnt = Application.Evaluate("Sumproduct(--( & _
rng1.Address(0,0,xlA1,true) & "=" & cell.Address & _
"),--(" & rng2.Address(0,0,xlA1,true) & "=" & cell.offset(0,1).Address
& _
")")
cell.offset(0,2).Value = cnt
Next

--
Regards,
Tom Ogilvy



"Bill" <[email protected]> wrote in message
news:[email protected]...
> Hello all,
> I have learned an incredible amount here and would like to thank
> everyone for that. Unfortunately, I have come up with something that I
> can't find a clear enough answer for.
>
> I have a macro that takes four seperate csv files exported from another
> program and compiles them onto a single Excel worksheet. The csv files
> always have a different number of rows and, depending on whose machine
> they were exported on, the columns are in different orders and possibly
> different amounts. As a result I have had to keep the macro as
> "generic" as possible to keep it from breaking when used by different
> users. I had originally put the output from the csv files into pivot
> tables but determined that countif would be faster and more compact. So
> far it has been, now that I have figured out how to make most of it
> work. I even figured out how to find the right column to look at no
> matter where it was in the file.
>
> The problem is with the last of the four csv files. The details are
> that I have x number of products (varies by day) stored in 2 locations.
> I need to find out how many of product A are in location 1, how many in
> location 2, how many of product B are in location 1, how many in
> location 2, etc, until I run out of products. I have tried a few of the
> examples posted here in the past and either get a mismatch error or a
> zero sum.
>
> What I need is VB code that will use variables for the range(s) due to
> the changing nature of the source, that will use variables for the
> criteria since it will be looping through the product list that is
> subject to change, and will produce two seperate variables that can be
> used as cells(r,12).value and cells(r,13).value (location 1 and
> location 2).
>
> I would rather start "fresh" but will post my current broken code if
> requested to.
>
> Thanks in advance.
> Bill
>

Thanks, Tom.
Basically, I have most of what follows in place. Everything is Dim'd
and Set correctly. I think what's causing the problem is where you are
using "for each cell in Range", I am using "For i=1 to ro" where ro
represents the variable number of products. I needed to do that because
(I forgot to mention) there are subtotal rows in the report worksheet
that I need to skip over. How can I convert cell.address to account for
the i= that I have available. I tried to change it to the Prd and Loc1
(or Loc2) variables and got cnt=Error 2015. On the report worksheet
there are two columns per product, one for each location, so I think I
will need two counts. Hopefully I am not confusing you with my
confusion.

Thanks again,
Bill

Tom Ogilvy wrote:
> Instead of sumif, use sumproduct.
---8<---snip--->8---
>
> In code you would do something like:
>
> Dim rng1 as Range, rng2 as Range, cell as Range
> Dim cnt as Long
> set rng1 = Range("A1:A100")
> set rng2 = Range("B1:B100")
> for each cell in Range("M1:M20")
> cnt = Application.Evaluate("Sumproduct(--( & _
> rng1.Address(0,0,xlA1,true) & "=" & cell.Address & _
> "),--(" & rng2.Address(0,0,xlA1,true) & "=" & cell.offset(0,1).Address
> & _
> ")")
> cell.offset(0,2).Value = cnt
> Next
>
> --
> Regards,
> Tom Ogilvy
>
>

I should also point out that frequently there will be a zero quanitity
in each location. Not sure if that will make a difference but it might.

Okay. So, if I put =SumProduct(etc) into a cell in a blank worksheet
and set the rest of it up as shown it works exactly as expected.
However, on that same worksheet if I put the code into a macro and
execute it I invariably get a "Type Mismatch" error at cnt= . I even
deleted the formula from the cell to make sure it wasn't interfering
somehow. While typing the code I found that there was a " missing from
before the & in front of rng1. When I put it in, VB accepted the line
of code but it still generates the error. What am I missing?

Bill

If you get a type mismatch, the formula is probably returning an error
meaning perhaps it isn't set up properly or you have error values in your
data.

--
Regards,
Tom Ogilvy


"Bill" <[email protected]> wrote in message
news:[email protected]...
> Okay. So, if I put =SumProduct(etc) into a cell in a blank worksheet
> and set the rest of it up as shown it works exactly as expected.
> However, on that same worksheet if I put the code into a macro and
> execute it I invariably get a "Type Mismatch" error at cnt= . I even
> deleted the formula from the cell to make sure it wasn't interfering
> somehow. While typing the code I found that there was a " missing from
> before the & in front of rng1. When I put it in, VB accepted the line
> of code but it still generates the error. What am I missing?
>
> Bill
>

Thanks, Tom.
Basically, I have most of what follows in place. Everything is Dim'd
and Set correctly. I think what's causing the problem is where you are
using "for each cell in Range", I am using "For i=1 to ro" where ro
represents the variable number of products. I needed to do that because
(I forgot to mention) there are subtotal rows in the report worksheet
that I need to skip over. How can I convert cell.address to account for
the i= that I have available. I tried to change it to the Prd and Loc1
(or Loc2) variables and got cnt=Error 2015. On the report worksheet
there are two columns per product, one for each location, so I think I
will need two counts. Hopefully I am not confusing you with my
confusion.

Thanks again,
Bill

Tom Ogilvy wrote:
> Instead of sumif, use sumproduct.
---8<---snip--->8---
>
> In code you would do something like:
>
> Dim rng1 as Range, rng2 as Range, cell as Range
> Dim cnt as Long
> set rng1 = Range("A1:A100")
> set rng2 = Range("B1:B100")
> for each cell in Range("M1:M20")
> cnt = Application.Evaluate("Sumproduct(--( & _
> rng1.Address(0,0,xlA1,true) & "=" & cell.Address & _
> "),--(" & rng2.Address(0,0,xlA1,true) & "=" & cell.offset(0,1).Address
> & _
> ")")
> cell.offset(0,2).Value = cnt
> Next
>
> --
> Regards,
> Tom Ogilvy
>
>

I should also point out that frequently there will be a zero quanitity
in each location. Not sure if that will make a difference but it might.

Okay. So, if I put =SumProduct(etc) into a cell in a blank worksheet
and set the rest of it up as shown it works exactly as expected.
However, on that same worksheet if I put the code into a macro and
execute it I invariably get a "Type Mismatch" error at cnt= . I even
deleted the formula from the cell to make sure it wasn't interfering
somehow. While typing the code I found that there was a " missing from
before the & in front of rng1. When I put it in, VB accepted the line
of code but it still generates the error. What am I missing?

Bill

If you get a type mismatch, the formula is probably returning an error
meaning perhaps it isn't set up properly or you have error values in your
data.

--
Regards,
Tom Ogilvy


"Bill" <[email protected]> wrote in message
news:[email protected]...
> Okay. So, if I put =SumProduct(etc) into a cell in a blank worksheet
> and set the rest of it up as shown it works exactly as expected.
> However, on that same worksheet if I put the code into a macro and
> execute it I invariably get a "Type Mismatch" error at cnt= . I even
> deleted the formula from the cell to make sure it wasn't interfering
> somehow. While typing the code I found that there was a " missing from
> before the & in front of rng1. When I put it in, VB accepted the line
> of code but it still generates the error. What am I missing?
>
> Bill
>

Well, if it helps, this exactly what I have going on:

Data set:
Columns A & B;

Product Inventory Location <=Header
PrdA LocA
PrdA LocA
PrdB LocA
PrdC LocA
PrdB LocA
PrdA LocA
PrdB LocB
PrdB LocB
PrdC LocB
PrdC LocB

Columns E & F;

Prods Locs <=Header
PrdA LocA
PrdB LocA
PrdC LocA
PrdA LocB
PrdB LocB
PrdC LocB


Macro:

Sub sumprod_test()

Dim rng1 As Range, rng2 As Range, cell As Range
Dim cnt As Long
Set rng1 = Range("A2:A12")
Set rng2 = Range("B2:B12")
For Each cell In Range("E2:E7")
cnt = Application.Evaluate("Sumproduct(--(" & _
rng1.Address(0, 0, xlA1, True) & " = """ & cell.Address & "),--(" &
_
rng2.Address(0, 0, xlA1, True) & "=" & cell.Offset(0, 1).Address &
")")

cell.Offset(0, 2).Value = cnt
Next

End Sub

Produces "Type Mismatch" when it gets to cnt=. When I float the mouse
pointer over the .Addresses I get valid ranges and cell locations, but
cnt = 0.

I keep hoping one of these I will understand what I'm seeing and doing.

Bill