<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

Excel VBA Find Method

| | Information Helpful? Why Not Donate.

Excel Find Method in Excel VBA. How to Use Find in Excel VBA Macros

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 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 WithEnd 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 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 LongDim 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 lCountEnd Sub

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. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical 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