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
7
  • Can you clarify "still not getting it"? Is it throwing an error? If so, where? What? Commented Nov 23, 2017 at 9:15
  • Sorry about that. I’m getting an error on the Me.tbItem.Value = Commented Nov 23, 2017 at 9:37
  • What error message? Commented Nov 23, 2017 at 9: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 Commented Nov 23, 2017 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. Commented Nov 23, 2017 at 16:54

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.