Userform:Search through all ws / show results in Listbox

Hello everyone,
I have very large wordlist ( 0.5 Million Rows, 11 columns, words only in column A) and would like to search through all worksheets of it with the help of a Userform (containing one textbox for words to be searched and a listbox for the results). When I type a word in the textbox and then click on the button search, i want a list of all found results in the listbox.

I will be thankful for each kind of help.

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?

Hi Mojo249,
thank you very much for your solution. It works great with 0.5 Million words and fast.
The thing is that i work in a very large firm where they have only office 2003 and we are not allowed to install own software. Thats the reason that i splitted the large wordlist into many ws so that i can use it with excel 2003.
I will be very gratefull if you could add one more modification in the code: I need to copy and paste sometimes one of the results and its not working for me at the moment with shorcutkeys of copy and paste. Can you maybe add another button "copy" so that i can copy the selected result and then paste it where I want?

thank you very much

Hi jaslake,
thanks for your interest and for reading my problem. Sorry, but i couldnt understand you question.

Hi,
i got two more problem with it.

1) I added one more column and it shows me "Error typ 13" at following ling:

    If InStr(LCase(ws.Cells(varRow, 1).Value), varSearchString) <> 0 Then

2) it does not search through all ws

Hi wali
What I am asking is if you have a list of words that you'll frequently be searching for.

Hi wali
Give me a sampling of the word list you'll be using...as many as you like...but at least 10 to 15. Please include the word list on a worksheet in a new copy of your Excel file. The new Excel file should also include this

I added one more column and it shows me "Error typ 13"

Hi wali
I attempted to respond to your private message but was blocked. The purpose of the Word List would be to enhance the entry of words that you typically use in the Search Textbox. If you use the same words over and over, the Search Textbox could be made to AutoFill as you type. A Dictionary is not what I had in mind...only a list of frequently searched for words.

Attached is a workbook with revised code that accommodates this

it does not search through all ws

If this is a continuing issue

I added one more column and it shows me "Error typ 13"

then I'll need to see your new file with the additional column.

Regarding this

I need to copy and paste sometimes one of the results and its not working for me at the moment with shorcutkeys of copy and paste. Can you maybe add another button "copy" so that i can copy the selected result and then paste it where I want?

As you discovered, one cannot copy/paste from a ListBox on a UserForm. However, with code, you CAN place one or multiple items that you select into another location in the workbook. If you describe what you wish to do, I believe the code can be added to allow you to select an item from the results ListBox and place it "where you want".

Hi wali

Try this attachment...sorry 'bout that...don't know what happened.

Hi wali

Private messaging is a reasonable tool for confidential information...however, if it's limited to one way conversation, it serves no purpose. Either you've blocked private messaging or your environment doesn't allow it.
Either turn private messaging on on your end or don't send them to me. I've tried twice to respond to your private messages and have been blocked. Frankly, it doesn't make me happy.

The link you provided links to a Dictionary
http://www.qamosona.com//downloads/dict2.rar

I've not introduced this dictionary to the code so I'd suggest it's not a source of your issue. Post a file that demonstrates your issue and perhaps I can help you resolve it.

In the meantime, the attached appears to resolve this

I would like to add another ws "de-ps" to the file and would like to paste the higlighted results into the last row of the ws "de-ps".

Let me know of issues.

Hi,
i am very sorry for the thing with private message. Each time I tried to send you a private message, i was told that its not possible to do so cus "you are not in my contacts or blocked" ??..?. And then i found a check box below "send the message any how.." and as i activated it, i was able to send you the message. I looked into my acount settings and couldnt find where to modify it in a way so that each member can send me private messages. Any way sorry for that! I dont know yet how to change the settings.

Now to the code:
1) the file i have uploaded contains the excel file with original wordlist. I have named it "dict" but its an excel file (compressed as rar) the same as you have worked with. The only difference is that the one i have sent you the link of contains 0.5 million words (each worksheet contains smaller amount of rows than 56000). And i think the source of error is just the larger number of rows.
The code you have sent works perfectly with small list. But as soon as i try it with large list it shows me erron on following code line:

If InStr(LCase(ws.Cells(varRow, 1).Value), varSearchString) <> 0 Then

2) Can the code be modified in such a way that it doesnt delet/replace the content of ws "de-ps" after copy button is pressed? It will be great if it searches for the last row and copies the selected result under the last row of "de-ps".

Hi wali

Regarding this

Can the code be modified in such a way that it doesn't delete/replace the content of ws "de-ps" after copy button is pressed?

In this code

Private Sub CommandButton3_Click()
    Dim i As Integer
    'Dim j As Integer
    Dim LR As Long
 
    Sheets("ps-de").Cells.ClearContents
    LR = Sheets("ps-de").Range("A" & Rows.Count).End(xlUp).Row + 1
    With lstResults
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                Sheets("ps-de").Range("A" & LR).Value = .List(i)
                LR = LR + 1
            End If
        Next i
    End With
End Sub

Either delete or comment out this line of code

Sheets("ps-de").Cells.ClearContents

Regarding this

the file i have uploaded contains the excel file with original wordlist. I have named it "dict" but its an excel file (compressed as rar) the same as you have worked with.

I don't currently have a utility to uncompress .rar files but I know they're freely available. I'll look into that but I use .zip files. If you could use .zip, that may be helpful.

I'd assume the .rar file is much of our communication issue. However, IF you have a file that has a half million entries, how many do you use on a regular basis...10, 100, 1000, 10,000??? You can add to the list as you please.

Hi,
thank you very much for the modifications. Unfortunately it again works only with small amount of rows. As soon as I try to use it with the original wordlist (0.5 Million Words) it shows me erro in line :

If InStr(LCase(ws.Cells(varRow, 1).Value), varSearchString) <> 0 Then

I use always the same list which has about 0.5 rows, which I am now uploading. I am sending you here the link for download.

"link was removed"

Please, let me know after you have downloaded it, so that i can delete the link again (cus of copy right matters)

regards

Regards

Hi wali
I've downloaded your zip file. The procedure halts when it gets to row 8270 of Sheet 1. The data in A8270 is bad data (#NAME?). If you figure out what's causing the bad data, the procedure should run fine.

The procedure probably could be modified to skip these invalid items. Let me know how you wish to proceed.

PS: after seeing your file, I see I totally misunderstood what you were saying. I apologize.

Hi wali

Should you choose to go this route

The procedure probably could be modified to skip these invalid items

insert two lines of code as follows

For varRow = 1 To LR
                On Error Resume Next
                If InStr(LCase(ws.Cells(varRow, 1).Value), varSearchString) <> 0 Then
                    LR = wsTemp.Range("A" & Rows.Count).End(xlUp).Row
                    wsTemp.Range("A" & LR).Offset(1, 0).Value = ws.Cells(varRow, 1).Value
                End If
               On Error GoTo 0

Let me know how you make out.

Hi wali

Glad I could help.