CODEWITHSUNDEEP
vbaexcel
user3706202
user3706202

Reputation: 215

Loop through each row in a table

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

Upvotes: 4

Views: 38933

Answers (2)

Hambone
Hambone

Reputation: 16377

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

Upvotes: 4

Rory
Rory

Reputation: 34035

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!

Upvotes: 14

Related Questions