Loop through ListObjects

rahulbassi268

New Member
Joined
Sep 23, 2016
Messages
3
Hello Gurus,

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

Setup_Tbl.JPG


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

VBA 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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,588
Office Version
  1. 365
Platform
  1. Windows
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.

1616743919697.png


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.

VBA Code:
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
 
Solution

rahulbassi268

New Member
Joined
Sep 23, 2016
Messages
3
Thanks a lot Peter. It worked.
And thanks for both the advises. I'll follow them starting right now.

Thanks
Rahul
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,588
Office Version
  1. 365
Platform
  1. Windows
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’)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,321
Messages
5,641,513
Members
417,213
Latest member
wikk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top