Find a cell in a table column that matches my criteria?

[rtemen]

I am using Excel 2013. I have a listobject excel table called Assembly I need to find the cell that contains "asmb1" in column "Item Name"
Here is what I have that does work:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">[COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ItemRows [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Integer[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] AssemblyTable [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Worksheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Assembly"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]ListObjects[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Assembly"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
ItemRows [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] AssemblyTable[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]ListColumns[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Item Name"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]DataBodyRange[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Rows[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Count
[/FONT][/COLOR]</code>

Here ItemRows gets the proper number of rows in the table.

Now I try to do the search and it fails:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">[COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ItemRows [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Integer[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] AssemblyTable [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Worksheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Assembly"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]ListObjects[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Assembly"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
ItemRows [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] AssemblyTable[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]ListColumns[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Item Name"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]DataBodyRange[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Find[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"asmb1"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])
[/FONT][/COLOR]</code>

This fails with the following message:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Run-time error'91': Object variable or With block variable not set

</code>Thanks for any ideas.
Rich

 

[mart37]

Re: How to find a cell in a table column that matches my criteria?

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; line-height: inherit; border: 0px; font-variant: inherit; font-stretch: inherit; vertical-align: baseline; white-space: inherit;">AssemblyTable.ListColumns("Item Name").DataBodyRange.Find("asmb1").Row</code>

 

[rtemen]

Re: How to find a cell in a table column that matches my criteria?

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; line-height: inherit; border: 0px; font-variant: inherit; font-stretch: inherit; vertical-align: baseline; white-space: inherit;">AssemblyTable.ListColumns("Item Name").DataBodyRange.Find("asmb1").Row</code>

I get the same error if I add the Row or Rows as you suggested.

 

[mart37]

Re: How to find a cell in a table column that matches my criteria?

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; line-height: inherit; border: 0px; font-variant: inherit; font-stretch: inherit; vertical-align: baseline; white-space: inherit;">ItemRows = AssemblyTable.ListColumns("Item Name").DataBodyRange.Find("asmb1").Row</code>

 

[rtemen]

Re: How to find a cell in a table column that matches my criteria?

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; line-height: inherit; border: 0px; font-variant: inherit; font-stretch: inherit; vertical-align: baseline; white-space: inherit;">ItemRows = AssemblyTable.ListColumns("Item Name").DataBodyRange.Find("asmb1").Row</code>

You sent the same message.

Rich

 

[mart37]

Re: How to find a cell in a table column that matches my criteria?

Maybe you forgot: Dim AssemblyTable As ListObject
I think: Find("asmb1") Does that string exists?

Set f = AssemblyTable.ListColumns("Item Name").DataBodyRange.Find("asmb1")If f Is Nothing Then Exit Sub
ItemRows = f.Row

 

[mart37]

Re: How to find a cell in a table column that matches my criteria?

Set f = AssemblyTable.ListColumns("Item Name").DataBodyRange.Find("asmb1")
If f Is Nothing Then Exit Sub
ItemRows = f.Row

 

[rtemen]

Re: How to find a cell in a table column that matches my criteria?

Set f = AssemblyTable.ListColumns("Item Name").DataBodyRange.Find("asmb1")
If f Is Nothing Then Exit Sub
ItemRows = f.Row

Yes, AssemblyTable is types as a ListObject, as the example code at the top of my post is working.

I get this same error no matter how I Dim ItemRows, or whether or not I = the function to ItemRows or if I Set ItemRows.

There must be some sort of magic going on in the Find system.

Rich