Index Match within a table

Greetings All,

I am going to this as a last resort. Most of the time google helps me out.

So, I am trying to look up a value within a table using index/match via vba code. The table may move around, or expand, so i want the table range to be dynamic.

I am selecting a Purchase Order code in the list textbox and I want another textbox to display a description of that code. I have a table of PO CODES/DESCRIPTIONS somewhere in the workbook that I want to pull the info from.

This is what I am trying to do:

Please Login or Register  to view this content.

I have tried to just indicate the static range, and it still gives me 1004. Not sure what to do here anymore. Could anyone provide a working example of a code that does index/match from a table?

Thank you.

I really wish I could say it worked, but I am still having same error 1004 on the same line. TableRangeCodes is correctly identifies the range that I want to look at as $D$3:$D$8 when I use "msgbox TableRangeCodes.address". Could there be a syntax error in how I reference the range?

Originally Posted by Sergey1989

Could there be a syntax error in how I reference the range?

Hi,

yes 'cause of

• you forgot to declare the type of the TableRangeCodes variable
  as you can check during the execution in the Locals VBE window …
  
  
• As a ListColum object does not have a DataBodyRange property !
  Just open the VBA inner help of ListColumn object to see its existing properties …

you forgot to declare the type of the TableRangeCodes variable
as you can check during the execution in the Locals VBE window …

As a ListColum object does not have a DataBodyRange property !
Just open the VBA inner help of ListColumn object to see its existing properties …

I thought that I declared "TableRangeCodes" as range. I will try to look into that.

Is MatchResult number or string.
If it is string, then use " .find"

MatchResult should be an integer, right? I am looking for row number after all. Could you post an example?

Can you post a copy of the workbook for review?

I have isolated this example and put it into test workbook. Enter numbers 1-6 in the first cell.

Could you please explain what each argument in that find command means?