Excel Find Method in Excel VBA <See Also Excel VBA Find Dates | Advanced Excel Find | Excel Find & Replace>
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
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 Worksheets CodeName
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
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
<See Also Excel VBA Find Dates | Advanced Excel Find | Excel Find & Replace>
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to firstname.lastname@example.org 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates