# Run-time error 424 accessing a table



## JenniferMurphy (Jan 6, 2023)

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 (Jan 6, 2023)

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 *rng*TableData As Range
Dim loTableData As ListObject

'Run-time error 424
Set *rng*TableData = ActiveSheet.ListObjects(rnTable).*Range*
Set *lo*TableData = ActiveSheet.ListObjects(rnTable)
```


----------



## JenniferMurphy (Jan 6, 2023)

Alex Blakenburg said:


> 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
> 
> ...



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 (Jan 6, 2023)

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 (Jan 6, 2023)

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 (Jan 6, 2023)

Alex Blakenburg said:


> 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 (Jan 6, 2023)

JenniferMurphy said:


> 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 )  


JenniferMurphy said:


> 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.


JenniferMurphy said:


> '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 (Jan 6, 2023)

Alex Blakenburg said:


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



Ok, here's the table:

Weighted Ratings.xlsmBCDEFG7ProductWtdRtgPriceARtgARevsWeight8A$199.994.54,56249 lbs9B$249.994.67565 lbs10C$175.004.638749 lbs11D$215.004.452286 lbsWtdRtg

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 (Jan 6, 2023)

Very strange:


----------



## Alex Blakenburg (Jan 6, 2023)

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
```


----------



## JenniferMurphy (Jan 6, 2023)

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
```


----------



## JenniferMurphy (Jan 6, 2023)

Here's a more complete picture.

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 rngPrices As Range
Set rngPrices = ActiveSheet.ListObjects(rnTable).ListColumns(3).DataBodyRange
Debug.Print "rngPrices = " & rngPrices(1, 1) & " " & rngPrices(2, 1) _
            & " " & rngPrices(3, 1) & " " & rngPrices(4, 1)
Dim PriceSumRng As Double
PriceSumRng = Application.WorksheetFunction.Sum(rngPrices)
Debug.Print "PriceSumRng = " & PriceSumRng

Debug.Print ""

Dim arrPrices As Variant
arrPrices = rngPrices.Value
Debug.Print "arrPrices = " & arrPrices(1, 1) & " " & arrPrices(2, 1) _
            & " " & arrPrices(3, 1) & " " & arrPrices(4, 1)
Dim PriceSumArr As Double
PriceSumArr = Application.WorksheetFunction.Sum(arrPrices)
Debug.Print "PriceSumArr = " & PriceSumArr

End Sub
```

Here's the immediate window:


```
rngPrices = 199.99 249.99 175 215
PriceSumRng = 839.98

arrPrices = 199.99 249.99 175 215
PriceSumArr = 0
```


----------



## Alex Blakenburg (Jan 6, 2023)

Love your work.
It doesn't happen at my end using your XL2BB but have discovered that XL2BB loses the number formatting.

Change the line below to be .Value2

```
arrPrices = rngPrices.*Value2*
```

Your numbers are formatted as Currency and Value2 reads just the underlying number while Value reads Dates and Currency differently.


----------



## JenniferMurphy (Saturday at 1:13 AM)

Alex Blakenburg said:


> Love your work.



Thanks, but you are the one with the beautiful work. You are one of several on this board who have saved me at least days of work and probably weeks. Without this board, Excel would be unusable for me for anything beyond simple tables.



> It doesn't happen at my end using your XL2BB but have discovered that XL2BB loses the number formatting.
> 
> Change the line below to be .Value2
> 
> ...



That did it. The code now works perfectly.

Thank you very much! 👍👍👏👏🥰

I wondered what .Value2 was for, but was too lazy to look it up. I thonk I will now use it preffy much all the time except for when I might need the formatti g, whoich I cannot imagine when in VBA code.

I have made this the solution because it is more complete.

I do have one final question:

It is true that if I am going to do a lot of processing on a range of data that it is more efficient to load it into a VBA array that keep accessing it in the sheet via a range?


----------



## Alex Blakenburg (Saturday at 1:59 AM)

JenniferMurphy said:


> is true that if I am going to do a lot of processing on a range of data that it is more efficient to load it into a VBA array that keep accessing it in the sheet via a range?



Definitely. It is significantly faster if you can minimise the number of individual reads and writes to the spreadsheet. Eventhough you are still reading all the cells and then writing all the cells, doing it as a single read into an array and single write back to the spreadsheet is much faster than doing individual reads and writes, not to mention that any looping and calculations are now in memory.

If you haven't done much with arrays, below is the syntax for writing an single column from your array back to the spreadsheet.


```
Sub WriteBackSingleColumn()

    Dim arr As Variant
    Dim rng As Range
    Dim ArrColToWriteBack As Long
    
    arr = Range("A1").CurrentRegion     ' Test Data has 3 columns
    
    Set rng = Range("G1")
    ArrColToWriteBack = 2
    rng.Resize(UBound(arr), 1).Value2 = Application.Index(arr, 0, ArrColToWriteBack)  '    It will work without specifying the .Value2
    
End Sub
```


----------



## JenniferMurphy (Saturday at 2:03 AM)

Alex Blakenburg said:


> Definitely. It is significantly faster if you can minimise the number of individual reads and writes to the spreadsheet. Eventhough you are still reading all the cells and then writing all the cells, doing it as a single read into an array and single write back to the spreadsheet is much faster than doing individual reads and writes, not to mention that any looping and calculations are now in memory.


That's what I thought. Thanks.



> If you haven't done much with arrays, below is the syntax for writing an single column from your array back to the spreadsheet.
> 
> ```
> Sub WriteBackSingleColumn()
> ...


You are one jump ahead of me -- probably not a difficult task. 🤔😥 That is the next thing I was going to need to do. Thanks!


----------



## JenniferMurphy (Saturday at 4:13 AM)

Alex Blakenburg said:


> If you haven't done much with arrays, below is the syntax for writing an single column from your array back to the spreadsheet.



I will be writing back column 2 of the body of the table. I couldn't follow your code example, so I fiddled around and came up with this, which works. The last 4 statements write those test values to column 2 of the table. Let me know if you have any corrections.

```
Sub WtdRtg()
Const MyName As String = "WtdRtg"   'The name of this macro for error messages

' Some constants
Const MinRows As Long = 1   'Table must have at least 1 row
Const MinCols As Long = 3   'Table must have at least 3 columns (name, wtdrtg, attribute)

' Get the name of the table
Const rnTableName As String = "TableName" 'The name of the cell with the name of the table
Dim rnTable As String                     'The name of the table
rnTable = Range(rnTableName).Value2       '.Load the table name

' Load the headers into one array and the body into another
Dim TableHdr As Variant       'The table header row
TableHdr = ActiveSheet.ListObjects(rnTable).HeaderRowRange.Value2
Dim TableData As Variant      'The table data (body)
TableData = ActiveSheet.ListObjects(rnTable).DataBodyRange.Value2

'Do some validity checking
Dim TblRows As Long: TblRows = UBound(TableData, 1) 'Get the number of rows
If TblRows < MinRows Then
  MsgBox "Table has less than " & MinRows & " rows": Exit Sub: End If
Dim TblCols As Long: TblCols = UBound(TableData, 2) 'Get the number of columns
If TblCols < MinCols Then
  MsgBox "Table has less than " & MinCols & " columns": Exit Sub: End If

Dim rngTableData As Range
Set rngTableData = ActiveSheet.ListObjects(rnTable).DataBodyRange
rngTableData(1, 2).Value = 1  'Test data
rngTableData(2, 2).Value = 2  'Test data
rngTableData(3, 2).Value = 3  'Test data
rngTableData(4, 2).Value = 4  'Test data
```


----------



## Alex Blakenburg (Saturday at 8:28 AM)

Assuming the code was meant to demonstrate the use of an array, doesn't really do that at all.

The data is being written using direct sheet access and not an array. I deleted these lines which are the write lines:

```
'Dim rngTableData As Range
'Set rngTableData = ActiveSheet.ListObjects(rnTable).DataBodyRange
'rngTableData(1, 2).Value = 1  'Test data
'rngTableData(2, 2).Value = 2  'Test data
'rngTableData(3, 2).Value = 3  'Test data
'rngTableData(4, 2).Value = 4  'Test data
```

Personal preference but I `Set loTable = Range(rnTable).ListObject` rather than continually referencing ActiveSheet.ListObject.
Also personal preference, I added an arr prefix to your 2 array names to help me follow the code.

I updated column 2 of your array using a loop using the same test data 1 to 4.
Then wrote out the array back to Column 2 of the table.


```
Sub WtdRtg_Array()
Const MyName As String = "WtdRtg"   'The name of this macro for error messages

' Some constants
Const MinRows As Long = 1   'Table must have at least 1 row
Const MinCols As Long = 3   'Table must have at least 3 columns (name, wtdrtg, attribute)

' Get the name of the table
Const rnTableName As String = "TableName" 'The name of the cell with the name of the table
Dim rnTable As String                     'The name of the table
rnTable = Range(rnTableName).Value2       '.Load the table name

' XXX Set the ListObject variable and replace ActiveSheet.ListObject in the rest of the code
Dim loTable As ListObject
Set *loTable *= Range(rnTable).ListObject

' Load the headers into one array and the body into another
Dim arrTableHdr As Variant       'The table header row
arrTableHdr = *loTable*.HeaderRowRange.Value2
Dim arrTableData As Variant      'The table data (body)
arrTableData = *loTable*.DataBodyRange.Value2

'Do some validity checking
Dim TblRows As Long: TblRows = UBound(arrTableData, 1) 'Get the number of rows
If TblRows < MinRows Then
  MsgBox "Table has less than " & MinRows & " rows": Exit Sub: End If
Dim TblCols As Long: TblCols = UBound(arrTableData, 2) 'Get the number of columns
If TblCols < MinCols Then
  MsgBox "Table has less than " & MinCols & " columns": Exit Sub: End If

*' Test data*
*' Load test data (sequential no) into array)
Dim i As Long
For i = 1 To UBound(arrTableData, 1)
    arrTableData(i, 2) = i
Next i*

' Writing out just Column 2 of the array
*loTable.ListColumns(arrTableHdr(1, 2)).DataBodyRange.Resize(UBound(arrTableData, 1)) = Application.Index(arrTableData, 0, 2)*

' Alternative - Comment out previous line and uncomment line below
' Writing out the whole array (all columns)
' *loTable.DataBodyRange.Resize(UBound(arrTableData, 1), UBound(arrTableData, 2)) = arrTableData*

End Sub
```


----------



## JenniferMurphy (Sunday at 2:56 AM)

Alex Blakenburg said:


> Assuming the code was meant to demonstrate the use of an array, doesn't really do that at all.
> 
> The data is being written using direct sheet access and not an array. I deleted these lines which are the write lines:
> 
> ...


😄🤔😂 It was only meant to show that I was able to get the data into an array. I knew that the 4 lines above were making 4 separate puts back into the sheet. They were just a quick and dirty way to show that the data would actually get written out to the right cells. My plan was to replace them with something like what you showed. But I would *never* have come up with anything as elegant as what you posted. For that, I cannot thank you enough. I wish I could buy you a beer or dinner or something. I have learned more from this thread than anything I can recall from the last 10 years.

Thank you, thank you, thank you.

PS: I have adopted all of your other suggestions. 

PPS: This is a such a valuable post, that I am going to make it the solution. I hope that is not too confusing for other readers.


----------



## JenniferMurphy (Sunday at 5:29 AM)

Alex Blakenburg said:


> Assuming the code was meant to demonstrate the use of an array, doesn't really do that at all.
> . . .


Ok. I now have a working macro. I think I did it right.


```
Sub WtdRtg()
Const MyName As String = "WtdRtg"   'The name of this macro for error messages

' Some constants
Const MinRows As Long = 1   'Table must have at least 1 row
Const MinCols As Long = 3   'Table must have at least 3 columns (name, wtdrtg, 1 attribute)
Const WRCol As Long = 2     'The weighted rating column

' Worker variables
Dim iCol As Long    'Loop index
Dim iRow As Long    'Loop index

' Get the name of the table
Const rnTableName As String = "TableName" 'The name of the cell with the name of the table
Dim rnTable As String                     'The name of the table
rnTable = Range(rnTableName).Value2       '.Load the table name

' Define a ListObject variable. It will replace ActiveSheet.ListObject in the rest of the code
Dim loTable As ListObject
Set loTable = Range(rnTable).ListObject

' Load the headers into one array and the body into another
Dim arrTableHdr As Variant       'The table header row
arrTableHdr = loTable.HeaderRowRange.Value2
Dim arrTableData As Variant      'The table data (body)
arrTableData = loTable.DataBodyRange.Value2

'Do some validity checking
Dim NumRows As Long: NumRows = UBound(arrTableData, 1) 'Get the number of rows
If NumRows < MinRows Then
  MsgBox "Table has less than " & MinRows & " rows": Exit Sub: End If
Dim NumCols As Long: NumCols = UBound(arrTableData, 2) 'Get the number of columns
If NumCols < MinCols Then
  MsgBox "Table has less than " & MinCols & " columns": Exit Sub: End If

'Calculate the means and std deviations (just the data columns)
Dim arrMeans() As Double:     ReDim arrMeans(NumCols)   'The means for each data column
Dim arrStdDevs() As Double:   ReDim arrStdDevs(NumCols) 'The std devs for each data column
Dim arrNextCol() As Variant:  ReDim arrNextCol(NumRows) 'The property data in the next column
For iRow = 1 To NumRows         'Zero the WtdRtgs
  arrTableData(iRow, WRCol) = 0
Next iRow
For iCol = MinCols To NumCols
  With Application.WorksheetFunction
    arrNextCol = .Index(arrTableData, 0, iCol)
    arrMeans(iCol) = .Average(arrNextCol)
    arrStdDevs(iCol) = .StDev_S(arrNextCol)
  End With
  For iRow = 1 To NumRows
    arrTableData(iRow, WRCol) = arrTableData(iRow, WRCol) + _
      ((arrTableData(iRow, iCol) - arrMeans(iCol)) / arrStdDevs(iCol))
  Next iRow
Next iCol

' Write out just weighted ratings column (2) of the array
loTable.ListColumns(arrTableHdr(1, WRCol)).DataBodyRange.Resize(UBound(arrTableData, 1)) _
       = Application.Index(arrTableData, 0, WRCol)

End Sub
```
It operates on this sheet table. I have not yet implemented the Order or Weight rows (7 & 8).

Weighted Ratings.xlsmBCDEFG2TblMainTable name34Mean$209.9954.525001386.5062.25005Std Dev$31.3550.095742125.2617.538167OrderHiLoHiLoHiLoHiLo8Weights11119ProductWtdRtgPriceARtgARevsWeight10A+0.15847$199.994.54,56249 lbs11B+1.59861$249.994.67565 lbs12C-1.55854$175.004.638749 lbs13D-0.19853$215.004.452286 lbs14Means$210.004.51,38762 lbs1516ProductWtdRtgPriceARtgARevsWeight17A+0.15847-0.31909-0.26112+1.49417-0.7555018B+1.59860+1.27555+0.78335-0.61710+0.1568019C-1.55854-1.11609+0.78335-0.47030-0.7555020D-0.19853+0.15962-1.30558-0.40677+1.3542021Z Sum=0.00000-0.00000=0.00000=0.00000WtdRtgCell FormulasRangeFormulaD4D4=AVERAGE(TblMain[Price])E4E4=AVERAGE(TblMain[ARtg])F4F4=AVERAGE(TblMain[ARevs])G4G4=AVERAGE(TblMain[Weight])D5D5=STDEV.S(TblMain[Price])E5E5=STDEV.S(TblMain[ARtg])F5F5=STDEV.S(TblMain[ARevs])G5G5=STDEV.S(TblMain[Weight])D14D14=SUBTOTAL(101,[Price])E14E14=SUBTOTAL(101,[ARtg])F14F14=SUBTOTAL(101,[ARevs])G14G14=SUBTOTAL(101,[Weight])D17:G20D17=ZScore(D10,D$4,D$5,D$7)D21:G21D21=SUM(D17:D20)C17:C20C17=SUMPRODUCT(D17:G17,D$8:G$8)


----------



## Alex Blakenburg (Sunday at 6:02 AM)

Re: Your feedback post.
Wow that a big call 😉 but thank you, I really appreciate the sentiment. 😊



JenniferMurphy said:


> Ok. I now have a working macro. I think I did it right.


Hopefully I can leave it up to you to validate the output but your use of arrays looks fine to me.


----------



## JenniferMurphy (Jan 6, 2023)

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
```


----------



## JenniferMurphy (Sunday at 12:09 PM)

Alex Blakenburg said:


> Re: Your feedback post.
> Wow that a big call 😉 but thank you, I really appreciate the sentiment. 😊


You absolutely deserve it.



> Hopefully I can leave it up to you to validate the output but your use of arrays looks fine to me.


The pseudo table in 16:21 is the validator. It does the calculations manually. The numbers in D17:G20 are the individual ZScores. The numbers in C17:C20 are the weighted ratings, which are what the macro is supposed to calculate. As you can see, they are identical! Yea!!

Again, thank you. This code is a million times better than anything I would have come up with on my own and in a fraction of the time.


----------



## Alex Blakenburg (Sunday at 8:38 PM)

My pleasure.


----------

