Using Application.Match with a Structured Table

[Calgary_Neil]

Hi feeling dumb today,

I have a Structured Table called Experience with 2 columns Lvl, and Title and want find the CrtLvl (Current Level) of the CrtTitle (Current Title) using
CrtLvl = Application.Match( CrtTitle, Experience[Title], 0)

In this case the level = row number, but it could be expanded by wrapping this in a similar Index call.
What I'm not getting is how to wrap "Experience[Title]".
Being a onetime call I don't want to create to many variables, and being a Structured Table isn't the name globally predefined?

 

[Akuini]

Try:

CrtLvl = Application.Match(CrtTitle, Sheets("Sheet1").ListObjects("Experience").ListColumns("Title").Range, 0)

change Sheets("Sheet1") to suit.

 

[Alex Blakenburg]

being a Structured Table isn't the name globally predefined?

Yes, that is my understanding.

You can try:

    CrtLvl = Application.Match(CrtTitle, Range("Experience[Title]"), 0)

Note: this counts from starting from the first data row. @Akuini's code starts counting from the heading row. Change Akuini's code to the below to have it start at the 1st data row

CrtLvl = Application.Match(CrtTitle, Sheets("Sheet1").ListObjects("Experience").ListColumns("Title").DataBodyRange, 0)

 

[Calgary_Neil]

@Alex Blakenburg Uggh, I thought I had tried that. Thanks

Both I didn't like the sheets method because it reduces the effectiveness of the global variable by tying it to A sheet. It is interesting how the two different calls differ.
Thank you both for replying

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.
FYI: rngTitle.Parent.Name will get you the sheet name of the sheet the table is on.