How to check for occurences of a range in a string?-CodePudding

So I have a vertical range of a few words, and I have in a cell a sentence in a form of a string. I was wondering how I could write some code in order to check for the number of occurrences of the range of words in the string. Doesn't have to be consecutive. So for example, a1:a3 could be the words apple pear orange, and the string could be "I love pear and apple!" and the function would output 2. I have some code written down here but it only works when the search is with a string, not with a range.

Function WordListCount(TextString As String, rng As String) As Integer

TextString = LCase(TextString)
Dim Result() As String
Dim Result2() As String

Dim Count As Integer
Result = Split(TextString, " ")
Result2 = Split(rng, " ")
Count = UBound(Result())
Dim k As Integer
Dim i As Integer
Dim repeat As Integer
repeat = 0

For i = LBound(Result) To UBound(Result)
For k = LBound(Result2) To UBound(Result2)
If StrComp(Result(i), Result2(k)) = 0 Then
repeat = repeat   1
End If
Next k
Next i

WordListCount = repeat

End Function

Since this code works if what're you're searching for is a string instead of a range, how would I be able to convert a range to a string? preferably separated by a space?

CodePudding user response：

When splitting words in a general sentence structure, you'll likely want to split not just on whitespace but also on punctuation. An easy way of doing this is to use Regular expressions, so add a reference to the library:

Tools -> References -> Microsoft VBScript Regular Expression #

From there, you can split the text string into words and match them against your range:

Function WordListCount(TextString As String, rng As Range) As Integer
    Dim Rex, Matches, Count
    WordListCount = 0
    
    Set Rex = New RegExp
    Rex.Pattern = "\w "
    Rex.Global = True
    
    Set Matches = Rex.Execute(TextString)
    
    For Each Match In Matches
        For Each Cell In rng
            If (StrComp(Match.Value, Cell.Value, vbTextCompare) = 0) Then
                WordListCount = WordListCount   1 ' Found word
                Exit For
            End If
        Next
    Next
End Function

You can refer to the range directly, it doesn't ned to be converted to a string:

Debug.Print WordListCount("I love pear and apple!", Range("A1:A3"))