How do I loop though one column, count all of the zeroes in that column, and then hop to the next column and do the same for every column?

user_19530031 0
Viewed 60

I have some data set up with the the days of the month on the left (aka the rows), and customer names across the columns. Each customer has different values for the days of the month. I would like to count the number of zeroes each customer has and then display that information in a message box once a certain criteria is met. I can do this for one column with the code below:

Dim Count as Integer
Count = 0
 
Dim c As Range
For Each c in Range("C12:AG42")
    If c.Value = "0" Then Count = Count + 1
Next

Cells(2,1).Value = Count

I do not understand how I would write this to move to the next column and also record the result? Is this a nested for loop task? Any advice is greatly appreciated. I learned vba a few years ago and haven't touched it so my skills have deteriorated considerably.

vba excel
2 Answers

Yes, you can use the nested loop, like the one below. It counts 0s in each column and records the subtotal in row 2 of the same column. You can hard code the last column and row if needed.

Sub CountZeroes()

Dim count As Integer
Dim i As Long
Dim x As Long
Dim lastColumn As Long
lastColumn = Cells(12, Columns.count).End(xlToLeft).Column 'Last Column
Dim lastRow As Long
lastRow = Cells(Rows.count, 3).End(xlUp).Row 'LastRow

'3 - Starting column
'12 - Starting row
For i = 3 To lastColumn
    count = 0
    For x = 12 To lastRow
        If CStr(Cells(x, i).Value) = "0" Then
            count = count + 1
        End If
    Next x
    Cells(2, i).Value = count
Next i

End Sub
user_141638390

Count Zeros in Columns

enter image description here

Option Explicit

Sub CountZeros()
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim crg As Range
    Dim ZeroCount As Long
    
    For Each crg In ws.Range("C12:AG42").Columns

        ZeroCount = Application.CountIf(crg, "0")
        crg.EntireColumn.Rows(2) = ZeroCount
        
        ' or just (without the variable):
        'crg.EntireColumn.Rows(2) = Application.CountIf(crg, "0")
        
'        ' Continue with e.g.:
'        Select Case ZeroCount
'        Case Is < 3
'            MsgBox "'" & crg.EntireColumn.Rows(11).Value & "'" _
'                & " is a great customer.", vbInformation
'        Case Is > 7
'            MsgBox "'" & crg.EntireColumn.Rows(11).Value & "'" _
'                & " is a bad customer.", vbCritical
'        ' Add more cases
'        Case Else
'        End Select
    
    Next crg
    
End Sub
user_98140690
Related