The Excel Find Method is an excellent tool to use when writing Excel VBA macros. Unfortunately most end up using a VBA loop instead of the Find Method. The syntax for the Find Method is as shown below:
expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
Where "expression" is any valid range Object, e.g. Range("A1:A100"), Columns(2) etc. Also, a Range Object is returned whenever we use the Find Method. The Range Object returned will of course be the Range where the value being sought resides.
The single best way to get the code needed for the Find Method is to record a macro using it on any Excel Worksheet. You will end with code like shown below;
Cells.Find(What:="Cat", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate
From here we can modify the code to suit any exact needs. It is very important to understand that the settings LookIn, LookAt and SearchOrder are saved each time the Find Method is used. For this reason one should always specify these settings explicitly each and every time you use the Find Method. If you don't, you run the risk of using the Find Method with settings you were not aware of.
The After setting is also very important. Whichever cell is set here will be the last one searched and not the first as some may expect. For this reason, one should always set this explicitly each and every time you use the Find Method.
Yet another trap can be the incorrect use of the After:= setting. If the Range Object specified is NOT within the range you are using Find on, you will get an error. For example, if you wanted to find a value on another Worksheet (not the Active one), restrict the Find to, say Column A and then select the found cell, you could use;
Sub FindCatOtherSheet() Dim rFound As Range On Error Resume Next With Sheet1 Set rFound = .Columns(1).Find(What:="Cat", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False) On Error GoTo 0 If Not rFound Is Nothing Then Application.Goto rFound, True End With End Sub
Note the use of .Cells(1,1) as the After:= setting. If this was ANY cell NOT within Column A the code would normally bug-out. However, the use of On Error Resume Next prevents this. BUT, despite that you will not be taken to the cell. Sheet1 is the worksheet code name.
The example code below shows how we can use the Find Method on any Excel Worksheet to locate all occurrences of the Word "Cat" add a cell comment to each cell.
Note the use of the COUNTIF Worksheet Function to restrict the Find to the exact number of the times the word "Cat" appears in Column 1. Note also the setting of a Range variable (rFoundCell) to the found cell each time the word "Cat" found. This same variable is then used in the After setting of the Find Method.
Sub Find_Bold_Cat() Dim lCount As Long Dim rFoundCell As Range Set rFoundCell = Range("A1") For lCount = 1 To WorksheetFunction.CountIf(Columns(1), "Cat") Set rFoundCell = Columns(1).Find(What:="Cat", After:=rFoundCell, _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) With rFoundCell .ClearComments .AddComment Text:="Cat lives here" End With Next lCount End Sub
|Excel: Get Data From Closed Excel Workbooks|
|Excel: Get Last Word From Text String|
|Extract Number From Text String|
|Excel VBA: Extract Word From a Text String|
|Excel VBA: Find Number Between 2 Numbers|
|Find Method to Find Dates|
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions. You can order a software analysis of your real estate at a custom software development agency in the UK and the USA.