2

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

Community
  • 1
  • 1
user3706202
  • 197
  • 1
  • 3
  • 14

2 Answers2

11

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!

Rory
  • 32,730
  • 5
  • 32
  • 35
3

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
Hambone
  • 15,600
  • 8
  • 46
  • 69
  • thanks for the quick reply. When I try this: Set table = Worksheets("Blad1").ListObjects("Table1") I get an error however that "error is out of range". Do you know what might go wrong? – user3706202 Jun 26 '14 at 13:23
  • Check the table name... if you click in the table, select the "Table Tools/Design" ribbon tab, the very first ribbon group should have a text box for "Table Name:" Does the name match? You can always rename it (in this text box) to match. – Hambone Jun 26 '14 at 15:04
  • But truth be told, I like Rory's solution better. – Hambone Jun 26 '14 at 15:05