Thread: Limit the search range in FIND() function

Hi,

I am currently using the simple code block below to jump to a cell on another sheet based on a user-inputted value to a cell on the current sheet. Although jump is too strong a word at the moment...walk slowly might be more appropriate.

The problem is that the sheet that contains the target cell hosts a very sizable used range and the FIND function can sometimes take almost a minute before finding and focussing on the required cell.

While I do not believe that a search range below that of sheet level for the FIND() function is possible, the cell that I am looking to jump to resides in a one-column, sorted, dynamic named range (=Bookings!JobID) on the target sheet, so I am sure that it must be possible to find and go the target cell in a much quicker timeframe than that being delivered at the moment. Just can't find something suitable at the moment.

Maybe something using VLOOKUP or GOTO might be possible? Any advice appreciated.

Thanks in advance as ever.
Orson.

Code:

Sub temp()
'
' temp Macro
   Dim searchstring As String
    searchstring = Range("DynamicSummary!$ac$6")
    
    Worksheets("Bookings").Select
'
    Cells.Find(What:=searchstring, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
End Sub

Orson100 try to limit the search area

Code:

Sub temp()
searchstring = Range("DynamicSummary!$ac$6")
Set LookInR = Sheets("Bookings").Range("A1").CurrentRegion
With LookInR
           Set FoundOne = .Find(What:=searchstring, lookat:=xlPart)
        If Not FoundOne Is Nothing Then
            fAddress = FoundOne.Address
            Do
                FoundOne.select
              Loop While FoundOne.Address <> fAddress
        End If
    Next
End With
End Sub

oops,

tested code

Code:

Sub temp()
searchstring = Sheets("Sheet1").Range("A1")
Set LookInR = Sheets("Sheet2").Range("A1").CurrentRegion
With LookInR
           Set FoundOne = .Find(What:=searchstring, lookat:=xlPart)
        If Not FoundOne Is Nothing Then
            fAddress = FoundOne.Address
            Do
                Range(fAddress).Activate
              Loop While FoundOne.Address <> fAddress
        End If
  
End With
End Sub

no problem remember to mark the post solved