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]

Try this:

Assuming the range and result are all in the active sheet.
Not sure what

Blad4​

is.

Sub Count_Me()
'Modified 5/31/18 4:55 AM EDT
Application.ScreenUpdating = False
Dim ans As Long
ans = 0
Dim c As Range
    For Each c In Range("E2:AT820")
        If c.Value = "0" Then ans = ans + 1
    Next
Cells(2, 1).Value = ans
Application.ScreenUpdating = True
End Sub

 

[lisanne_123]

I'm sorry, blad4 means sheet4

Try this:

Assuming the range and result are all in the active sheet.
Not sure what

Blad4​

is.

Sub Count_Me()
'Modified 5/31/18 4:55 AM EDT
Application.ScreenUpdating = False
Dim ans As Long
ans = 0
Dim c As Range
    For Each c In Range("E2:AT820")
        If c.Value = "0" Then ans = ans + 1
    Next
Cells(2, 1).Value = ans
Application.ScreenUpdating = True
End Sub

 

[lisanne_123]

Thanks! The code works, but is still counts the total of cells with the value of zero. But I need to count the total ranges.

For example:
1 1 1 0 0 1 0 The total amount of zeros is 3 but the total ranges with zeros is 2.

I don't know how to do that

 

[My Aswer Is This]

Is the range we are searching on sheet4

If not I need both sheet names.

Like this Sheet:

"Master"
"Four"

 

[My Aswer Is This]

I'm not sure now what your wanting.

You said search the range for "0"

I do not understand your example

 

[My Aswer Is This]

Are you saying if one cell has the value "000145678"
The count for that cell would be 3

 

[lisanne_123]

I'm making a calendar to allocate cottages to certain reservations. Now, I need to reduce the amount of gaps in this calendar, but first i need to count them.
Is all on the same sheet

 

[My Aswer Is This]

Not sure what Gaps are.
When you said "0" did you mean "0" or did you mean empty?

I think you may want to explain your entire Goal.
How would finding the number of cells with "0" help you remove Gaps
What is a Gap?

 

[lisanne_123]

The calendar contains on the top line the dates, on the left the number of a cottage. And within it is filled with the number of the reservation. If a cottage is empty on that date, it gets a 0 in the cell. I need to count the total number of zeros, but if there is a serie of zeros the gap is still counted as 1.
I hope this makes any sense