1

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...

braX
  • 11,506
  • 5
  • 20
  • 33
Toni92
  • 27
  • 7
  • make sure you use the `.Cells` reference correct. `"I"` corrosponds to a text – Wesley Dec 19 '21 at 19:40
  • 2
    `lookat` accepts `xlWhole` or `xlPart`. `xlValues` goes into `LookIn`. I assume you have `on error resume next` before this code, you should really remove that. There is no reason to execute the search twice though. Do `Set releaseFound = myrange.Find(currentRelease, LookIn:=xlValues)` and then see if `releaseFound` is `Nothing`. – GSerg Dec 19 '21 at 19:46
  • @GSerg, thanks very much. I had mistaken LookIn and LookAt. With LookIn:=xlValues it is working properly :) – Toni92 Dec 20 '21 at 06:59

2 Answers2

1

A Find Method Example

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
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
0

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
Toni92
  • 27
  • 7