Using Application.Match and an array with wildcards

Question

So I have some code similar to this and it works well and seems to be fast and simple. I like that I can place a few dozen words in the array for words to search for.

Dim ArrDataType As Variant

ArrDataType = Array("Circle:", "Point:")

For i = 1 To 10 Step 1
    
    CurrentCellValue1 = Trim(mybook.Sheets(SourceSh).Range("A" & i))
    
    If IsNumeric(Application.Match(CurrentCellValue1, ArrDataType, 0)) Then  
        Debug.Print "Match Found!: " & CurrentCellValue1
    End If
    
Next i

However I was trying to modify this to find words using wildcards but can't figure out how to do it. For example:

Dim ArrDataType As Variant

ArrDataType = Array("Circle:*", "Point:*")

For i = 1 To 10 Step 1
    
    CurrentCellValue1 = Trim(mybook.Sheets(SourceSh).Range("A" & i))
    
    If IsNumeric(Application.Match(CurrentCellValue1, ArrDataType, 0)) Then  
        Debug.Print "Match Found!: " & CurrentCellValue1
    End If
    
Next i

This doesn't work so clearly I am implementing the wildcard incorrectly. I know Could search letter by letter and things but I have thousands of rows to search (not only 10) and I assume these other methods would perform quite a bit slower. It seems like it shouldn't be hard to incorporate wildcards into this code that is already working well.

Scott Craner · Accepted Answer · 2025-07-21 17:46:52Z

2

Iterate the array inside the column iteration. Then I would use Instr instead. No need for WildCards.

Dim ArrDataType As Variant

ArrDataType = Array("Circle:", "Point:")

For i = 1 To 10 Step 1
    
    CurrentCellValue1 = Trim(mybook.Sheets(SourceSh).Range("A" & i))

    For j = lbound(ArrDataType) to ubound(ArrDataType)
    
        If Instr(CurrentCellValue1,ArrDataType(j)) > 0 Then  
            Debug.Print "Match Found!: " & CurrentCellValue1
            Exit For
        End If
    Next j
    
Next i

answered Jul 21 at 17:46

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

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

3 Comments

XCELLGUY Jul 21 at 17:51

Ugh... That seems like it will be slow... But I will try it.

Scott Craner Jul 21 at 17:57

One way to speed up, would be to load the values from the sheet into an array and iterate that on the outer loop. But iterating an array will not be that much slower on a limited array. If your array were hundreds of items then, yes, but not a handful.

Scott Craner Jul 21 at 18:01

Actually I had it backwards: fastexcel.wordpress.com/2011/10/26/… But again with the handful, I doubt you will notice a difference. And if your list is larger, especially the outer loop, then array will be quicker.

rotabor · Accepted Answer · 2025-07-22 07:51:36Z

0

With the "Microsoft VBScript Regular Expressions 5.5" library you can get the result as fast and easy as possible:

Option Explicit

Sub Test1()
  Dim re As New RegExp, r, i&
  re.Pattern = "Circle:|Point:": r = ThisWorkbook.Sheets("Sheet1").[C1:C10]
  For i = LBound(r, 1) To UBound(r, 1)
    If re.Test(r(i, 1)) Then
      Debug.Print "Match Found!: " & r(i, 1)
    End If
  Next
End Sub

For the summary:

Sub Test2()
  Dim re As New RegExp, r As Boolean
  re.Pattern = "Circle:|Point:"
  r = re.Test(ThisWorkbook.Sheets("Sheet1").Evaluate("TEXTJOIN("" "",TRUE,A1:A10)"))
  Debug.Print "Match" & IIf(r, "", " not") & " found"
End Sub

answered Jul 22 at 7:51

rotabor

5,6072 gold badges5 silver badges23 bronze badges

Collectives™ on Stack Overflow

Using Application.Match and an array with wildcards

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related