Find with VBA

Using the FIND method 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.

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.

Range.FIND("String to Find")

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

MsgBox "Val in Row " & i
End Sub

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. The following is a 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

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.  Works like a charm.  Of course a Vlookup would do a similar task without the use of VBA at all.