Loop through two filters and copy a cell to another sheet

Hi,

I'm trying to create a table that shows a certain value between two states.
I have 50 States, so that's 2500 values I need to input and update frequently.

How do I use the macro to loop through two filters (states 1 and 2), extract the value (cell B1), and paste that into the appropriate table cell in sheet1?

For example, if I were to get a value for MI and OH from sheet2, I'd filter column C to 'MI' and column D to 'OH'. This returns 5 results and the resulting B1 value is 2.20. I need to copy and paste this value into sheet1's cell AI22.

I hope this works out.
Thank you.

Hi kvnpark

Welcome to the Forum!

I don't particularly like this approach. You need to click on Sheet 2 Cell B1 to fire the macro.

Place this code in Sheet 2 module

Please Login or Register  to view this content.

Hi kvnpark

Right click on the Sheet 2 tab...select "View Code". This opens VBA. Paste the code in the right hand window.

To fire the code place your cursor anywhere EXCEPT on Cell B2, do your filtering...then select Cell B2. The code will fire. You won't "see" anything but the proper Sheet 1 cell will be populated.

There is an alternative to this...a command button could be placed on Sheet 2 and a procedure run from the button. If you're interested in this approach, let me know.

Let me know of issues.

Hi kvnpark

I don't really know what you're asking here.

can we put this inside a loop so that the whole table gets generated with a single run

Please explain further.

Hi kvnpark

Wow, yes that clarifies IT. The IT is a totally different animal with a totally different approach. I'd assume it can be done...need to think about how...bit more complex.

Get back to you tomorrow I'd assume.

Hi kvnpark

Your profile indicates Excel 2010. Does this need to run on pre Excel 2007?

Hi kvnpark

Just opened the fie...get back to you.

Hi kvnpark

Try the code in the attached. Click the button on Sheet2. IF it does as you require, it's been tested on both Excel 2000 and Excel 2007. Let me know of issues.

Hi John,

I copied and pasted more data into sheet2, and tried it. It seems to work for the most part.
But for some reason, the row 21 of sheet1, which is MD, is blank even though there are two entries with MD as state1. Both also have corresponding state2 of 'ON'. Thus cell AL21 should have a value of 2.07.

When I first tried with new data, the loop would stop and sheet2 would show me some filtered result. But I figured out that it was due to spaces in front of some State abbreviations (ex. " IL" instead of "IL"). I fixed all that and some other entry errors.

So, the only major issue at the moment is the data for State1 of MD (AL21 to be specific). I can't see anything wrong with the entry. It just won't enter the value into the table.

Hi John,

I figured out when the cell AL21 is not entered. It's when I sort the data by State1 (A to Z).
Conversely, when I sort the data by State1 "Z to A", instead of AL21, cell AL20 is blank. The cell value should be 1.24.

When I sort the list by date (Oldest to newest), both AL21 and AL20 are filled correctly; however, now Z44 is missing.
When I sort the list by date (Newest to oldest), both AL21 and AL20 are again filled in correctly; however, Z35 is missing.

Basically, depending on how I sort the data in sheet2, some values don't get filled in to sheet1.

I'm not sure how this is possible. I hope this works out.
Thanks.

Hi Kevin

I'm on it. May have just discovered a clue...I'll let you know.

Hi Kevin
Try the code in the attached. I believe the issue is resolved. You tell me.

PS: I meant to delete Sheet3...it's a backup copy of Sheet2.

Hi Kevin

Glad it work's for you.

If I don't get back to you on this by Monday

Could you explain a little bit about the role of the Constants tab and the two new columns

please remind me. I've house guests this weekend , so, will not be paying much attention to the Forum.

PS: When you say

two new columns

to what are you referring?

Hi Kevin

Ah...I understand. Hopefully I can explain so you understand. There IS a reason for it. Get back to you. Again, if I don't get back to you by Monday...REMIND ME.

Hi Kevin

The first procedure "Sub CreateUniqueStates()" creates a list of all states from Sheet2 column C. It also creates a named range (State1) for this unique list (duplicates removed). That's Column A of Sheet Constants. This named range is the VBA equivalent of what you'd see doing a manual filter on Column C. This named range is used in "Sub FilterStates()".

The second procedure, "Sub FilterStates()", cycles each item in "State1" and filters Column C for the value of the item. That filter on Column C then creates a Column D filtered list This column D filtered list may contain 1 item or 100+ items...who knows how many. That's Column D of Sheet Constants.

The second procedure then takes Column D of Sheet Constants and creates a UNIQUE list of Sheet Constants Column D and places it in Column G of Sheets Constants. It names the contents of Column G "State2". The second procedure cycles each item in "State2" and filters Column D for the value of the item. This is the equivalent of you manually filtering Column D.

Hope that explains it...if not, let me know.