Userform:Search through all ws / show results in Listbox

Hi

Refer to the file attached. The code will search column A on sheet 1 for all cells which contain the search string entered into the search box.

You can change the sheet that is searched by changing the line Set ws = Sheets("1").

You say that there are potentially 1/2 million rows of data to be searched. 1) You'll notice the following lines:

wsTemp.Range("A65000").End(xlUp)
varLastRow = ws.Range("A65000").End(xlUp).Row

Because your file is Excel 2003 (and limited to 65k rows), I used 65000 but you'll need to increase those if you want to use it in Excel 2007 with more than 65000 records. 2) The code uses a loop which may be slow for so many rows. If it is, let us know and we'll find an alternative.

Hope this helps.

Dion

Private Sub cmdSearch_Click()

Dim ws As Worksheet
Dim wsTemp As Worksheet
Dim varSearchString As String
Dim varRow As Long
Dim varLastRow As Long

If Me.txtSearchString.Text = "" Then
    MsgBox "Please enter a search term.", vbCritical
    Exit Sub
End If

Application.ScreenUpdating = False

Me.lstResults.RowSource = ""

Set ws = Sheets("1")
Set wsTemp = Sheets("tmpSearch")

wsTemp.Range("A2:A50000").Clear
varLastRow = ws.Range("A65000").End(xlUp).Row
varSearchString = LCase(Me.txtSearchString.Text)

For varRow = 1 To varLastRow
    If InStr(LCase(ws.Cells(varRow, 1).Value), varSearchString) <> 0 Then
        wsTemp.Range("A65000").End(xlUp).Offset(1, 0).Value = ws.Cells(varRow, 1).Value
    End If
Next varRow

With wsTemp
    .Columns("A:A").EntireColumn.AutoFit
End With

Application.ScreenUpdating = True

If wsTemp.Range("A2").Value = "" Then
    MsgBox "No results found.", vbInformation
Else
    Me.lstResults.ColumnWidths = wsTemp.Columns(1).Width
    Me.lstResults.RowSource = "=OFFSET(tmpSearch!$A$2,0,0,COUNTA(tmpSearch!$A$2:$A$65000),1)"
End If

End Sub

Hi wali
Do you have a defined list of search terms or is it truly free style?