Geek asks and answers

Excel - VBA: make the comparison method less restrictive (by finding a word in a sentence)

I use the match function in my excel program, but I have a problem with it because it only selects exact matches . For example, if you compare a cell with a Banana with another cell with a Banana, it will work and returns a positive value.
But if you compare the “Banana” with the cell whose content is “Banana choco,” then he won’t know that the word “banana” is in the cell.

In my case, I would like to return TRUE whenever a word is specified in a sentence.

Here is my code:

Worksheets("sBldgMakati").Activate

For i = 2 To 605
  Range("B" & i).Activate
  myResult = IsNumeric(Application.Match(ActiveCell.Value, elementsListRange, 0))

   If myResult Then
   Range("K" & i).Value = Range("K" & i).Value + 10
   Else
   Range("K" & i).Value = Range("K" & i).Value + 0
End If

Next i

, elementsListRange , (, "" ), ActiveCell.value ( "Banana choco" ).

!

+5
2

, :

Function match(searchStr As Variant, matchStr As Variant) As Boolean
    match = False
    If (IsNull(searchStr) Or IsNull(matchStr)) Then Exit Function
    If (matchStr = "") Or (searchStr = "") Then Exit Function
    Dim f As Variant
    f = InStr(1, CStr(searchStr), CStr(matchStr), vbTextCompare)
    If IsNull(f) Then Exit Function
    match = f > 0
End Function

:

Dim searchstr="A Banana found"
Dim isFound as boolean
IsFound=false
For each c in range
    If match(searchstr, c.value) 
         IsFound=true
         Exit for
    End if
End for

, searchStr matchStr , Excel.

+2

Excel ( F1 match):

match_type 0, lookup_value - , - (?) (*) - lookup_value. ; .

, =MATCH("*Banana*",C8,0) 1, C8 "Banana choco".

VBA, , , , , :

myResult = IsNumeric(Application.Match("*" & ActiveCell.Value & "*", elementsListRange, 0))

B2:B605 , elementListRange, , , , - ( ):

For Each cell in elementsListRange.Cells
    myResult = Application.Match("*" & cell.Value & "*", B2:B605, 0)
    if (IsNumeric(myResult)) then
        Range("K" & myResult).Value = Range("K" & myResult).Value + 10
    end if
next
+5

More articles:


All Articles