Help with VBA for Looping through and using Countif

[JohnGow383]

Hi,

Having trouble counting items in a table using VBA. I'll post a very simple example here to explain.

I am trying to use VBA to count for each row all the cells with "x" in the above table, only when the cell in Col C = "A"

Here is my code, but it's wrong of course. It is only counting the first row. The answer should be 2+3+3+2=10

Dim ws1 As Worksheet
Dim cell, rng As Range
Dim i, Count As Long

Sub test()


Set ws1 = Sheet1
Set rng = ws1.Range("C4:C13")

For i = 4 To 13
 
   For Each cell In rng
   
      If ws1.Cells(i, 3).Value = "A" Then
     
       Count = Application.WorksheetFunction.CountIf(Range(Cells(i, 4), Cells(i, 10)), "x")
    
      End If
    Next cell
   
 Next i
 
   MsgBox Count 'To test
 End Sub

Any help appreciated thanks

 

[offthelip]

try this change:

Sub test()


Set ws1 = Sheet1
Set rng = ws1.Range("C4:C13")
Count = 0
For i = 4 To 13
 
   For Each cell In rng
   
      If ws1.Cells(i, 3).Value = "A" Then
     
       Count = Count + Application.WorksheetFunction.CountIf(Range(Cells(i, 4), Cells(i, 10)), "x")
    
      End If
    Next cell
   
 Next i
 
   MsgBox Count 'To test

End Sub

 

[Skyybot]

Try this:
Sub test()
Dim ws1 As Worksheet
Dim cell As Range, chkRng As Range, cntRng As Range, box As Range
Dim i As Long, xCount As Long

Set ws1 = ThisWorkbook.Sheets(1)
Set chkRng = ws1.Range("C4:C13")

For Each cell In chkRng
    If cell.Value = "A" Then
        Set cntRng = ws1.Range(Cells(cell.Row, 4), Cells(cell.Row, 10))
            For Each box In cntRng
                If box.Value = "x" Then
                    xCount = xCount + 1
                End If
            Next box
        End If
    Next cell
MsgBox xCount
End Sub

 

[JohnGow383]

Thank you very much this works

Try this:
Sub test()
Dim ws1 As Worksheet
Dim cell As Range, chkRng As Range, cntRng As Range, box As Range
Dim i As Long, xCount As Long

Set ws1 = ThisWorkbook.Sheets(1)
Set chkRng = ws1.Range("C4:C13")

For Each cell In chkRng
    If cell.Value = "A" Then
        Set cntRng = ws1.Range(Cells(cell.Row, 4), Cells(cell.Row, 10))
            For Each box In cntRng
                If box.Value = "x" Then
                    xCount = xCount + 1
                End If
            Next box
        End If
    Next cell
MsgBox xCount
End Sub

 

[JohnGow383]

Sorry this returns 102 but have solution now, thanks

try this change:

Sub test()


Set ws1 = Sheet1
Set rng = ws1.Range("C4:C13")
Count = 0
For i = 4 To 13
 
   For Each cell In rng
  
      If ws1.Cells(i, 3).Value = "A" Then
    
       Count = Count + Application.WorksheetFunction.CountIf(Range(Cells(i, 4), Cells(i, 10)), "x")
   
      End If
    Next cell
  
 Next i
 
   MsgBox Count 'To test

End Sub

 

[Skyybot]

Thank you very much this works

Happy to help.