Found object returns Nothing despite being set within an "If not ... is nothing" check-CodePudding

I am trying to find a string in a row and then select the found cell. For this I have the following part of code:

currentRelease = Worksheets("Summary").Cells(5, "I").Value
Set myrange = Worksheets("Summary").Rows(15)

If Not myrange.Find(currentRelease, lookat:=xlValues) Is Nothing Then
    Set releaseFound = myrange.Find(currentRelease, lookat:=xlValues) 'this row gets executed
    releaseFound.Select
End If

I am not getting any error, but when debugging no cell gets selected and I see that the releaseFound is returning Nothing although the Set releaseFound... row of this code gets executed, so the If check has been actually positive? What could be the reason for this?
I tried to use the Find feature on the sheet, outside of VBA, and the value is really found where I am searching for it...

CodePudding user response：

A Find Method Example

Range.Find method

Option Explicit

Sub FindMethodTest()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("Summary")
    
    ' Try to restrict this declaration to a string or a numeric value.
    Dim SearchValue As Variant: SearchValue = ws.Cells(5, "I").Value
    
    Dim SearchRange As Range: Set SearchRange = ws.Rows(15)
    
    ' Use a variable to reference the found cell.
    Dim FirstFoundCell As Range
    ' To start the search with the first cell 'A15', you need to set
    ' the 'After' parameter to the last cell in row '15'.
    Set FirstFoundCell = SearchRange.Find( _
        What:=SearchValue, After:=SearchRange.Cells(SearchRange.Cells.Count), _
        LookIn:=xlValues, LookAt:=xlWhole)
        
    ' Now test the variable.
    If Not FirstFoundCell Is Nothing Then ' found
        FirstFoundCell.Select
        Debug.Print "The first found value is in cell '" _
            & FirstFoundCell.Address(0, 0) & "'."
    Else ' not found
        Debug.Print "Value not found."
    End If

End Sub

CodePudding user response：

The suggestion by @GSerg in the comments above solved the issue: lookat accepts xlWhole or xlPart. xlValues goes into LookIn. The code now works for me:

currentRelease = Worksheets("Summary").Cells(5, "I").Value
Set myrange = Worksheets("Summary").Rows(15)

If Not myrange.Find(currentRelease, lookin:=xlValues) Is Nothing Then
    Set releaseFound = myrange.Find(currentRelease, lookin:=xlValues) 'this row gets executed
    releaseFound.Select
End If