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

I think you are missing some parentheses - it needs to be
of the form Match(....) and you have Match,...

My guess is that this is what you need:

MyEventRow=Application.WorkSheetFunction.Index(Mat ch
(TabName,Sheets("Lists")..Range("J3:J20"),0)),0)

-----Original 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
(Match,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


.

Glen,

You also need Application.WorkSheetFunction. in front of "Match" plus the parentheses after.

Regards,
Jim Cone
San Francisco, CA

"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

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