Good day to you, I have 3 worksheets titled, "response" "positive wordlist," and "negative wordlist"
In response, there is a input cell A(11). Given a sentence, the vba code will search for all words contained in A(11) in both positive and negative wordlist. Then the code must determine a fraction,
The number of positive words divided by the number of negative words.
Your assistance is greatly appreciated!
Sorry, I missed the word VBA. After I figured out a formula, I came back to post it and saw you wanted a VBA solution. So here is a vba solution.
It puts up a message box with the result - you didn't say what to do with the fraction. You should be able to change the code to output what you want.
Also, if a sentence has repeat words in it, each word gets counted separately. For example in my test string, I had "the" in the sentence twice and it both matched the positive wordlist so that added a count of 2 to the positive wordlist match count.
Also, you didn't say where your wordlists were on the sheet, so I seach the whole usedrange of the sheet. Make any adjustments you need to the code.
Dim sh1 As Worksheet
Dim r1 As Range, r2 As Range, r3 As Range, i As Long
Dim sh2 As Worksheet, sh3 As Worksheet, s As String
Dim v As Variant, cntPos As Long, cntNeg As Long
cntPos = 0
cntNeg = 0
Set sh1 = Worksheets("Response")
Set r1 = sh1.Range("A11")
Set sh2 = Worksheets("Positive Wordlist")
Set sh3 = Worksheets("Negative Wordlist")
Set r2 = sh2.UsedRange
Set r3 = sh3.UsedRange
s = Application.Trim(r1)
v = Split(s, " ")
For i = LBound(v) To UBound(v)
Debug.Print i, v(i), Application.CountIf(r2, v(i)), Application.CountIf(r2, v(i))
If Application.CountIf(r2, v(i)) > 0 Then
cntPos = cntPos + 1
ElseIf Application.CountIf(r3, v(i)) > 0 Then
cntNeg = cntNeg + 1
If cntNeg > 0 Then
s = "Positive count: " & cntPos & vbNewLine & _
"Negative count: " & cntNeg & vbNewLine & vbNewLine & _
"fraction: " & Format(cntPos / cntNeg, "#.####")
s = "No negative words matched - fraction is undefined"
tested and worked as I understand the requirement.