using Cells.Find to search for multiple values

[keith05281967]

Hello,

I'm trying to use the below to find a number of different spellings that might be used to represent the same column header. The vbe doesn't like this. Is there another way? Is it a syntax thing?

Cells.Find(What:="Tom" Or "Tommy" Or "Thomas", after:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)

thanks,
Keith
excel 07

 

[mirabeau]

Depending on the amount of possible variation in the possible names ("Zommy", or "yummy", or?) you mght have to use several different lines, or more compactly to loop through some possibilities listed in an array.

Otherwise try the wildcard approach
Find("*om*, ... ) or similar.

 

[Scott Huish]

Or doesn't work like that, it is a Boolean function that returns a TRUE/FALSE result. To do that search you have to do multiple Finds:

Sub test()
Dim n, x As Long, c As Range
n = Array("Tom", "Tommy", "Thomas")
For x = LBound(n) To UBound(n)
    Set c = Cells.Find(n(x), after:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False)
    If Not c Is Nothing Then
        c.Select
        Exit Sub
    End If
Next
MsgBox "Not Found"
End Sub

 

[keith05281967]

thanks HOTPEPPER. I think i'm still doing something wrong because it is only considering the 1st value I give it.

 

[Scott Huish]

Can you post the code exactly as you have it?

 

[Trebor76]

Hi Keith,

Not sure how efficient this will be on large datasets (always a worry with Excel 2007 and on), but nonetheless try this:

Sub Macro1()

    Dim varMyArray As Variant
    Dim lngArrayItem As Long
    Dim lngSearchLoop As Long
    Dim rngFoundCell As Range, _
        rngFoundRange As Range
    
    varMyArray = Array("Tom", "Tommy", "Thomas")
    
    Application.ScreenUpdating = False
    
    For lngArrayItem = LBound(varMyArray) To UBound(varMyArray)
        
        'If there is at one match for array item, then...
        If WorksheetFunction.CountIf(ActiveSheet.UsedRange, varMyArray(lngArrayItem)) > 0 Then
            '...begin a loop to return the cell location for array item.
            For lngSearchLoop = 1 To WorksheetFunction.CountIf(ActiveSheet.UsedRange, varMyArray(lngArrayItem))
                'If the 'rngFoundRange' hasn't been used (set yet) then...
                If rngFoundRange Is Nothing Then
                    '...set it to the first cell found from the active cell.
                    Set rngFoundCell = ActiveSheet.UsedRange.Find(varMyArray(lngArrayItem), _
                                            After:=ActiveCell, _
                                            LookIn:=xlFormulas, _
                                            LookAt:=xlWhole, _
                                            SearchOrder:=xlByRows, _
                                            SearchDirection:=xlNext, _
                                            MatchCase:=False, _
                                            SearchFormat:=False)
                    Set rngFoundRange = rngFoundCell
                'Else...
                Else
                    '...add to it the next cell found after the 'rngFoundCell' cell.
                    Set rngFoundCell = ActiveSheet.UsedRange.Find(varMyArray(lngArrayItem), _
                                            After:=rngFoundCell, _
                                            LookIn:=xlFormulas, _
                                            LookAt:=xlWhole, _
                                            SearchOrder:=xlByRows, _
                                            SearchDirection:=xlNext, _
                                            MatchCase:=False, _
                                            SearchFormat:=False)
                    Set rngFoundRange = Union(rngFoundRange, rngFoundCell)
                End If
            Next lngSearchLoop
        End If
        
    Next lngArrayItem
    
    Application.ScreenUpdating = True
    
    If rngFoundRange Is Nothing Then
        MsgBox "None of the array items were found.", vbInformation, "My Search Editor"
    Else
        rngFoundRange.Select
    End If
    
End Sub

HTH

Robert

 

[keith05281967]

All,

Thanks for the replies. Trebor - I will look at your code. The below code actually does what I was trying to accomplish. Feel free to use:

n = Array("Ted", "Frank", "Harry", "Albert", "Ron", "Steve")
nCntr = LBound
myfindloop:
Set c = Cells.Find(What:=n(nCntr), after:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)

If c Is Nothing And nCntr > UBound Then
MsgBox "loan number field not found", vbExclamation
ElseIf c Is Nothing And nCntr < UBound Then
nCntr = nCntr + 1
GoTo myfindloop
Else
c.Select

thanks,
Keith

 

[Trebor76]

Hi Keith,

Thanks for the feedback. A couple of points with your solution:

• It's missing an End If,
• Will error out if no items in the array are found, and
• Will only select the first match found which based on your earlier posts is not sufficient.

Regards,

Robert