VBA Express : Excel - Wildcard search for the wildcard characters * and ?

Excel

Wildcard search for the wildcard characters * and ?

Ease of Use

Easy

Version tested with

2000

Submitted by:

johnske

Description:

All cells and their entire contents with the selected option (* or ?) are listed in a message box.

Discussion:

Coders may want to find cells that contain the wildcard characters * or ? somewhere in the cells text or the cells worksheet formula. (Note that the other {lesser-used} wildcard character # can also be used here in place of ? or *)

Code:

instructions for use

			Option Explicit 
 
 '< Option 1 >
Sub FindCellsWithAsterisks() 
     'find wildcard character * in text
    Dim cell As Range, FirstAddress As String, FoundList As String 
    With ActiveSheet.UsedRange 
         'use tilde to find an *
        Set cell = .Find("~*", LookIn:=xlValues, SearchOrder:=xlByRows, _ 
        LookAt:=xlPart) 
        If Not cell Is Nothing Then 
            FirstAddress = cell.Address '< Bookmark start point
            Do 
                FoundList = FoundList & "Cell " & cell.Address(0, 0) & _ 
                " =" & vbTab & cell & vbNewLine 
                Set cell = .FindNext(cell) 
            Loop Until cell Is Nothing Or cell.Address = FirstAddress 
        End If 
    End With 
     'show search results
    MsgBox FoundList 
    Set cell = Nothing 
End Sub 
 
 
 '< Option 2 >
Sub FindCellsWithQuestionMarks() 
     'find wildcard character ? in text
    Dim cell As Range, FirstAddress As String, FoundList As String 
    With ActiveSheet.UsedRange 
         'use tilde to find a ?
        Set cell = .Find("~?", LookIn:=xlValues, SearchOrder:=xlByRows, _ 
        LookAt:=xlPart) 
        If Not cell Is Nothing Then 
            FirstAddress = cell.Address '< Bookmark start point
            Do 
                FoundList = FoundList & "Cell " & cell.Address(0, 0) & _ 
                " =" & vbTab & cell & vbNewLine 
                Set cell = .FindNext(cell) 
            Loop Until cell Is Nothing Or cell.Address = FirstAddress 
        End If 
    End With 
     'show search results
    MsgBox FoundList 
    Set cell = Nothing 
End Sub 
 
 
 '< Option 3 >
Sub FindFormulasWithAsterisks() 
     'find wildcard character * in cell formulas
    Dim cell As Range, FirstAddress As String, FoundList As String 
    With ActiveSheet.UsedRange 
         'use tilde to find an *
        Set cell = .Find("~*", LookIn:=xlFormulas, SearchOrder:=xlByRows, _ 
        LookAt:=xlPart) 
        If Not cell Is Nothing Then 
            FirstAddress = cell.Address '< Bookmark start point
            Do 
                 'if it's a formula it'll be preceded with an '=' sign
                If cell.Formula Like "=*" Then 
                    FoundList = FoundList & "Cell " & cell.Address(0, 0) & _ 
                    " =" & vbTab & cell.Formula & vbNewLine 
                End If 
                Set cell = .FindNext(cell) 
            Loop Until cell Is Nothing Or cell.Address = FirstAddress 
        End If 
    End With 
     'show search results
    MsgBox FoundList 
    Set cell = Nothing 
End Sub 
 
 
 '< Option 1 alternative >
Sub FindCellsWithAsterisks2() 
     'find wildcard character * in text
    Dim cell As Range, FoundList As String 
    For Each cell In ActiveSheet.UsedRange 
         'search cell for an *
        If InStr(cell, "*") Then 
            FoundList = FoundList & "Cell " & cell.Address(0, 0) & _ 
            " =" & vbTab & cell & vbNewLine 
        End If 
    Next 
     'show search results
    MsgBox FoundList 
End Sub 
 
 
 '< Option 2 alternative >
Sub FindCellsWithQuestionMarks2() 
     'find wildcard character ? in text
    Dim cell As Range, FoundList As String 
    For Each cell In ActiveSheet.UsedRange 
         'search cell for a ?
        If InStr(cell, "?") Then 
            FoundList = FoundList & "Cell " & cell.Address(0, 0) & _ 
            " =" & vbTab & cell & vbNewLine 
        End If 
    Next 
     'show search results
    MsgBox FoundList 
End Sub 
 
 
 '< Option 3 alternative >
Sub FindFormulasWithAsterisks2() 
     'find wildcard character * in cell formulas
    Dim cell As Range, FoundList As String 
    For Each cell In ActiveSheet.UsedRange 
         'search cell for = sign and an *
        If InStr(cell.Formula, "*") And cell.Formula Like "=*" Then 
            FoundList = FoundList & "Cell " & cell.Address(0, 0) & _ 
            " =" & vbTab & cell.Formula & vbNewLine 
        End If 
    Next 
     'show search results
    MsgBox FoundList 
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:

Download the attachment and extract the workbook to view some example uses.

Sample File:

SearchForWildcardChars.zip 10.8KB

Approved by mdmackillop

This entry has been viewed 254 times.