0

This sub is supposed to find the row that matches the name in the userform combobox. Then pull in data from other columns in that row. Trying to do it with structured referencing after reading the posts below but still not getting it.

How do i loop an excel 2010 table by using his name & column reference?.

Looping through all rows in a table column, Excel-VBA

Edited to change row to row.Value and deleted Set row

Here are a few different attempts.
I get an Object required error with the first one at the If Intersect line

Dim tbl As ListObject
Dim row As Range

Set tbl = ActiveSheet.ListObjects("List")

For Each row In [List[Name]].Rows

If Intersect(row.Value, tbl.ListColumns("Name").Range).Value = (Me.cbName) Then

With tbl.DataBodyRange
Me.tbItem.Value = Intersect(row.Value, .ListColumns("Item").Range).Value
'rest of code
End With
End If
Next

Another try
I get an Invalid procedure call or argument error at the If Range line

Dim tbl As ListObject
Dim row As Range

Set tbl = ActiveSheet.ListObjects("List")

For Each row In [List[Name]].Rows

If Range("List[Name]")(row.Value) = Me.cbName Then

Me.tbItem.Value = Range("List[Item]")(row.Value).Value
'rest of code

End If
Next
bigbucky
  • 407
  • 6
  • 20
  • Can you clarify "still not getting it"? Is it throwing an error? If so, where? What? – jeffreyweir Nov 23 '17 at 09:15
  • Sorry about that. I’m getting an error on the Me.tbItem.Value = – bigbucky Nov 23 '17 at 09:37
  • What error message? – jeffreyweir Nov 23 '17 at 09:39
  • 1. You set up the variable `row` but then use it as a control variable of the for loop, so `tbl.Range.EntireRow` is never being used. 2. `row` in the for loop is already a range object. Access it directly like `row.Value` or `row.Cells(1,1).Value` – newacc2240 Nov 23 '17 at 10:59
  • Thanks @newacc2240, I'll try changing to row.Value. I thought I needed to set the variable before the For loop that's why I defined it. – bigbucky Nov 23 '17 at 16:54
  • Sorry @jeffreyweir I'll add the specific errors – bigbucky Nov 23 '17 at 16:55
  • You need only to tell excel what type the variable is. No need to set it to a certain object since it will be used as a control variable, will be assigned an object or a value by the `For Each` statement automatically. – newacc2240 Nov 24 '17 at 02:24

0 Answers0