Index(Match...

[GMet]

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

 

[K Dales]

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(Match
(TabName,Sheets("Lists")..Range("J3:J20"),0)),0)

 

[Jim Cone]

Glen,

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

Regards,
Jim Cone
San Francisco, CA

 

[Bob Phillips]

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