Find rows that contain words that I need

Question

I currently have a BD with almost 1500 rows. Each row has a string of different characters (all within the same cell), in some cases, there are similar words within each row. For example:

Row 1: Tempera for children of color red 10 grams 
Row 2: Tempera for adults color black 50 grams 
Row 3: Tempera for children of color yellow 10 grams 
Row 4: Tempera for adults color black 10 grams

I am trying to create a macro in which you enter two or more words and that these words are searched in each row, and subsequently, I explain which are the lines that contain the two or more words. For example:

If I entry the words:

"black tempera" : Rows 2, 4
"children" : Rows 1, 3
"10 tempera" : Rows 1,3 and 4

If you have no more than two search criterias, you can use the `AutoFilter` function. Why make a big deal with VBA? — Physikbuddha, May 18 '15 at 08:52

score 1 · Answer 1 · edited May 23 '17 at 10:26

This might not be quite what you're looking for, but one approach rather than writing a macro from scratch would be to use a concatenation function like this one with an array formula:-

=myconcat(",",IF((1+ISBLANK(B1)-ISERROR(SEARCH(B1,$A$1:$A$4)))*(2-ISBLANK(B1)-ISBLANK(C1))*(1+ISBLANK(C1)-ISERROR(SEARCH(C1,$A$1:$A$4))),ROW($A$1:$A$4),""))

where the strings to be searched are in A1:A4 and the words to search on are in B1 and C1.

The formula has to be entered in D1 with Ctrl Shift Enter and pulled down.

enter image description here

score 1 · Accepted Answer · answered May 18 '15 at 14:07

you can use the following code:

Private Function SearchForOneStringInArr(oneString As String, arr() As String) As Boolean
   Dim i
    For i = 0 To UBound(arr)
       If InStr(1, LCase(oneString), LCase(arr(i))) = 0 Then
            SearchForOneStringInArr = False
            Exit Function
        End If
    Next
SearchForOneStringInArr = True
End Function
Public Function mySearch(rng As Range, toSearch As String) As String
   Dim i As Integer, cell As Range
   i = 1
   Dim strArr() As String
   Dim resultStr As String
   resultStr = "Rows:"
   strArr = Split(toSearch, " ")
   For Each cell In rng.Cells
        If SearchForOneStringInArr(cell.Value, strArr) = True Then
           resultStr = resultStr & Str(i) & " , "
        End If
        i = i + 1
    Next
    mySearch = resultStr
End Function

use the following formula:

=mySearch(A1:A4, "black tempera")
=mySearch(A1:A4, "children")
=mySearch(A1:A4, "10 tempera")

Find rows that contain words that I need

2 Answers2