How to make a loop run for variable criteria.

[ANDREAAS]

Good Day Boys and Girls

I have the code below that searches for area names in one worksheet and when it finds the name, copies the row to another specified worksheet.

Problem is that there are hundreds of areas to search for, so i need a loop within the loop to search for each of the areas instead of me having to type out an if statement for each of the areas.

Please Login or Register  to view this content.

I have a sheet called "Ref." where all the areas is listed, so instead of repeating this

Please Login or Register  to view this content.

and this....

Please Login or Register  to view this content.

I'd like for the code to run through sheets("Ref.").Range("B9:B1000") and search for each of the areas listed there in the source sheet.


Any help will be greatly appreciated.

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

[ANDREAAS]

Hi Richard

See attacched as requested. i typed in red what the desired result should be. My code above works, only problem is that i currently would have to retype that bottom part over and over for each area.

[Richard Buttrey]

To be perfetly honest I believe you are just storing up problems for yourself.

You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. Yours exhibits all those features.


You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

So before you get too far with this I'd create a single sheet database that contains the following columns
Date
Area *
Office Name *
name for whatever is represented by columns C:K (15-23) - * Values would be one of 15 - 23
Hours
Number of Guards
Grading *
Shift Time


The * values could be Validation drop down cells from which you could pick values. The way I normally arrange these things is to have a single data entry row above the database in which the new values are entered, then a button which runs a macro that adds the new record to the database.

[ANDREAAS]

thank you Richard. much appreciated. Will change the approach as suggested.