syntax to assign table cell values to variables... losing my mind!

[jbaich]

Hi all, I have been reading the internet all day trying to figure out how to properly reference tables and i have tried everything i could find but so far am at a loss...

I've recently begun using Power Query a lot, so I figured I'd better learn how to refer to tables in VBA... it seems like it should be super simple and intuitive, and maybe for others it is... but I am at my wits end trying to understand how to correctly loop through a table and assign the value of the cell in the active row of the desired column. God I hope someone out there can put this into words that will make sense... I swear i've tried every combination of every post that comes up in the first 5 google search pages for every search combination of VBA, Table, Variable, Excel, Syntax, Loop, Reference, etc....

myTable

Primary ID (PrID variable)	Name
1564654	Jim
7578445	Jane
1983587	Elvis

<tbody>
</tbody>

Sub Test()


Dim myTable As ListObject
Dim Master As Workbook
Dim Listsht As Worksheets


Set Master = ThisWorkbook
Set Listsht = Master.Sheets("Summary List")
Set myTable = Listsht.ListObjects("Summary_Listing")


  For Each rw In myTable.ListRows  '//Loop through each row\\


    PrID = ???????????????? I have tried everything here and keep getting object missmatch or some other  error, wrong number of arguments, invalid property assignment... the list goes on...

shouldn't it be something simple like[INDENT]PrID = myTable([@[Primary ID]])  or 
PrID = myTable("[Primary ID](rw)")

[/INDENT]
Next rw


End Sub

So first loop the variable PrID would be 1564654, 2nd iteration would be 7578445, 3rd would be 1983587 and so on...

Eventually I'd like to be able to use the variable for things like

ApplicationFunction.Match(PrID, Master.sheets("sheet1").Range("A:A"), 0)

etc...

Please, please, please put me out of my misery!

Thanks in advance

 

[Norie]

What you probably want to do is loop through the rows in the Primary ID column.

In the following code that's what's happening in the first loop, the second loop is through the ListRows and, perhaps, shows how it's not as intuitive as looping through the column.

Sub Test()
Dim Master As Workbook
Dim Listsht As Worksheet
Dim prID As Range
Dim myTable As ListObject
Dim rw As ListRow

    Set Master = ThisWorkbook
    Set Listsht = Master.Sheets("Summary List")
    Set myTable = Listsht.ListObjects("Summary_Listing")

    For Each prID In myTable.ListColumns("Primary ID").DataBodyRange
        MsgBox prID.Value
    Next prID


    For Each rw In myTable.ListRows
        MsgBox rw.Range(1, 1).Value
    Next rw
    
End Sub

 

[Eric W]

Another variation, which avoids hardcoding column indexes:

Sub Test()

Dim myTable As ListObject
Dim Master As Workbook
Dim Listsht As Worksheet
Dim r As Long, IDix As Long

Set Master = ThisWorkbook
Set Listsht = Master.Sheets("Summary List")
Set myTable = Listsht.ListObjects("Summary_Listing")


  IDix = myTable.ListColumns("Primary ID").Index

  For r = 1 To myTable.ListRows.Count                   '//Loop through each row\\
    prid = myTable.DataBodyRange(r, IDix)
    MsgBox prid
  Next r

End Sub

 

[jbaich]

Thanks so much! so just to clarify... in most instances, in order to utilize column names in your code, you either need to use the rather long .ListColumns("Column Name").DataBodyRange syntax, or define your columns as variables? nothing short and sweet?

Many thanks!

 

[Eric W]

I personally have about hit my knowledge limit on using table references in VBA! Most of what I know I gleaned from here:

https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables


Norie provided a few options, and I another, but if there's still another way to do it (and there may be), I don't know what it is. I'd have to do some more digging/experimenting. If I come up with something, I'll let you know.