Need a macro to loop through a drop down box

**WC_500** · 07-12-2011, 06:46 PM

Hello, I'm new to progamming as you'll probably be able to notice with my code so please bear with me.

I am trying to use a macro to run a pricing comparison and to copy each price over to another worksheet so I can view all of them in one spot.

There are 2 companies, A and P, where company P has 14 possible rates depending on what selection is made in cell F23 on the 'Quote' Tab. I set up a macro to move the chosen coverages along with company A premium to the 'Comparison' Tab but am having problems when it comes to quoting and recording all of company P's premiums.

I set up a macro to loop, which it seems to do, but what I want the macro to do is to start with the first item in the drop down box, A1, (which as an FYI this cell uses the list from AD7:AD20), move company P's rates over to the 'Comparison' Tab and drop them into the corresponding cells in column E. Then go back and change the drop down box to B1, move those rates to column F in the 'Comparison' Tab and so on - all the way to the final selection of Y1 in column R.

Here is the macro and part of my copy/paste code (which is probably the long way of doing it) that I set up to move company P's premiums over. Does anyone have any insight as to how I can add code to loop through the drop down box and record each of the specific premiums at the same time?

Please Login or Register  to view this content.

(I attached a spreadsheet with all the code if it helps. The loop code is under Module 2, named Tier A1 macro)

**JBeaucaire** · 07-12-2011, 09:51 PM

I would update your macros like so:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**WC_500** · 07-13-2011, 10:08 AM

Wow, that is great. You just saved me a bunch of time

I left the code I already had in place for the Quote_All_Tiers macro because I noticed if I put in the code that you suggested in, it doesn't move the selections from the Quote Tab that should go to the Comparison Tab to columns C and D. It just overlays them onto the Quote screen. Maybe something is reversed in the code or a reference is missing?

**JBeaucaire** · 07-13-2011, 10:33 AM

There's no need to guess, just look at the code... it absolutely puts the values from various cells in the Quoter page into the cells in columns C:D of the Comparisons page. And it does it without all the inefficient selecting and switching and copy/pasting.

Please Login or Register  to view this content.

This line of code says "make the value of C17:C18 on the Comparisons page the same values as L7:L8 from the Quoter page."

When I run that line of code I see the value transfer in, you don't?

Clear the cells on the Quote page, then use the F8 key in the VBeditor to run that macro one line at time, check the Comparisons sheet after each command, you'll see the values coming in.

**WC_500** · 07-13-2011, 11:04 AM

Oh, you're right sorry about that. Thanks for the tip on using F8.

After running through it I saw that the problem was the Formatting Macro. Turns out it formatted the Quote Tab rather than the Comparisons Tab. So I made sure to reference the Compariston tab and everything works just fine now.

Thanks so much for the help!