Run-time error 424 accessing a table

[JenniferMurphy]

I found code examples online for accessing various parts of a table using the ListObjects object. I am doing something wrong. The Set TableData statement gets a run-time error 424.

Sub WtdRtg()

Const rnTable As String = "Tbl"   'The name of the main table
Dim TblData
TblData = Range(rnTable).Value

Dim TableData As ListObject

'Run-time error 424
Set TableData = ActiveSheet.ListObjects(rnTable).Range.Select

Dim TableHdr As ListObject

Set TableHdr = ActiveSheet.ListObjects(rnTable).HeaderRowRange.Select

End Sub

 

[Alex Blakenburg]

You know you are going to be kicking yourself.
1) You can't have Set with Select
Set TableData = ActiveSheet.ListObjects(rnTable).Range.Select
2) By putting .Range on the end its a range and not a list object

Dim rngTableData As Range
Dim loTableData As ListObject

'Run-time error 424
Set rngTableData = ActiveSheet.ListObjects(rnTable).Range
Set loTableData = ActiveSheet.ListObjects(rnTable)

 

[JenniferMurphy]

You know you are going to be kicking yourself.

My rear end is too sore already. I'm into kicking the furniture. The cat will be next.

1) You can't have Set with Select
Set TableData = ActiveSheet.ListObjects(rnTable).Range.Select
2) By putting .Range on the end its a range and not a list object

Dim rngTableData As Range
Dim loTableData As ListObject

'Run-time error 424
Set rngTableData = ActiveSheet.ListObjects(rnTable).Range
Set loTableData = ActiveSheet.ListObjects(rnTable)

Here's my code:

Sub WtdRtg()

Const rnTable As String = "Tbl"   'The name of the main table

Dim rngTableData As Range
Set rngTableData = ActiveSheet.ListObjects(rnTable).Range

Dim loTableData As ListObject
Set loTableData = ActiveSheet.ListObjects(rnTable)

It doesn't get any errors, but these Immediate window results are puzzling.

The ubound function complains that rngTableData is not an array, but if I subscript it, I get the correct table contents. So is it an array or isn't it?

?ubound(rngtabledata,1)
'Error: expected array
?rngtabledata(1,1)
Product
?rngtabledata(1,2)
WtdRtg
?rngtabledata(1,3)
Price
?rngtabledata(2,1)
A
?rngtabledata(2,3)
 199.99

loTableData is even stranger.

?ubound(lotabledata,1)
'Error: expected array
?lotabledata
Tbl
?lotabledata(1,1)
'Error: Wrong number of arguments

 

[Alex Blakenburg]

Do you want to tell me what you are trying to do ?
1) Set rngTableData = ActiveSheet.ListObjects(rnTable).Range
a) Ubound applies to arrays this is not an array
b) .Range uses the whole table use DataBodyRange if you want to exclude the headings
c) ListObject is the Table you still need to tell it what part of the Table you want to access.

See if this helps:

Sub WtdRtg()

Const rnTable As String = "Tbl"   'The name of the main table
Dim TblData
TblData = Range(rnTable).Value

Dim rngTable As Range
Set rngTable = ActiveSheet.ListObjects(rnTable).Range
Debug.Print rngTable.Address, "rngTable (Range - Whole Table)"

Dim rngTableData As Range
Set rngTableData = ActiveSheet.ListObjects(rnTable).DataBodyRange
Debug.Print rngTableData.Address, "rngTableData (DataBody)"


Dim loTableData As ListObject
Set loTableData = ActiveSheet.ListObjects(rnTable)
Debug.Print loTableData.Range.Address, "loTableData.Range"
Debug.Print loTableData.DataBodyRange.Address, "loTableData.DataBodyRange"
Debug.Print loTableData.HeaderRowRange.Address, "loTableData.HeaderRowRange"

End Sub

 

[Alex Blakenburg]

I added 2 items later; which were:

TblData = Range(rnTable).Value
Debug.Print UBound(TblData), "TbldData a Variant array)"

'This version doesn't need you to reference the sheet, you just need the table name
Dim lo As ListObject
Set lo = Range(rnTable).ListObject
Debug.Print lo.Range.Address, "lo range based on name without sheet reference"
Debug.Print lo.DataBodyRange.Address, "lo DataBodyRange based on name without sheet reference"

 

[JenniferMurphy]

Do you want to tell me what you are trying to do ?

Sometimes I wonder if even I know.

I have a table in a worksheet. I need to calculate the Z Scores on several of the columns, then apply a weighting factor and sum the rows.

1) Set rngTableData = ActiveSheet.ListObjects(rnTable).Range
a) Ubound applies to arrays this is not an array

I think I see. It's a range, not an array. Ranges, like arrays, have dimensions, and so, can be subscripted. Right?

And I got this to work:

Dim rngPrice As range
Set rngPrice = ActiveSheet.ListObjects(rnTable).ListColumns(3).DataBodyRange

?application.worksheetfunction.sum(rngprice)
 839.98
?application.worksheetfunction.average(rngprice)
 209.995
?application.worksheetfunction.stdev_s(rngprice)
 31.3549576090714

So, if rngTableData & rngPrice are ranges (not arrays), does that mean that every time I access an element (cell), I am going back to the sheet? That is, the data is not in any VBA object, so VBA has to go back to the sheet to get the actual data. Is that correct?

I think I read once that if I am going to access very many cells, it's better to load the entire range into a VBA array. This is what I tried:

Dim Price As Variant
Price = rngPrice.Value

'It's a 4x1 array
?ubound(price,1)
 4
?ubound(price,2)
 1

'It has the correct values
?price(1,1)
 199.99
?price(2,1)
 249.99

'But how do I get this to work?
?application.worksheetfunction.sum(price)
 0

b) .Range uses the whole table use DataBodyRange if you want to exclude the headings
c) ListObject is the Table you still need to tell it what part of the Table you want to access.

I'm not sure how this is useful. It seems like I would still have to load it into an array to be able to use it.


See if this helps:

Sub WtdRtg()

Const rnTable As String = "Tbl"   'The name of the main table
Dim TblData
TblData = Range(rnTable).Value

Dim rngTable As Range
Set rngTable = ActiveSheet.ListObjects(rnTable).Range
Debug.Print rngTable.Address, "rngTable (Range - Whole Table)"

Dim rngTableData As Range
Set rngTableData = ActiveSheet.ListObjects(rnTable).DataBodyRange
Debug.Print rngTableData.Address, "rngTableData (DataBody)"


Dim loTableData As ListObject
Set loTableData = ActiveSheet.ListObjects(rnTable)
Debug.Print loTableData.Range.Address, "loTableData.Range"
Debug.Print loTableData.DataBodyRange.Address, "loTableData.DataBodyRange"
Debug.Print loTableData.HeaderRowRange.Address, "loTableData.HeaderRowRange"

End Sub

[/QUOTE]

Yes, that works perfectly. Thank you.

 

[Alex Blakenburg]

I think I see. It's a range, not an array. Ranges, like arrays, have dimensions, and so, can be subscripted. Right?

Yes for both ranges and arrays you can use something like ( i, j )

So, if rngTableData & rngPrice are ranges (not arrays), does that mean that every time I access an element (cell), I am going back to the sheet? That is, the data is not in any VBA object, so VBA has to go back to the sheet to get the actual data. Is that correct?

Yes if you don't load it into an array and refer to the range you are going back to the sheet.

'But how do I get this to work?

I might need to see your data because this worked fine for me.

Sub testSum()
    Const rnTable As String = "Tbl"   'The name of the main table
    Dim rngPrice As Range
    Set rngPrice = ActiveSheet.ListObjects(rnTable).ListColumns(3).DataBodyRange
    
    Dim Price As Variant
    Price = rngPrice.Value
    
    Debug.Print Application.WorksheetFunction.Sum(Price)

End Sub

 

[JenniferMurphy]

I might need to see your data because this worked fine for me.

Sub testSum()
    Const rnTable As String = "Tbl"   'The name of the main table
    Dim rngPrice As Range
    Set rngPrice = ActiveSheet.ListObjects(rnTable).ListColumns(3).DataBodyRange
   
    Dim Price As Variant
    Price = rngPrice.Value
   
    Debug.Print Application.WorksheetFunction.Sum(Price)

End Sub

Ok, here's the table:

Weighted Ratings.xlsm
	B	C	D	E	F	G
7	Product	WtdRtg	Price	ARtg	ARevs	Weight
8	A		$199.99	4.5	4,562	49 lbs
9	B		$249.99	4.6	75	65 lbs
10	C		$175.00	4.6	387	49 lbs
11	D		$215.00	4.4	522	86 lbs
WtdRtg

Here's my code:

Sub WtdRtg()

Const rnTable As String = "Tbl"   'The name of the main table

Dim rngTableData As Range
Set rngTableData = ActiveSheet.ListObjects(rnTable).Range

Dim rngPrice As Range
Set rngPrice = ActiveSheet.ListObjects(rnTable).ListColumns(3).DataBodyRange

Dim Price As Variant
Price = rngPrice.Value

End Sub

And here is the test data:

?ubound(price,1)
 4 
?ubound(price,2)
 1 
?price(1,1)
 199.99 
?price(2,1)
 249.99 
?Application.WorksheetFunction.Sum(Price)
 0 

?Application.WorksheetFunction.Sum(rngPrice)
 839.98

I get the right result with rngPrice, but not with Price.

It's past my bedtime. I'll resume this in the morning.

Thanks for all your help.

 

[Alex Blakenburg]

Very strange:

 

[Alex Blakenburg]

It was pretty late at your end. Is there any chance that you paused code before the line Price = rngPrice.Value ?
Try adding the Debug.Print line in the position shown below:

Dim Price As Variant
Price = rngPrice.Value

Debug.Print Application.WorksheetFunction.Sum(Price), "<- Application.WorksheetFunction.Sum(Price)"

End Sub