Looping through and array with three possible decisions to make
 

I am probably making this more complex than I need to, so what is the
simplest way to loop through an array, looking for possibly three common bits
of data and adding them together. Using a wine example... I am searching a
series of 800+ records for:

Wine Vendor
Wine Origin
Wine Category

The user would choose from a drop down, either vendor, origin or category,
and the macro calcs sales and costs based on the selections. For any of the
three, the user can choose "ALL" or a specific value in each group.

I started writing sub routines to take into account each possible option,
but hoped there might be an easier way... can anyone help with a shorter
solution?

Thanks!

Looping through and array with three possible decisions to make
 

You can do this completely without programming. Say you have four columns:
Col A - Vendor
Col B - Origin
Col C - Catagory
Col D - Sales

Select a column header and:

Data Filter AutoFilter

You can then select combinations of options. Use the =SUBTOTAL() function
to add up sales because SUBTOTAL() only sums the visible part of filtered
data.
--
Gary''s Student - gsnu200761


"jayklmno" wrote:

I am probably making this more complex than I need to, so what is the
simplest way to loop through an array, looking for possibly three common bits
of data and adding them together. Using a wine example... I am searching a
series of 800+ records for:

Wine Vendor
Wine Origin
Wine Category

The user would choose from a drop down, either vendor, origin or category,
and the macro calcs sales and costs based on the selections. For any of the
three, the user can choose "ALL" or a specific value in each group.

I started writing sub routines to take into account each possible option,
but hoped there might be an easier way... can anyone help with a shorter
solution?

Thanks!

Looping through and array with three possible decisions to mak
 

Unfortunately, I have been asked not to go that route for fear of confusing
individuals with too much information. I need to perform work on the data and
just present the answer. I would have had it done with autofilters...

"Gary''s Student" wrote:

You can do this completely without programming. Say you have four columns:
Col A - Vendor
Col B - Origin
Col C - Catagory
Col D - Sales

Select a column header and:

Data Filter AutoFilter

You can then select combinations of options. Use the =SUBTOTAL() function
to add up sales because SUBTOTAL() only sums the visible part of filtered
data.
--
Gary''s Student - gsnu200761


"jayklmno" wrote:

I am probably making this more complex than I need to, so what is the
simplest way to loop through an array, looking for possibly three common bits
of data and adding them together. Using a wine example... I am searching a
series of 800+ records for:

Wine Vendor
Wine Origin
Wine Category

The user would choose from a drop down, either vendor, origin or category,
and the macro calcs sales and costs based on the selections. For any of the
three, the user can choose "ALL" or a specific value in each group.

I started writing sub routines to take into account each possible option,
but hoped there might be an easier way... can anyone help with a shorter
solution?

Thanks!