Excel Macro Example With A Loop

Excel macros

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials
***************

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


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

3 Feedbacks on "Excel Macro Example With A Loop"

HK

Hi thanks for the informative post.

Just curious why you wouldn’t just use a filter? I pressed Autosort and then entered the required citeria and done!

With the macro you have to think long and hard to type the macro, and also you might type the wrong information into the first sheet (human error). What are the benefits of the macro?



Leslie

@HK – Was just trying to give a simple example of how to build a loop macro. It can be much more flexible than a simple filter so in the end will often be much more useful, but I agree that for a simple case, it is not necessary:)



airline stewardess jobs

airline stewardess jobs…

Experiments in Finance…



Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.