VBA (Excel): Find Based on Multiple Search Criteria Without Looping

Question

I have a large data sheet that I want to search in VBA based on 3 sets of criteria. Each row entry can be assumed to be unique. The format of the sheet/data itself cannot be changed due to requirements. (I've seen several posts on related questions but haven't found a working solution for this yet.)

At first I used the classic VBA find method in a loop:

Set foundItem = itemRange.Find(What:=itemName, Lookin:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows)
If Not foundItem Is Nothing Then
    firstMatchAddr = foundItem.Address
    Do
        ' *Check the other fields in this row for a match and exit if found*
        Set foundItem = itemRange.FindNext(foundItem)
    Loop While foundItem.Address <> firstMatchAddr  And Not foundItem Is Nothing
End If

But because this needs to be called a number of times on large sets of data, the speed of this was no good.

I did some searching and found that I could use the match method with index. So I unsuccessfully tried many variations of that such as:

result = Evaluate("=MATCH(1, (""" & criteria1Name & """=A2:A" & lastRow & ")*(""" & criteria2Name & """=B2:B" & lastRow & ")*(""" & criteria3Name & """=C2:C" & lastRow & "), 0)")

And

result = Application.WorksheetFunction.Index(resultRange, Application.WorksheetFunction.Match((criteria1Name = criteria1Range)*(criteria2Name = criteria2Range)*(criteria3Name = criteria3Range))

And

result = Application.WorksheetFunction.Index(resultRange, Application.WorksheetFunction.Match((criteria1Range=criteria1Name )*(criteria2Range=criteria2Name )*(criteria3Range=criteria3Name ))

Then I tried using AutoFilter to sort:

.Range(.Cells(1,1), .Cells(lastRow, lastCol)).AutoFilter Field:=1, Criteria1:="=" & criteria1Name
.Range(.Cells(1,1), .Cells(lastRow, lastCol)).AutoFilter Field:=2, Criteria1:="=" & criteria2Name
.Range(.Cells(1,1), .Cells(lastRow, lastCol)).AutoFilter Field:=3, Criteria1:="=" & criteria3Name

But because one of the sorting columns contains dates, I had issues getting AutoFilter to work properly.

My question is, how can I search through columns in Excel VBA based on multiple criteria, without looping, returning either the row number or the value in the cell of that row that I am interested in?

Thar

Why are you against looping? How many rows are you talking? – Warfore

What did you wang to do with the returned results? – Crossbones

Answer 1

You could use an Advanced Filter. Put the column headers in a separate part of the sheet (or a different sheet altogether). Under those column headers, put the criteria you're looking for in each column. Then name that range (including the headers) something like "Criteria". Then the macro becomes:

Sub Macro1()

    Sheets("Sheet1").Range("A1").CurrentRegion.AdvancedFilter xlFilterInPlace, Range("Criteria")

End Sub

As a follow up to my comment below, to have the VBA create the criteria range behind the scenes:

Sub Macro1()

    'Code up here that defines the criteria

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    With Sheets.Add
        'Create the advanced filter criteria range
        .Range("A1") = "HeaderA"
        .Range("B1") = "HeaderB"
        .Range("C1") = "HeaderC"
        .Range("A2") = criteria1Name
        .Range("B2") = criteria2Name
        .Range("C2") = criteria3Name

        'Alternately, to save space:
        '.Range("A1:C1").Value = Array("HeaderA", "HeaderB", "HeaderC")
        '.Range("A2:C2").Value = Array(criteria1Name, criteria2Name, criteria3Name)

        'Then perform the advanced filter
        Sheets("Sheet1").Range("A1").CurrentRegion.AdvancedFilter xlFilterInPlace, .Range("A1:C2")

        'Remove behind the scenes sheet now that the filter is completed
        .Delete
    End With

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

Norite

That's an interesting approach. I can't modify the contents of the workbook in any way, though, and the set of criteria is dynamic based on another set of inputs. I'm really looking for a way to do this exclusively in VBA. – Thar

You could create the criteria range in vba, even create a temp worksheet that will get deleted afterwards, just disable screenupdating and the user will never see the behind the scenes stuff – Norite

I have updated the answer to expand on what I was talking about in my previous comment – Norite

+1 a little easier is to build an IF formula testing fir all three critria, then filtering the True values in a single column – Crossbones

+1. I've succeeded in filtering the multiple criterias. After I do what I need to do with the filtered values, I would like to be able to unfilter the spreadsheet; however, the filter arrow in the header is not displayed. Is it possible to make it appear together with the filtering? I worked around this implementing another macro with an empty search criteria to filter all rows within the data. – Silverside

Answer 2

You can use EVALUATE for multiple criteria like so to return the row numbers of mathcing values. This uses the same approach as Is it possible to fill an array with row numbers which match a certain criteria without looping?

• It searches 50000 rows to match
  • the first four letters in column A matches fred
  • the date in B to be greater than 1/1/2001
  • apple in column 5
• Any rows matching these criteria are retuned as row numbers in x

(rows 1 and 5 in picture below)

code

Sub GetEm2()
x = Filter(Application.Transpose(Application.Evaluate("=IF((LEFT(A1:A10000,4)=""fred"")*(B1:B10000>date(2001,1,1))*(C1:C10000=""apple""),ROW(A1:A10000),""x"")")), "x", False)
End Sub

Application.Transpose is limited to 65536 cells, so a longer range needs to be "chunked" into pieces.

Crossbones

Did you forget to add insert Dim x in the code? After having the rows number stored in x, how would you filter these rows? Tks. – Silverside

No, X is a variant. – Crossbones

So yes, he forgot to add Dim x as Variant. Always use Dim, even for variants. – Oneil

But better would be Dim x() As String because that is what it returns: an array of strings. Nice one-liner though, +1. – Oneil