VBA issue with Find function

Question

I'm trying to find the row of for a value within a certain range. This range is changing, so it is set up with variables. In the example below the range is set from row 1 to 441 and it finds the value in row 442. How can it find this outside the set range?

Set myDataFile = Workbooks.Open(Filename:=myDataFolder & myFileName)
        If counter + n = 1 Then
            With myDataFile.Sheets(1).Range(Cells(nrlist(n), 1), Cells(nrlist(n + 1) - 1, 1))
            Set loc = Cells.Find(what:=list(j))
            If Not loc Is Nothing Then
                Debug.Print (loc.Row)
                i = loc.Row
                Else
                i = ""
            end if

Answer 1

In VBA, if you're finding a value outside the specified range, it might be because the Cells.Find method is not limited to the With block range you've defined. To ensure it searches only within the specified range, use .Find instead of Cells.Find inside your With block. Here's a compact correction:

Set myDataFile = Workbooks.Open(Filename:=myDataFolder & myFileName)
If counter + n = 1 Then
    With myDataFile.Sheets(1).Range(myDataFile.Sheets(1).Cells(nrlist(n), 1), myDataFile.Sheets(1).Cells(nrlist(n + 1) - 1, 1))
        Set loc = .Find(what:=list(j))
        If Not loc Is Nothing Then
            Debug.Print loc.Row
            i = loc.Row
        Else
            i = ""
        End If
    End With
End If

Ensure .Find is prefixed with a dot to confine the search within the specified range. Also, specify the full path for Cells inside the With block to avoid referencing the wrong sheet.