Vba Count range of zeros in a bigger range

[lisanne_123]

Hey,

I need te count the amount of ranges with zeros in a bigger range.

Now I have:
Dim nrGaps as Integer
nrGaps = Application.WorksheetFunction.CountIf(Range("E2:AT820"), "0")
Blad4.Cells(2, 1) = nrGaps

But this count the number of zero. Can somebody help?

 

[My Aswer Is This]

So your saying it may be 0 or 00 or 000 or 00000
Is that correct?

 

[My Aswer Is This]

If the room is reserved what is entered into the cell?

 

[kalak]

Hey,

I need te count the amount of ranges with zeros in a bigger range.

Now I have:
Dim nrGaps as Integer
nrGaps = Application.WorksheetFunction.CountIf(Range("E2:AT820"), "0")
Blad4.Cells(2, 1) = nrGaps

But this count the number of zero. Can somebody help?

You could try something like this.
If you're looking down columns run it this code as it is. For looking along rows leave out the red bit.

Sub count_groups_of_zeros()
Dim c, q As Long, r As Long, s As Long
For Each c In Range("E2:AT820").Value
    If c = 0 Then
        r = r + 1
        If q = 0 Then
            q = 1
            s = s + 1
        End If
    Else
        q = 0
    End If
Next c
MsgBox r & " zeros in " & s & " groups"
End Sub

 

[lisanne_123]

Thank you so much!! It worked!!

You could try something like this.
If you're looking down columns run it this code as it is. For looking along rows leave out the red bit.

Sub count_groups_of_zeros()
Dim c, q As Long, r As Long, s As Long
For Each c In Range("E2:AT820").Value
    If c = 0 Then
        r = r + 1
        If q = 0 Then
            q = 1
            s = s + 1
        End If
    Else
        q = 0
    End If
Next c
MsgBox r & " zeros in " & s & " groups"
End Sub