Loop through each row in a table

Question

I've defined a table as cards (see picture here: https://www.flickr.com/photos/113328996@N07/)

I now want a create some VBA code which does the following:

1. Loop through the whole table
2. Count the times it 'hits' "A".

I wantto do something like this

sub countCards

//Open sheets: "sheet1"

dim countA as integer
countA = 0

//foreach each row in table "cards"
if (cells is "A")
countA = countA + 1
end if

But I can't find the syntax to get this working. Can anybody help me?

Dear regards,

Marc

Answer 1

One way:

Dim oList As ListObject
Dim oRow As ListRow
Dim counta As Long

Set oList = ActiveSheet.ListObjects("cards")
For Each oRow In oList.ListRows
    If oRow.Range(1) = "A" Then counta = counta + 1
Next oRow
MsgBox counta

but using Application.Countif would be simpler!

Answer 2

This may be excessively verbose, but it should give you an idea.

Sub countCards()

  Dim table As ListObject
  Dim tableData, rowData As range
  Dim countA As Integer

  Set table = Worksheets("Sheet1").ListObjects("Table1")
  Set tableData = table.range

  countA = 0

  For Each rowData In tableData.Rows

    If Cells(rowData.row, rowData.Column).Value = "A" Then
      countA = countA + 1
    End If

  Next

End Sub