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" wrote in message
ups.com...
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" wrote in message
oups.com...
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