Lists, Ifs, Loops and Other Good Stuff

marhun

New Member
Joined
Sep 2, 2009
Messages
10
Hi there

I am having a bit of trouble with simplifying a macro I did a while back. Simply I have a report on one worksheet which depending on the selections made from a drop down list on another worksheet filters the data so only the relevant stuff if shown in the report.

The bit I am having trouble with is that I previously had written a macro which within it said what each of the drop down values should be and then repeated a set process. This has become a bit unweildy.

What I would like to do is to get a bit of script which will say that the drop down selections should be from a list maintained in another worksheet. For Example

Selection 1 in the Selector Worksheet should equal the active cell in the List Worksheet. Then it should do my repetitive macro.

I then want it to change Selection 1 to the next value in the list and so on until it comes to a blank, at which point it stop.

Any suggestions? I've tried IF's and LOOP's but must be doing something wrong.

Thanks
Mark
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
This is just a piece of test code to see if I can get the macro to end when the result of the HLOOKUP which is returned in a cell Named "Region" is null.

Once I get that bit working I can then go from there. Right now the loop is not ending and the bit that copies the cell named Current to the cell named Previous which drives the row returned in the HLOOKUP just continues to do so even after the first blank return has been reached.

Let me know if you need the spreadsheet to make more sense of it.

Thanks very much




Do

Region = "=if(iserror(HLOOKUP(RegionKey,ReportRange,current,FALSE)),"""",HLOOKUP(RegionKey,ReportRange,current,FALSE))"

Market = "=if(iserror(HLOOKUP(MarketKey,ReportRange,current,FALSE)),"""",HLOOKUP(MarketKey,ReportRange,current,FALSE))"

Range("A15") = "=Region&Market"
Range("Current").Select
Selection.Copy
Range("Previous").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

If Region = "" Then
Exit Do
End If
Loop
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top