Creating a custom search function

[doumamar]

Hi,

I have a userform and would like to create a search function on it.
I would like it to check a dynamic range of cells on a single worksheet for an expression the user enters into the search box.
I would then like all rows in which the expression is found to be copied onto a separate sheet.

I have searched for a while for an idea on how to do this, but alas I am new to VBA and have little clue as to how to complete such a task.

Does anyone know how to do this?

Thanks
David

 

[AgentSmith]

Re this "dynamic range", what determines the cells you wish to search? Is it a named range or is something on the form that tells it where to search?

Something like this might do it:

Public Sub SearchAndCopy(SearchFor As String, SearchRange As Range)
    Dim CurrRow As Long     'the row on which the current match is found
    Dim PrevRow As Long     'the row on which the last match was found (<> CurrRow)
    Dim PasteRow As Long    'the row number to paste to
    Dim FoundCell As Range  'result of the Find()
    Dim FirstAddress As String
    
    'Initialise
    PasteRow = 2
    PrevRow = -1
    
    'Search for matches then copy and paste rows to Sheet2
    Application.Goto SearchRange.Cells(1, 1)
    Set FoundCell = SearchRange.Find(SearchFor, , xlFormulas, xlPart, xlNext)
    If Not FoundCell Is Nothing Then
        FirstAddress = FoundCell.Address
        Do
            CurrRow = FoundCell.Row
            If CurrRow <> PrevRow Then
                SearchRange.Rows(CurrRow).EntireRow.Copy 'drop .EntireRow if you don't need it
                Worksheets("Sheet2").Activate
                Cells(PasteRow, 1).Activate
                ActiveSheet.Paste
                PasteRow = PasteRow + 1
                PrevRow = CurrRow
            End If
            Set FoundCell = SearchRange.FindNext(FoundCell)
        Loop Until FoundCell Is Nothing Or FoundCell.Address = FirstAddress
        Application.CutCopyMode = False
    End If
End Sub