Search Range, List Results in MultiColumn ListBox

[LiamPotter]

Hi there,

I've got a severe problem trying to get this working. I've tried all sorts of coding and ways to get around it but none work. Would anyone be able to help? I'll explain the best way I can:

My logical solution turns out as follows... User enters search text > look through range to find all cells containing search text > copy entire row containing search text to another sheet > repeat until all results are listed on other sheet > load userform & listbox and use search results on new listbox.

One of the UserForm's within my program is just a listbox which contains data from a dynamic named range on sheet 3 (columns A to F). The listbox shows all the data within that range in the 6 columns on the listbox. That much I can do no problem.

The problem I'm encountering is trying to make a small search function which allows my users to search through the above range in any of the cells in that range, and list those results on another UserForm in another listbox. At the moment I can't even get a code working to copy the information in my search range to another sheet ready to be used on the new listbox...

I can code up the listbox to contain any range I want but for the life of me I can't make a code which will copy all results found in the above range onto another sheet (the results don't need to be an exact match either, even if part of a cell contains that text I want it listed).

Can anyone help me with a code which will search through the range A300:F300 on Sheet3, and for all results found to copy the entire row onto Sheet6 range A*:F*? Once I've conquered that I'll be able to do the rest but I'm just dumbfounded on how to do this part.

Many thanks in advance for any help.

~Liam

[royUK]

The database form example does this, get it here

http://www.excel-it.com/vba_examples.htm

[mikerickson]

This uses AdvancedFilter to copy the records matching the search term to another sheet. It uses an InputBox to get the search term from the user.

Sub AdvancedFilterMove()
    Dim dataRange As Range
    Dim keyCol As Range, keyValue As Variant
    Dim destinationSheet As Worksheet
    Dim destinationRange As Range
    Dim critRange As Range
    
    keyValue = Application.InputBox("Enter your search term", Type:=2)
    If keyValue = "False" Then Exit Sub: Rem cancel pressed
    
    With ThisWorkbook.Sheets("SourceSheet"): Rem adjust
        Set keyCol = Range("A:A"): Rem adjust
        Set dataRange = Range(.Cells(.Rows.Count, 1).End(xlUp), .Cells(1, .Columns.Count).End(xlToLeft))
    End With
    
    Set destinationSheet = ThisWorkbook.Sheets("destinationSheet")
    Set destinationRange = destinationSheet.Range("a1")
          
    Rem set critrange
        With destinationRange.Parent.UsedRange
            Set critRange = .Parent.Cells(1, .Column + .Columns.Count + 1)
        End With
        If critRange.Column < destinationRange.Column + dataRange.Columns.Count Then
            Set critRange = critRange.Parent.Cells(1, destinationRange.Column + dataRange.Columns.Count)
        End If
        
        Rem write criteria
        Set critRange = critRange.Resize(2, 1)
        critRange.Range("A1").Value = keyCol.Range("a1").Value
        critRange.Range("A2").FormulaR1C1 = "'=" & keyValue
    Rem filter + move
        destinationRange.Resize(, dataRange.Columns.Count).EntireColumn.ClearContents
            
        dataRange.AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=critRange, CopyToRange:=destinationRange, Unique:=False
    Rem clean-up
        critRange.EntireColumn.Delete
End Sub

(Removing the "'=" from this line will find the records that start with the search term, leaving it in will find exact matches only.)

critRange.Range("A2").FormulaR1C1 = "'=" & keyValue