Loop through ListObjects

[rahulbassi268]

Hello Gurus,

Kindly help me to understand where am I going wrong here.

I have a listobject (Table) with 2 columns. I want to get table rownumbers (row number in dataBodyRange; Not worksheet row number) in array where DataType(2nd column) is "Date"
But, my loop in below code is always stuck at row number 3 i.e. Hire_Date.
If databodyrange row number is not possible, then corresponsing value in first column would be fine.

All I need is the result in an array.

Below is my code

Dim SetupListObj As ListObject
Dim LocateDate As Range
Dim date_fields() As Variant

Set SetupListObj = ActiveSheet.ListObjects("MyTable")
Set LocateDate = SetupListObj.ListColumns(2).DataBodyRange
    For Each setup_cell In LocateDate
        MsgBox setup_cell.Value
        If setup_cell.Value = "Date" Then
'            setup_row = SetupListObj.ListColumns(2).DataBodyRange.Range(setup_cell).Row
            MsgBox "setup_row " & SetupListObj.ListColumns(2).DataBodyRange.Row & " - " & SetupListObj.ListColumns(1).DataBodyRange(setup_row, 1).Value
            count = count + 1
            ReDim Preserve date_fields(count)

 '           date_fields(count) = SetupListObj.ListColumns(1).DataBodyRange(setup_row, 1).Value
            date_fields(count) = setup_row
        End If
    Next setup_cell

 

[Peter_SSs]

You have not declared the variable setup_row nor given it a value anywhere in the code.


I would strongly recommend forcing yourself to declare all variables as it will automatically pick up many problems like this.

Also suggest not using special words that vba already uses as part of its language as variable or procedure names (eg Count)

See if this is doing what you want.

Sub Test()
  Dim SetupListObj As ListObject
  Dim LocateDate As Range
  Dim date_fields() As Variant
  Dim setup_cell As Range
  Dim counter As Long
  Dim setup_row As Long


  Set SetupListObj = ActiveSheet.ListObjects("MyTable")
  Set LocateDate = SetupListObj.ListColumns(2).DataBodyRange
    For Each setup_cell In LocateDate
        setup_row = setup_row + 1
        MsgBox setup_cell.Value
        If setup_cell.Value = "Date" Then
            MsgBox "setup_row " & setup_row & " - " & SetupListObj.ListColumns(1).DataBodyRange.Cells(setup_row, 1).Value
            counter = counter + 1
            ReDim Preserve date_fields(counter)
            date_fields(counter) = setup_row
        End If
    Next setup_cell
End Sub

 

[rahulbassi268]

Thanks a lot Peter. It worked.
And thanks for both the advises. I'll follow them starting right now.

Thanks
Rahul

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

BTW, I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)