Using a table like an object in Excel -

i created table in excel, example:

                            b     1    fruit       price ($)     2    apple          5     3    banana         3     4    orange         4     5    lemon          2     6    pineapple      4      

the name table fruits. how can use table obejct in vba code?

for example use in match function:

result = worksheetfunction.match("banana", table("fruits").column("fruit"), 0) 

and result 2.

---

problem solved - 1 observation, better use application.match instead of worksheetfunction.match. worksheetfunction.match returns #value!, when value not found. different application.match returns #n/a.

you need make special construction of range reference appropriate column within table. best if present improving code:

result = worksheetfunction.match("banana", range("fruits[fruit]"), 0) 

assuming fruits range name table. result result = 2