Cannot use ListObject object - subscript out of range

[tonyr63]

I am having trouble accessing the Excel ListObject object using VBA code where I keep getting Subscript out of range errors as if there are no recognised lists on the spreadsheet where I try to run the code. I can see the list and I know the sheet is active when I run the test below. I was of the expectation that once there was a header row and record rows underneath Excel automatically recognises it as a list or at least that is how it works when using the manual interface Data Sort and Filter functionality through the Excel UI. Do I need to do something different in VBA to define a list? When I click a cell inside the list the list Tab is NOT activiates so what turned this on?</SPAN>

To bring things back to basics I have tested with this code running on a new workbook which has a basic list of 3 rows and 3 columns.</SPAN>

Sub ActivateHeaderRow1()</SPAN>

Dim wrksht As Worksheet</SPAN>
Dim objList As ListObject</SPAN>
Dim objListRng As Range</SPAN>

Set wrksht = ActiveWorkbook.ActiveSheet</SPAN>
Set objList = wrksht.ListObjects(1)</SPAN>
Set objListRng = objList.HeaderRowRange</SPAN>

objListRng.Activate</SPAN>

End Sub</SPAN>


This is my basic list.</SPAN>

date time hours</SPAN>
1/05/2014 2:15 5</SPAN>
2/05/2014 3:15 4</SPAN>
3/05/2014 4:15 6</SPAN>

It fails on this line:</SPAN>
Set objListRng = objList.HeaderRowRange

What is the VBA code to make a list act like a list?

Any suggestions appreciated.
</SPAN>

 

[Norie]

To create a table/list you need to select the data and goto Insert>Table.

 

[tonyr63]

To create a table/list you need to select the data and goto Insert>Table.

Thanks for your suggestion Norie. It would appear that this step is missing from countless code snippets I have found including on Microsoft sites, which fail when pasted into a new test workbook.

I presume that I need to use the add method something like this:

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$6:$AP$12"), , xlYes).Name = "myTable1?

Thanks for your assistance


Kind Regards


Tony

 

[Norie]

I think the code you've seen is probably assuming you already have a table/list setup.

What are you going to do with the data?

Whatever it is, are you sure you need to use ListObject?

 

[tonyr63]

Your correct the examples assume that you have a table / list setup as I suspected in my original post "Do I need to do something different in <acronym title="visual basic for applications">VBA</acronym> to define a list? Because Excel automatically recognises a list when using the manual interface such as Data Sort and Filter functionality and most of the VBA examples excluded the code to setup the Table you can see I could be confused.

 

[tonyr63]

What I have been working on is importing weekly timesheet data provided in CSV files into Excel consolidating them into accumulative tables. The timesheets sometimes come with the list headings in a different order, although the correct fields, and if the person running the VBA code does not notice the changed order the consolidated data can be distorted. I wrote a test header script that compares the header range of the destination list to the header range of the CSV list for import. It does this cell by cell using Cells collection and named ranges and works perfectly. I have the test header sub called by the import list sub which passes parameters for testing and returns true of false if it finds a difference. It also highlights using colour background the fields that are found different.


However I wanted to make the test header sub procedure more flexible so that it could accept any header for testing not just the 11 fields in my example. One challenge I face is that I have no guarantee that the header of the CSV list for import will always be on the first row. It could have all the correct fields in the correct order but not have the header on row 2 as it currently has. Row one of the import sheet has Text like “Timesheet for period …” so that messes up using the list object as it detects lists but will default including the 1^st row even though there are only data in the 1^st cell of row one and the rest of the rows have all 11 fields of the respective row populated. I guess I was looking for an easier way of finding the square range of the list but ignoring data below or above the list. You can imagine that I face the same problem with Current Region or Used Range so I am left with using the 1^st field of the Compare Header Range to match with the corresponding location of the Import List Header row.



I would be interested in discovering how other people have handled searching for headers in lists for comparison to expected headers field lists as a validation check before proceeding with data consolidation actions. I was hoping that the List Object would automatically find the dimension of a list on a sheet page and not require you to map out its range dimension as I have discovered you have to do making it in my situation not much more useful than conventional range dimensioning techniques.