Other option for 'ActiveCell'-CodePudding

I would like to ask if the below VB can be done without going to sheet1. Planning to use a form to search multiple words within the range but not activating the list and staying to working sheet.

Here's the code:

Sheet1.Activate
'search for searchText and populate list box with all data found
Dim searchText As String, FirstAddr As String
Dim FoundCell As Range, LastCell As Range, searchRange As Range
Dim i As Integer, endRow As Long
Dim foundTarget As Boolean

    searchText = Me.txtTarget.Text

    If Len(searchText) = 0 Then Exit Sub
   
    Application.ScreenUpdating = False
    Range("A1").End(xlDown).Select
    endRow = ActiveCell.Row
    Range("A1").Select
    Application.ScreenUpdating = True
    
    Set searchRange = Range("A2:D" & endRow)
    
    Me.lstCustSearch.Clear
    foundTarget = True
    
    With searchRange
        Set LastCell = .Cells(.Cells.Count)
    End With

    Set FoundCell = searchRange.Find(what:=searchText, after:=LastCell)

    If Not FoundCell Is Nothing Then
        FirstAddr = FoundCell.Address
    Else
        foundTarget = False
    End If

    i = 0
    Do Until FoundCell Is Nothing

        Me.lstCustSearch.AddItem Cells(FoundCell.Row, 1).Value
        Me.lstCustSearch.List(i, 1) = Cells(FoundCell.Row, 2).Value
        Me.lstCustSearch.List(i, 2) = Cells(FoundCell.Row, 3).Value
        Me.lstCustSearch.List(i, 3) = _
            Format(Cells(FoundCell.Row, 4).Value, "$#,##0.00")
        
        Set FoundCell = searchRange.FindNext(after:=FoundCell)
        If FoundCell.Address = FirstAddr Then
            Exit Do
        End If
        i = i   1
        
    Loop
    
    If Not foundTarget Then
        MsgBox "No data found for " & searchText
    Else
        Me.txtTarget.Text = ""
    End If
    
    Me.txtTarget.SetFocus
    
End Sub

also, if possible to create a command button where I can populate the selected data to the working sheet once closing the form.

Thank you!

Expectation is to use a form without activating the list.

CodePudding user response：

Instead of:

Sheet1.Activate
...
Range("A1").End(xlDown).Select
endRow = ActiveCell.Row

... you can just:

endrow = Sheet1.Range("A1").End(xlDown).Row