HELP - making text search code case and plural insensitive

[Modonnell43]

I am struggling to modify this code (below) to handle fuzzy matches, e.g., treating words like dog, dogs, Dog, and Dogs the same. Any suggestion as to how I can modify this code to do this?
————
Function Count_Positives(Sentence As String, Positive_Words As Range) Dim Counter As Integer
For x = 1 To Positive_Words.Rows.Count Counter = Counter + (Len(Sentence) - _ Len(Replace(Sentence, Positive_Words(x), ""))) _ / Len(Positive_Words(x)) Next x Count_Positives = Counter

End Function

 

[Chihiro]

By default Replace function uses binary compare. Add the optional compare mode argument and set it to vbTextCompare.

See link for detail on Replace function.
https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/replace-function

 

[Modonnell43]

Thank you for the help. I think I'm getting closer but I'm a newbie. I modified my code as follows but now the counts are off. Can you see where I went wrong?

Function Count_Positives(Sentence As String, Positive_Words As Range)

Dim Counter As Integer

For x = 1 To Positive_Words.Rows.Count
  Counter = Counter + (Len(Sentence) - _

  Len(StrComp(Sentence, Positive_Words(x), vbTextCompare))) _

  / Len(Positive_Words(x))

Next x

Count_Positives = Counter

End Function

 

[Chihiro]

Upload sample workbook. Without it, I can't really comment on what is going wrong.

 

[Modonnell43]

Thanks. Sample workbook uploaded.

 

[Chihiro]

Oh, didn't realize you changed Replace to StrComp.

If using Replace, you need something like...

Function Count_Positives(Sentence As String, Positive_Words As Range)
Dim Counter As Long
Dim cel As Range
For Each cel In Positive_Words
    Counter = Counter + (Len(Sentence) - _
        Len(Replace(Sentence, cel.Value, "", , , vbTextCompare))) _
        / Len(cel.Value)
Next
Count_Positives = Counter

End Function

StrComp isn't suitable for your need.

 

[Modonnell43]

Thank you. It worked like a charm.

....Now on to solving the plurality challenge.

 

[Chihiro]

Plural would be difficult, or impossible without dictionary/lookup table of some sort.

Ex:
Plural for dog is dogs and is easy to deal with.

But, there are other patterns.
dish = dishes
hero = heroes
cherry = cherries
soliloquy = soliloquies
on and on...

 

[Modonnell43]

Is there a VBA wildcard operator?

 

[Chihiro]

What I’m saying is that wildcard alone can’t deal with all the different patterns that exist.

 

[Modonnell43]

Understood. I think that my application isn’t critical enough to worry about those errors as long as I can handle the most frequent cases ‘s’ and ‘es’. For that I believe wilcards will work.

 

[Chihiro]

Replace will already work for that. Meaning both dog and dogs will count as match to dog.

 

[Modonnell43]

Chihiro,

I hope all is well with you.
I have been using this Vba script for awhile now and wanted to know if there is a solution to preventing the Excel error "#Value" from occurring when there is a blank/empty cell in the range Positive_Words ?

Function Count_Positives(Sentence As String, Positive_Words As Range)
Dim Counter As Long
Dim cel As Range
For Each cel In Positive_Words
    Counter = Counter + (Len(Sentence) - _
        Len(Replace(Sentence, cel.Value, "", , , vbTextCompare))) _
        / Len(cel.Value)
Next
Count_Positives = Counter

End Function

 

[Chihiro]

Try...

Function Count_Positives(Sentence As String, Positive_Words As Range)
Dim Counter As Long
Dim cel As Range
For Each cel In Positive_Words
    If Len(cel.Value) Then
        Counter = Counter + (Len(Sentence) - _
        Len(Replace(Sentence, cel.Value, "", , , vbTextCompare))) _
        / Len(cel.Value)
    End If
Next

Count_Positives = Counter

End Function

 

[Modonnell43]

Oh, didn't realize you changed Replace to StrComp.

If using Replace, you need something like...

Function Count_Positives(Sentence As String, Positive_Words As Range)
Dim Counter As Long
Dim cel As Range
For Each cel In Positive_Words
    Counter = Counter + (Len(Sentence) - _
        Len(Replace(Sentence, cel.Value, "", , , vbTextCompare))) _
        / Len(cel.Value)
Next
Count_Positives = Counter

End Function

StrComp isn't suitable for your need.

Try...

Function Count_Positives(Sentence As String, Positive_Words As Range)
Dim Counter As Long
Dim cel As Range
For Each cel In Positive_Words
    If Len(cel.Value) Then
        Counter = Counter + (Len(Sentence) - _
        Len(Replace(Sentence, cel.Value, "", , , vbTextCompare))) _
        / Len(cel.Value)
    End If
Next

Count_Positives = Counter

End Function

Chihiro,

I hope all is well with you.
I have been using this Vba script for awhile now and wanted to know if there is a solution to preventing the Excel error "#Value" from occurring when there is a blank/empty cell in the range Positive_Words ?

Function Count_Positives(Sentence As String, Positive_Words As Range)
Dim Counter As Long
Dim cel As Range
For Each cel In Positive_Words
    Counter = Counter + (Len(Sentence) - _
        Len(Replace(Sentence, cel.Value, "", , , vbTextCompare))) _
        / Len(cel.Value)
Next
Count_Positives = Counter

End Function

Try...

Function Count_Positives(Sentence As String, Positive_Words As Range)
Dim Counter As Long
Dim cel As Range
For Each cel In Positive_Words
    If Len(cel.Value) Then
        Counter = Counter + (Len(Sentence) - _
        Len(Replace(Sentence, cel.Value, "", , , vbTextCompare))) _
        / Len(cel.Value)
    End If
Next

Count_Positives = Counter

End Function

That worked well ! Thank you for your help!