Case reference to Range or Table

[Ktcubed66]

I would like to create the portion after the case below as a variable or better yet make it refer to the databodyrange of a table column.

select case name

case "Bob", "Fred", "Jimmy"
do something
else
do something else
end select

Can the {"Bob", "Fred", "Jimmy"} after the case somehow be a reference to a table list column.

I have a sheet in my workbook that contains lists of people the owner of the spreadsheet supervises. I want to select his people out for the do something and eliminate the people who are not his (do something else). I want the owner of the spreadsheet to be able to easily (without going into the code), add and delete members of his team.

So he has a table (named etc.) that he can just add people to and take them away and the case uses his current list.

Name Code
Bob BT011
Fred FZ012
Jimmy JT102

case (appears in "NAME")
do something...

[AlphaFrog]

Maybe something like this....

Please Login or Register  to view this content.

[Ktcubed66]

I am trying to avoid the range reference. There are multiple ltablesbon this one sheet that I have successfully used for a variety of different automations. However he needs the flexibility to move the tables around and adjust the size.

However I tried an if not is error similar to that and it failed me. Didn’t think about the isnumeric. Will try that with my named reference.

[AlphaFrog]

Here is a guide to referencing parts of a table aka ListObjects

https://www.thespreadsheetguru.com/b...t-excel-tables

Please Login or Register  to view this content.