How to find multiple strings in VBA

Question

Assume an Excel sheet contains the following values in a random column:

VARIABLE X
AbbA
AddA
bbAA
ccbb
KaaC
cccc
ddbb
ccdd
BBaa
ddbB
Bbaa
dbbd
kdep
mCca
mblp
ktxy

Now the column should be searched for several words and word-phrases at the same time, for example the following:

(1) "bb"

(2) "cc"

(3) "d"

I put the target strings in an array:

Dim searchFor As String
Dim xArr 
searchFor = "bb/cc/d"
xArr = Split(searchFor , "/")

Also assume it does not matter if "bb" is in small letters or big letters (not case sensitive in this case). For the other cases it is case sensitive. At the end I would like to select the respective target cases in terms of their associated rows. Please also note that I would like to include cases in the selection, where the target string (e.g. "bb") is part of a word (e.g. "dbbd").

If possible, ignore the column title ("VARIABLE X) for searching/filtering as well as in the final selection of values.

How can this be done in VBA using (1) filters and/or using (2) regular loops? Which way would you recommend?

@SiddharthRout I have tried to apply a solution (by u): http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s for a long while but I cannot get the code to work with multiple criteria nor can I find the answer to the problem anywhere else — EDC, May 21 '15 at 20:56
LOL.. I was exactly thinking of that thread... If you have huge data in Excel then Autofilter is the best way else you can use loops as well. — Siddharth Rout, May 21 '15 at 20:59
What is it that you want to do after finding them? If you want to just identify the cells then use `.Find` and `.Findnext` as shown [Here](http://www.siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba/) — Siddharth Rout, May 21 '15 at 21:00
From the small amount of code you posted, don't bother with using a variable AND an array, Dim xArr as variant and then set it with xArr = array("bb","cc","d") — Dan Donoghue, May 21 '15 at 21:11
@SiddharthRout ty for the link that really helps, however I could not find any information on how to find "word-phrases" as in the above example that I have added. `.Findnext` searches for multiple occurences. But what I need is to find multiple words and word-phrases, including searching for multiple occurences of course. I could try to loop my string array and try to apply your code on each value inside, is that what you suggest? — EDC, May 21 '15 at 21:26
@SiddharthRout In the example I would like to delete them at the end — EDC, May 21 '15 at 22:04
In that case, I have a very simple solution for you :) But just to be sure... `AbBa` should become `Aa` right? — Siddharth Rout, May 21 '15 at 22:06
no, AbBa should be either selected or deleted. I am trying to ID 'wrong cases' by applying this routine. Making it equal to Aa would make it a 'correct case' instead — EDC, May 21 '15 at 22:10
Then .Find and .FindNext is the fastest way to do it :) Posted an answer to demonstrate it. You may have to refresh the page. — Siddharth Rout, May 21 '15 at 22:29

score 4 · Accepted Answer · answered May 21 '15 at 22:27

AbBa should be either selected or deleted. I am trying to ID 'wrong cases' by applying this routine.

Further to my comments, here is an example using .Find and .FindNext

My Assumptions

We are working with Col A in Sheets("Sheet1")
My Array is predefined. You can use your array.

In the below example, I am coloring the cells red. Change as applicable.

Sub Sample()
    Dim MyAr(1 To 3) As String
    Dim ws As Worksheet
    Dim aCell As Range, bCell As Range
    Dim i As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    MyAr(1) = "bb"
    MyAr(2) = "cc"
    MyAr(3) = "d"

    With ws
        '~~> Loop through the array
        For i = LBound(MyAr) To UBound(MyAr)
            Set aCell = .Columns(1).Find(What:=MyAr(i), LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)

            If Not aCell Is Nothing Then
                Set bCell = aCell
                aCell.Interior.ColorIndex = 3

                Do
                    Set aCell = .Columns(1).FindNext(After:=aCell)

                    If Not aCell Is Nothing Then
                        If aCell.Address = bCell.Address Then Exit Do
                        aCell.Interior.ColorIndex = 3
                    Else
                        Exit Do
                    End If
                Loop
            End If
        Next
    End With
End Sub

enter image description here

That's Awesome. I better hit the sack now! it's already 4:05 am on this part of the planet :P — Siddharth Rout, May 21 '15 at 22:35
God I miss Python when working with VBA... `if any([find in 'your string' for find in ['a','b','c'...]])` — njminchin, Oct 08 '21 at 02:09

score 1 · Answer 2 · answered May 21 '15 at 21:09

1

Start with your data in column A, this:

Sub qwerty()
   Dim i As Long, N As Long
   N = Cells(Rows.Count, "A").End(xlUp).Row

   For i = 2 To N
      t = LCase(Cells(i, 1).Text)
      If InStr(t, "bb") + InStr(t, "cc") + InStr(t, "d") = 0 Then
         Cells(i, 1).EntireRow.Hidden = True
      End If
   Next i
End Sub

will hide the miscreants:

enter image description here

AutoFilter can be tough with more than two options.

answered May 21 '15 at 21:09

Gary's Student

95,722
10
59
99

thanks for the solution. I wonder, does that work case sensitive? So for example can I search for `*bb*` by doing that? (At a sidenote I did not want to hide the rows but rather select them, but I know how to fix this) – EDC May 21 '15 at 21:28
Alright I have never used this function before. Still your solution does not work correctly, as it did not delete for example row 4 (contains "bb") or row 11 (contains "bB" which I wanted to be equal to "bb"). – EDC May 21 '15 at 21:32

score 1 · Answer 3 · answered May 21 '15 at 22:16

One way to delete these would be to use the Advanced Filter. Of course, you don't need VBA for this, but using VBA:

Option Explicit

Sub FilterByStrings()
    Dim rData As Range
    Dim rFiltered As Range
    Dim rCriteria As Range
    Dim vStrings As Variant, critStrings() As Variant
    Dim I As Long

vStrings = VBA.Array("bb", "cc", "d")

Set rData = Range("a1", Cells(Rows.Count, "A").End(xlUp))
Set rFiltered = Range("B1")
Set rCriteria = Range("c1")

'Add the wild cards and the column headers
ReDim critStrings(1 To 2, 1 To UBound(vStrings) + 1)
For I = 0 To UBound(vStrings)
    critStrings(1, I + 1) = rData(1, 1)
    critStrings(2, I + 1) = "<>*" & vStrings(I) & "*"
Next I

'criteria range
Set rCriteria = rCriteria.Resize(UBound(critStrings, 1), UBound(critStrings, 2))
rCriteria = critStrings

rData.AdvancedFilter Action:=xlFilterCopy, criteriarange:=rCriteria, copytorange:=rFiltered
rCriteria.EntireColumn.Clear

End Sub

If you want to return the cells that match those strings, you would set up the criteria range differently, removing the <> and having the criteria in a single column, rather than in adjacent rows.

Oh very interesting, thanks for the entry. I didnt think of advanced filters. — EDC, May 21 '15 at 22:33

How to find multiple strings in VBA

3 Answers3