Find

[nianchi111]

Hi,

I want to find a word in excel and I have used this code.

Sheets("Sheet1").Select
ActiveSheet.Columns("A:A").Select
Selection.Find(What:="Owner:", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

The Challenge is some times it is "Owner" or "Last known".

So first it has look for Owner if this is not found then find Last Known.

Please help me.

 

[Trebor76]

Hi nianchi111,

Try this which sets the 'rFoundCell' variable only if there's an exact match, so if 'Owner' is in column A of 'Sheet1' having the code look for 'Owner:' (as you've done) will not return a match:

Sub Macro1()
    
    Dim lLastRow As Long
    Dim rFoundCell As Range, _
        rSearchRange As Range
    Dim vArrayItem As Variant
        
    lLastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
       
    Set rSearchRange = Sheets("Sheet1").Range("A1:A" & lLastRow)
    
    For Each vArrayItem In Array("Owner", "Last Known")
    
        Set rFoundCell = rSearchRange.Find(What:=vArrayItem, _
                                           LookIn:=xlValues, _
                                           LookAt:=xlWhole, _
                                           SearchOrder:=xlByRows, _
                                           SearchDirection:=xlNext, _
                                           MatchCase:=True, _
                                           SearchFormat:=False)
        
        'If the 'rFoundCell' variable has been set, then...
        If rFoundCell Is Nothing = False Then
            '...quit the loop.
            Exit For
        End If
            
    Next vArrayItem
    
    If rFoundCell Is Nothing = True Then
        MsgBox "No matches were found!!", vbInformation, "My Search Editor"
    End If

End Sub

HTH

Robert

 

[lenze]

Give this a try

Sub FindMe()
Dim C As Range
Dim rng As Range
Dim LR As Long
LR = Cells(Rows, Count, "A").End(xlUp).Row
Set rng = Range("A2:A" & LR)
Set C = rng.Find("Owner:", LookIn:=xlValues)
If Not C Is Nothing Then
     C.Select
Else
    Set C = rng.Find("Last Known:", LookIn:=xlValues)
    If Not C Is Nothing Then
        C.Select
    Else: MsgBox "Item not found"
    End If
End If
End Sub

lenze

 

[nianchi111]

Thank you very much