vba WorksheetFunction.Match search for multiple values

Question

I am currently searching a HR sheet for a column header that contains specific text. This text can change each week, for example, a column called "State/Province" could have no spaces in it one week, but the following week it could be "State / Province" (notice the space between the words).

I am currently using the below code that looks for one condition

StateProvince = WorksheetFunction.Match("State/Province", hr.Sheets("Open").Rows(1), 0)

This works fine, but I am looking to add to this so that it looks for the second example containing the spaces if this is the case for the column header. Any suggestions?

i get an error if the first condition is not found, an i am trying to make the code as dynamic as possible — Aaron C
– Aaron C, Commented Jun 28, 2016 at 10:18
Trap them with errors, using worksheet functions in VBA will give an error, so if there is an error in the 1st use the 2nd if not, skip the 2nd. Or use wildcards * — Nathan_Sav
– Nathan_Sav, Commented Jun 28, 2016 at 10:20

Mrig · Accepted Answer · 2016-06-28 10:57:38Z

2

Use:

StateProvince = WorksheetFunction.Match("State*/*Province", hr.Sheets("Open").Rows(1), 0)

This answer is specific to your question. If you want more generic solution you'll have to provide other examples.

edited Jun 28, 2016 at 10:57

answered Jun 28, 2016 at 10:18

Mrig

11.7k2 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Aaron C Over a year ago

what does * do? could i place this at the start or end of the words as well to cater for possible spaces before or after each word?

Mrig Over a year ago

@AaronC - * is a wildcard character and can take the place of any number of characters. For example, "Tr*y" matches with "Tray", "Troy", and "Trolley". See this for details.

Mrig Over a year ago

@AaronC - In your example State*/*Province will even match for strings like States/Province, State/New Province, etc.

Aaron C Over a year ago

This should solve any problems i had, thanks for the help

user3598756 · Accepted Answer · 2016-06-28 10:36:14Z

Since the unpredictable appearance of spaces you'd better ignore them altoghether by means of a custom MyMatch() function to which pass "unspaced" string, like follows:

Function MyMatch(textToSearch As String, rng As Range) As Long
    Dim txtRng As Range, cell As Range

    MyMatch = -1 '<--| default value for no match found
    On Error Resume Next 
    Set txtRng = rng.SpecialCells(xlCellTypeConstants, xlTextValues)  '<--| consider only cells with text values
    On Error GoTo 0
    If Not txtRng Is Nothing Then '<--| If there's at least one cell with text value
        For Each cell In txtRng '<--| loop through selected "text" cells
            If WorksheeyFunction.Substitute (cell.Value, " ", "") = textToSearch Then '<--|remove every space occurrence from cell value and compare it to your "nospace" searched value
                 MyMatch = cell.Column - rng.Columns(1).Column + 1
                 Exit For
            End If
        Next cell 
    End If
End With

To be used like follows:

Dim StateProvince As Long
StateProvince = MyMatch("State/Province", hr.Sheets("Open").Rows(1)) '<--| pass an "unspaced" string to search
If StateProvince > 0 Then
     ' code for handling found StateProvince
Else
     ' code for handling NOT found StateProvince
End If

Collectives™ on Stack Overflow

vba WorksheetFunction.Match search for multiple values

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related