Looping in ListColumn

Question

Is it possible to loop through items in  the ListColumn object?

What I am trying to do is create a loop that does calculations depending on the value of the item.

This is what I have so far:

Dim tbl As ListObject

Set tbl = Sheets("Data").ListObjects("Table1")

Dim Actual As ListColumn

Set Actual = tbl.ListColumn("Actual")

Dim Credits As ListColumn

Set Credits = tbl.ListColumn("Credits")

In theory, my code would do something like this:

For Each actualValue in Actual

If actualValue <> 0 Then total = total + actualValue * creditsValue

Next

actualValue refers to the value of each item in the Actual ListColumn, and creditsValue refers to the value of each item in the Credits ListColumn in the same corresponding row as actualValue.

I am aware that I can do this with regular cell references:

total = total + Cells(row, colActual) * Cells(row, colCredits)

But I am more interested/curious in working with ListObjects if that is at all possible.

Any help or guidance is appreciated.

Answer 1

We can calculate the offset value for the difference between the 2 columns and use that like this:

Sub PlayWithTableLists()

  Dim anyCell As Range

  Dim tbl As ListObject

  Dim Actual As ListColumn

  Dim Credits As ListColumn

  Dim Total As Long ' change type as needed

  'this will hold offset from Credits column to Actual column

Dim offset2Actual As Integer

  Set tbl = Sheets("Data").ListObjects("Table1")

  Set Actual = tbl.ListColumns("actual")

  Set Credits = tbl.ListColumns("credits")

  'Calculate offset from Credits to Actual

  offset2Actual = Actual.Range.Column - Credits.Range.Column

  'this should work for numeric entries

  For Each anyCell In Credits.Range

    If IsNumeric(anyCell) Then

      MsgBox "Credit = " & anyCell & vbCrLf & "Actual = " & anyCell.Offset(0, offset2Actual)

      'Total = Total + creditsValue * actualValue

      Total = Total + anyCell * anyCell.Offset(0, offset2Actual)

    End If

  Next

  MsgBox "The Total is " & Total

  'just good houskeeping - release assigned resources

  'back to the system for reuse

  Set tbl = Nothing

  Set Actual = Nothing

  Set Credits = Nothing

End Sub

Answer 2

Thank you for your reply.

How would I reference the Actual values that are in parallel with the Credits value?

Answer 3

Use the .Range property of the ListColumn object, something like this:

Sub PlayWithTableLists()

  Dim anyCell As Range

  Dim tbl As ListObject

  Dim Actual As ListColumn

  Dim Credits As ListColumn

  Dim Total As Long ' change type as needed

  Set tbl = Sheets("Sheet1").ListObjects("Table1")

  Set Actual = tbl.ListColumns("actual")

  Set Credits = tbl.ListColumns("credits")

  For Each anyCell In Credits.Range

'Credits.Range includes the label, so

'to get beyond it

    If anyCell <> Credits.Name Then

      MsgBox anyCell.Value

    End If

  Next

  'this should work for numeric entries

  For Each anyCell In Credits.Range

    If IsNumeric(anyCell) Then

      Total = Total + anyCell

    End If

  Next

  MsgBox "The Total is " & Total

'just good houskeeping - release assigned resources

'back to the system for reuse

  Set tbl = Nothing

  Set Actual = Nothing

  Set Credits = Nothing

End Sub