With Application.WorkSheetFunction
.Index(.Match,TabName,Sheets("Lists").Range("J3:J2 0"),0),0)
End With

--

HTH

RP

"GMet" wrote in message
...
I have used Index(Match in my spreadsheets to sum data etc. However, when
I
take it to VBA via the Application.WorkSheet function, I get an error.
Here
is the code:


MyEventRow=Application.WorkSheetFunction.Index(Mat ch,TabName,Sheets("Lists")
.Range("J3:J20"),0),0)
The error focuses on "Match" and says that the function has not been
defined.

I have a list of "Event" tabs in a sheet I call "Lists" (Events are Prg
Mgmt
Milestones & not Excel events)
Basically I need to lookup the row of the "Event" in the "Lists" sheet
based
on the name of the currently selected tab/sheet.

How can I do this?

GMet