Searching a Textbox within a Userform for key words

[Qmustard]

Hi all,

I'm hoping what I'm after is quite straight forward.

I have a userform that has a TextBox and a command button on it.
When the userform loads up, it runs a small bit of code from a module that pulls in text from a text file stored on my C drive into the TextBox.

What I'd then like to do, is perform a search on the text that's just been pulled into the TextBox for key words and count how many times those words appear, with the results being in a message box or a label.

It's the search and count that I'm struggling with as I'm not sure where to begin with it.

 

[NoSparks]

perhaps something along the lines of this

Sub Count_KeyWords()
    Dim str As String
    Dim arr As Variant
    Dim i As Long, counter as Long
    Dim rng As Range, cel As Range
str = textbox1.Text
arr = Split(str)
rng = Sheet1.Range("A1:A10") 'the range holding your list of key words
For Each cel In rng
    For i = LBound(arr) To UBound(arr)
        If arr(i) = cel.Value Then
            counter = counter + 1
        End If
    Next i
    MsgBox "The count for Key word " & cel.Value & " is " & counter
    counter = 0
Next cel
End Sub

 

[Qmustard]

perhaps something along the lines of this

 Sub Count_KeyWords()
     Dim str As String
     Dim arr As Variant
     Dim i As Long, counter as Long
     Dim rng As Range, cel As Range
 str = textbox1.Text
 arr = Split(str)
 rng = Sheet1.Range("A1:A10") 'the range holding your list of key words
 For Each cel In rng
     For i = LBound(arr) To UBound(arr)
         If arr(i) = cel.Value Then
             counter = counter + 1
         End If
     Next i
     MsgBox "The count for Key word " & cel.Value & " is " & counter
     counter = 0
 Next cel
 End Sub

Thank you for the response, I'm just testing it now and I get a Run Time Error 91 - Object variable or With block variable not set on the line:

rng = Sheet1.Range("A1:A10") 'the range holding your list of key words

Not sure what that means or how to correct it.

 

[jproffer]

Put "Set" in front of it.

Also make sure the codename of the sheet you want your keywords in is indeed "Sheet1"...or you can use the tab name like this

Sheets("TabName").Range("A1:A10")

 

[Qmustard]

Ah, that worked! Perfect, thank you!

One last question, instead of having the results pop up on a msgbox, what would I be looking to do to have them each in their own label instead?

So If I have a maximum of 10 keywords, I can list them down the side of the userform with the count next to them instead of the msgbox.

 

[jproffer]

Maybe something along these lines:

For Each cel In rng
For k = 1 to 10
For i = LBound(arr) To UBound(arr)
         If arr(i) = cel.Value Then
             counter = counter + 1
         End If
     Next i
     Userform1.Controls("Label" & k).Caption = counter
next k
     counter = 0
Next cel

or something similar, depending on how you name your labels. In this case the names would be "Label1", "Label2", "Label3", etc.

 

[Qmustard]

Maybe something along these lines:

For Each cel In rng
For k = 1 to 10
For i = LBound(arr) To UBound(arr)
         If arr(i) = cel.Value Then
             counter = counter + 1
         End If
     Next i
     Userform1.Controls("Label" & k).Caption = counter
next k
     counter = 0
Next cel

or something similar, depending on how you name your labels. In this case the names would be "Label1", "Label2", "Label3", etc.

Apologies for the delay in replying.

Thank you for this, I will give it a go and see what happens.