I have a question about the FIND-VBA code.
How do add I an corresponding value to a new column?

For example next to every NAME the code adds an age looked up in a table.

Thanks Marcus

What can you do to make VBA code run faster? Well, when you run VBA code in Excel, you will notice that the screen flickerseach time an action is performed. Lets take a simple example:

If you run this, each time the cell below is selected, you will see that the screen has updated. This is fine as long as your macro is small, but if it is big, Screen Updating can take a long time, and turning it off can make VBA code run faster. Let’s prove that:

This code for me takes 0.64 seconds to run

Now, lets turn off Screen Updating and see the difference:

0.64 seconds vs 0.078 seconds The faster VBA code runs 8 times the speed of the slow code. If you run the same procedure many times a day, this can add up quickly, and allow you to go home earlier.

NOTE: I turned ScreenUpdating back on before displaying the messagebox... If you don't do that, you get something like this:

... which although it looks whacky is likely to annoy your users.

Download Spreadsheet to see how turning off Screen Updating speeds up VBA code

Training Video on how to make your code run faster by turning off ScreenUpdating:

How do you time how long it takes for VBA code to run ?

Simple..

Step1: At the start of your code, create a variable called "Start" (or whatever)

Set it = Timer (that takes a snapshot of the time)

Step2: At the End of your code, you can use a messagebox to display how long it took.

MsgBox Timer - Start & " Seconds"

Download Spreadsheet to see example of how to Time VBA code

Training Video on how to time your code:

Don't use VLOOKUP, INDEX, or MATCH from VBA when finding things on a worksheet with VBA.

• The problem with these is that when the function doesn't find something, it will return a VBA error which you then have to trap and handle successfully.
• This is a very messy way of coding, and prone to errors.

The best way to look for things on a sheet is to use .Find

Here's an example that finds all whole instances of "Nick" on this sheet:

This code will find all instances of "Nick":

Download workbook with code on how to find all instances of a string on a worksheet

Training Video on how to use FIND in VBA: