Find with VBA - How to Guide

Using FIND with Excel VBA can save a great deal of time and negates the use of inefficient looping constructs in many cases. A commonly seen example is to see someone asking for help to find a value in a range by looping through each cell in that range and testing for a particular condition. Find on the other hand will produce a result in very quick time when compared to looping through a range.


How is the Find Function Used in VBA?


The Find function should be more heavily used in Excel VBA. It can easily replace a looping construct where a word or phrase is found and that location trapped with a variable. The most important things to know about Find are as follows:

1. Find is used in VBA in the same way that we use the find dialog in Excel.
2. In essence we are finding a range or cells location to use as a starting point.
3. The find function can search all the cells or a group of cells you choose.
4. A variable can be used to trap the location of the Find result.

Excel's find method is handy for finding strings and cell locations especially when the cells location is moving and you want to trap that location. This is an example of Moving Data Columns with the help of the find method. The syntax for the Find method from the vba help section of Excel is as follows

Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)


If you are only looking for a string I like to simplify the above with the following VBA code.


Sub FindStr1() 'Excel VBA for a find.
Dim i As Integer

i = [a1:a10].Find("car").Row 'The row to find.
MsgBox "Val in Row " & i
End Sub
Find VBA
 

If the list we are looking in looks like the above and the cell we are looking to find is in row 4 then when we run the VBA procedure the find will produce the following result.

Find VBA Excel
 

The message box produces the correct result when using the find method.

So upfront you are saying find the string Car in range A1:A10. Simple as that! I find that it works wonderfully well in a range of situations, where finding the location and working from that location is key.

Trapping a Range with Find


The following is an Excel VBA example of trapping a range with the Find method.


Sub FindStr2() 'Excel VBA to find a Range of cells.
Dim fnd As Range
Dim rng As Range

Set fnd=Range("A1:G20").Find("Start")
Set rng=Range(Cells(fnd.Row, fnd.Column + 1), Cells(fnd.Row, fnd.Column + 2))
End Sub


The above will trap a range from the word 'start', it will move one column to the right and trap the range covering 2 columns in total.

Find with VBA
 

Based on the above technique the code would find the cells highlighted. The VBA finds the cell which says start goes across a column and traps the range two columns wide.

Using Find with Resize


Another way to write the above coding would be to incorporate a resize with the originally found cell. This would increase the size of the range that has been found. The VBA would be as follows.

Sub FindStr2() 'Excel VBA to find a Range of cells.
Dim fnd As Range
Dim rng As Range

Set fnd = Range("A1:G20").Find("Start")
Set rng = Cells(fnd.Row, fnd.Column + 1).Resize(, 2))
End Sub

The above VBA is a little more economical and works just as well to find and trap a range which will help if the range is dynamic. In truth all code should be written so it is dynamic which means the find methodology should be used a lot more.

Consolidating the Find Method

If your sheet has Start in A1 to G20 the coding will identify the cell and make a range which is 3 columns wide.  So if Start was in B2 the range (rng) would be Set from B2:D2.  This range could then be used for a whole host of activities which is open to your Excel imagination. 

Taking this concept one step further if you needed to find an item in a list then add a value from another cell to that list then it can be simply done with the following Excel VBA find coding.

Sub FndRng()
Range("G" & [C1:C500].Find([j6]).Row)=[J8]
End Sub

The above VBA coding will search column C for the contents of J6 and place the value of J8 in the found row of G.  So if the value to find was 100 [j6] and this was in C40 and if J8 contained the word "Found" then G40 would say Found.

Try it out.  Find works like a charm in VBA.  Of course a Vlookup would do a similar task without the use of VBA at all, however for on the fly trapping of a succinct location - the find method in VBA is gold.