Lists, Ifs, Loops and Other Good Stuff

[marhun]

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

 

[squidgeny]

Can you post the code?

 

[marhun]

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