why is match not working in this case?

[yxz152830]

Gurus,
below code returns "unable to get the match property of the worksheetfunction class"

for each tb1 in sheets("abc").listobjects
  a= worksheetfunction.match( "SS", tb1.headerrowrange,0)
next tb1

Thanks!

 

[BSALV]

when you don't have a match with worksheetfunction, you get an error.
Use application, that 'll result in a number, in case of a match or an errorvalue, but want stop your macro

For Each tb1 In Sheets("abc").ListObjects
     a = Application.Match("SS", tb1.HeaderRowRange, 0)
     If IsNumeric(a) Then MsgBox "found"
Next tb1

 

[Alex Blakenburg]

I am probably in the minority but I like to "dim a as long" so I do it this way.

    Dim a As Long
    For Each tb1 In Sheets("abc").ListObjects
         a = Application.IfError(Application.Match("SS", tb1.HeaderRowRange, 0), 0)
         If a <> 0 Then MsgBox "found"
    Next tb1