Application.Worksheetfunction.Match and Array question

[bdepolo]

I have no idea if this is the most efficient way of doing this but I have a range of values within a worksheet that I want to compare to the values in a dynamic array and see if there are any matches. At which point some code would run.

My question is: Is it possible to even run WorksheetFunction.Match on an Array? It's executing the code for every value within my for loop. I'm not terribly familiar with WorksheetFunction.Match (only used it once) and can't find much on if I've gotten the syntax right.

I'm also open to suggestions, this was just the first thing that came to mind (which I find is usually not the most efficient way to do things).

Thanks in advance!

Code Below:

For s = 10 To xlLastRow
     If Application.WorksheetFunction.Match(Range("H" & s).Value, _
             pcband1(), 0) 
     Then
                'Execute Code
     End If
Next s

 

[shg]

Yes, but you need to remove the WorksheetFunction part in able to test the result for an error:

    For s = 10 To xlLastRow
        If Not IsError(Application.Match(Cells(, "H").Value, pcband1, 0)) Then
            'Execute Code
        End If
    Next s

 

[bdepolo]

That worked great, thanks!

 

[shg]

You're welcome, thanks for the feedback.