Me, programs, examinations and coursework.

Did you FIND() it yet?

How to FIND() something in Excel with VBA? That was a question that had annoyed me for some time; it was also a problem that prevented me from fulfilling one specification:

The program must be able to allow the user to easily search for someone who has booked before.

While there were many ways to solve it, the easiest was to copy what Pizza Hut Hong Kong does and save it by a phone number.

Moped used for pizza delivery in Hong Kong

These people, besides having great pizza, also have a wonderful booking management system. I think. Image via Wikipedia.

It was easy to dream about. And collecting the phone number would be easy; all I had to do was insert an extra textbox into my UserForm and tweak the macro. Like pie.

The problem was searching for that particular number.

Before, I had come across some tutorial that suggested VLOOKUP(). It was insanely complicated, used a bunch of things I didn’t understand, and then, when I copied it, letter-for-letter; it didn’t work.

So I was put off searching by phone number and decided to dedicate my time to finishing it all off instead.

But when I was searching through VBA code tutorials on Google, I found a little help guide that turned out to be amazing.

This thing taught that there was a function in VBA called “Find”, and it would simply call on the Excel’s wonderful Find dialog and then find it like that.

Surprisingly, it's possible to simulate this with VBA. Just without the user interface.

And so, because there was no harm in doing so, I tried it out. And tried again when I realised I was working on the wrong workbook. The result was a working search system that really allowed me to search by phone number.

It's time to... Search By Phone Number.

It actually works. I can simply enter a phone number (or any string, in fact) and search for it by pressing the button. Everything — the names, the birthdate, the email address — all updates automatically. And with a bit of extra programming, I managed to add in extra buttons that took the user to the other UserForms I had created before, to allow them to change the booking details or personal particulars.

This was a breakthrough change. Now, I’m almost complete with the entire system. Only a couple more tweaks left.

Good luck to me!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s