Excel Macro Example With A Loop

Hi everyone, today I’m thrilled to be able to publish a new example of how powerful Excel macros can be. This is an email that I received from a reader (would love to get yours as well, simply contact us) and I decided to take up the challenge. First, here is the email:

“Hello.. Random e-mail. I stumbled onto your blog and found the tutorials suprisingly easy to read and understand! which isnt normal for me! I was wondering if you could just show me how to work this spreadsheet..
I work for a chauffeur company and i’ve recently made a spread sheet with the customers name, date of travel, place they travelled, e-mail address and whether the travel was for business or leisure.
What I need to do is to be able to filter the customers.. Say if they travelled to Heathrow in march 2011. And e-mail all of those customers. Do you see what I mean? It would be great if you could e-mail back with some help!

Laura”

So basically, this can be done in a number of different ways. I did think about using an advanced filter but in the end I decided to simply take a quick macro. First, you can take a look at what the database looked like (don’t worry, you can download the spreadsheet later in this post):

Basically, I wanted to give the option and finally decided on doing a simple loop. Sure, if the database had a few tens of thousands of lines, this could slow it up by a few seconds for this specific task, it looked like a perfect solution. If you would like a quick introduction on building an excel macro, I recommend that you go here!

So basically the steps in my opinion are as follow

1-determine the criteria
2-go through each line of the database to determine if it meets all 6 criterias
3-if it does, simply copy the line

Here is the code that I wrote:

As you can see, it is very simple, the only line that is more complicated is the verification of each line. Basically, I make sure that either the result in that cell meets the criteria or that the criteria is empty.

Here is the result as you can see it:

You can also download the spreadsheet here.

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss

***************************************************