VBA Express : Excel - The Find Function Simplified + An Alternative Find

Excel

The Find Function Simplified + An Alternative Find

Ease of Use

Intermediate

Version tested with

2000

Submitted by:

johnske

Description:

Still trying to become fluent with the logic and the expressions such as "If Not c Is Nothing Then..." used in the Help example? Here's a variation you may find easier to understand...

Discussion:

Among other things, Find can be used to find either "whole" or "part" (i.e. wildcard) values in cells. The inbuilt function is very fast, but unfortunately, the version in the Help file is not very easy to understand and thus is often not easy to modify to suit your own needs. In addition to this, when the search area is small, or, message and/or input boxes are being used (in particular), speed is not always a prime concern. This is where the shorter alternative version given here can come into its own.

Code:

instructions for use

			Option Explicit 
 
 '<< A SIMPLIFICATION OF THE MS "FIND" FUNCTION >>
 
Sub MSFindIt() 
    Dim Cell As Range, FirstAddress As String 
    With Range("A1:D500") 
        Set Cell = .Find("it", LookIn:=xlValues, searchorder:=xlByRows, _ 
        LookAt:=xlPart, MatchCase:=True) 
        On Error GoTo Finish '<< the "error" being that there are no "it"s
        FirstAddress = Cell.Address '<< bookmark the start-point of the search
        Do 
             'Do whatever you want below, the messagebox is just an example...
            MsgBox "An ''it'' was found at " & Cell.Address & " (" & Cell.Value & ")" 
            Set Cell = .FindNext(Cell) 
        Loop Until Cell Is Nothing Or Cell.Address = FirstAddress 
    End With 
Finish: 
End Sub 
 
 
 '<< AN ALTERNATIVE TO "FIND" >>
 
 '//While not as fast as the inbuilt "find" function, this alternative
 '//is still very fast for most purposes - it does exactly the same
 '//thing as the MS example above, but is much shorter and simpler...
 
Sub FindItWild() 
    Dim Cell As Range 
    For Each Cell In Range("A1:D500") 
        If Cell Like "*" & "it" & "*" Then '<< use "Like" for wildcards in If-Then statements
            MsgBox "An ''it'' was found at " & Cell.Address & " (" & Cell & ")" 
        End If 
    Next Cell 
End Sub

How to use:

Open an Excel workbook
Select Tools/Macro/Visual Basic Editor
In the VBE window, select Insert/Module
Copy and paste the code into the Module
Now select File/Close and Return To Microsoft Excel
Dont forget to save your changes...

Test the code:

In the Excel main window, select Tools/Macro/Macros.../MSFindIt/Run
Now do the same with FindItWild...

Sample File:

Find_It.zip 13.1KB

Approved by mdmackillop

This entry has been viewed 250 times.