MATCH (or FIND) with multiple criteria and wildcards

[Dr. Demento]

I originally asked this question at MrExcel but got no takers so I thought I'd try here.

Using VBA, I need to find the .address of the cell within a header (rngHeader) that contains either "DoB" or "Birth*" ([Birth* = Birth or Birthdate or Birthday]; I'd like the seach/match to be case-insensitive).
This will capture any variation of Birth:

rngDoB = WorksheetFunction.Match("BIRTH*", rngHeader, 0)

but this won't incorporate DoB (I tried both "&" and "or" separately)

rngDoB = WorksheetFunction.Match("BIRTH*" &/or "DoB", rngHeader, 0)

I searched around but couldn't come up with anything that merged both multiple criteria and wildcards.

According to this post, I can assign multiple criteria by referencing the specific cells and concatenating the named range/cell; however, I'm actually searching for the column that contains ANY of the search criteria. Again, I'm trying to find the single column in the header that could contain any of the following terms: DoB OR Birthdate OR Birthday OR Date of Birth.

Thanks, y'all.

EDIT: I just realized I probably should have posted this question in the VBA Forum; my apologies. Mod, if you could transport my inquiry, I'd be appreciative ;-)

 

[Deepak]

Moved to Appropriate forum..

There are multiple ways to do so.

Check this..

Option Explicit

Sub test()

Dim frng As Range, rngHeader As Range

Set rngHeader = [A1:D1]

Set frng = rngHeader.Find("BIRTH", , , xlPart, , , True)

If frng Is Nothing Then Set frng = rngHeader.Find("DOB", , , xlPart, , , True)

If Not frng Is Nothing Then Debug.Print frng.Address

End Sub

 

[jindon]

One way

    Dim x
    x = Filter(Application.IfError(Application.Match(Array("DOB", "*Birth"), rngHeader, 0), Chr(2)), Chr(2), 0)
    If UBound(x) > -1 Then
        Set rngDOB = rngHeader.Cells(x(0))
        MsgBox rngDOB.Address
    Else
        MsgBox "Not found"
    End If

 

[Dr. Demento]

@Deepak, thanks for moving my question. I had thought of nested tests, but had shyed away. Appreciate the knowledge that that's an alternative, though.

@jindon, thanks! Two questions: 1) is the variable 'x' a Variant (in the interest of Option Explicit) and 2) my original example listed the two criteria; how would I alter the formula to have multiple criteria (other than adding them in the Array); do more Chr(2) have to be added when adding more criteria??

Thanks again both of you for the assist!!!

 

[jindon]

1) Variant or String array x() As String
2) I don't understand what you are asking.

 

[Dr. Demento]

1. Thanks
2. If I had three search criteria, how would the x formula change? Would I just have to add the additional criteria to the Array or would other changes be required as well?

x = Filter(Application.IfError(Application.Match(Array("CRITERIA1","*CRITERIA2", "CRITERIA3"), rngHeader, 0), Chr(2)), Chr(2), 0)

 

[jindon]

And what to find?
All 3 or just one of them?
If it hits more than one criteria, what do you want to do?

 

[Dr. Demento]

The scenario is that I'm looking within the header of a table/range and I need to identify each column so that I can apply various formatting/validation rules. The intent is that there only one header cell that contains any one of the listed criteria; it's up to me to ensure that there wouldn't be multiple hits (wouldn't use ambiguous search terms like Date).

For example, the search criteria could be any of the following: DoB, Birth*, Nacimiento (birth in Spanish). However, there should only be a single header cell that contains any one of these terms.

Using Deepak's example, if I were to add additional search criteria, I'd add another If Then statement to evaluate the presence of the third criteria.

Set frng = rngHeader.Find("BIRTH", , , xlPart, , , True)
If frng Is Nothing Then Set frng = rngHeader.Find("DOB", , , xlPart, , , True)
If frng Is Nothing Then Set frng = rngHeader.Find("Nacimiento", , , xlPart, , , True)

Sorry I'm not being very clear.

 

[jindon]

Then you can add as many criteria as you want.
If it hits, ubound(x) > -1 and x(0) will be always the column reference, else , not hit, ubound(x) is always -1 as my original code.

 

[Dr. Demento]

Excellent!! Thanks so much.