Wow...I feel like I'm in the presence of greatness!!

I ran the code and got the value of -7 in cell E33.

I know the answer is 17...so I suspect the code is trying to count the total
rows, which would be 24 and then subtract 7 to get to 17, but it looks like
it's missing the 24 and using 0 - 7 to get -7.

David


"Die_Another_Day" wrote:

Tx, just a couple of questions for you Tom, what do I use Evaluate for?
In this routine I was wondering if it is possible to create a range
that is not linked to a specific sheet.
Dim r1 as Range
set r1 = Range("A1:A10")
For Each ws In ActiveWorkbook.Worksheets
if r1....
or is a dimmed range always linked directly to some specific cell?

Thanks,

Charles

P.S. David here is the most updated code:

Sub CellCounter()
Dim ws As Worksheet
Dim r1 As Range
Dim nonZero As Long
Dim cnt As Long
For Each ws In ActiveWorkbook.Sheets
If Left(ws.Name, 3) = "REV" Then
Set r1 = ws.Range("D8:D31")
nonZero = Application.CountIf(r1, "0")
ws.Range("E32") = nonZero
If nonZero = 0 Then
ws.Range("E33") = r1.Cells.Count
ElseIf r1.Cells(r1.Rows.Count, 1) = "0" Then
For cnt = r1.Rows.Count To 1 Step -1
If r1.Cells(cnt, 1) < 0 Then Exit For
Next
ws.Range("E33") = r1.Cells(r1.Rows.Count) - cnt
Else
ws.Range("E33") = 0
End If
End If
Next
End Sub

Tom Ogilvy wrote:
Charles,
Just a head up.
You don't need to use evaluate with Application.Countif or
Application.CountBlank. These return the number just as they would in the
worksheet. Using evaluate does nothing.

--
Regards,
Tom Ogilvy


"Die_Another_Day" wrote:

Sub CellCounter()
Dim ws As Worksheet
Dim r1 As Range
For Each ws in ActiveWorkbook.Sheets
If Left(ws.Name,3) = "REV" Then
Set r1 = ws.Range("D8:D31")
ws.Range("E32") = Evaluate(Application.CountIf(r1,"0"))
If Evaluate(Application.CountBlank(r1)) = r1.Cells.Count Then
ws.Range("E33") = r1.Cells.Count
ElseIf r1.Cells(r1.Rows.Count,1) = "" Then
ws.Range("E33") = (r1.Cells(r1.Rows.Count,1).Row - _
r1.Cells(r1.Rows.Count,1).End(xlUp).Row)
End If
End If
Next
End Sub

Glad to be of help.

Charles
David wrote:
Now THAT was impressive...worked perfectly the way you wrote it...but I'm
looking for the result found on each page to be written to cells on each page
instead of a message box with the total.
Using what you've done, I would like the total count of data cells written
to cell E32 and the number of blank cells written to cell E33 on each page
for that page's results. Thanks so much...we're almost there!

"Die_Another_Day" wrote:

Sub CellCounter()
Dim DataCells as Long
Dim BlankCells as Long
Dim ws As Worksheet
Dim r1 As Range
DataCells = 0
BlankCells = 0
For Each ws in ActiveWorkbook.Sheets
If Left(ws.Name,3) = "REV" Then
Set r1 = ws.Range("D8:D31")
DataCells = DataCells + Evaluate(Application.CountIf(r1,"0"))
If Evaluate(Application.CountBlank(r1)) = r1.Cells.Count Then
BlankCells = BlankCells + r1.Cells.Count
ElseIf r1.Cells(r1.Rows.Count,1) = "" Then
BlankCells = BlankCells + (r1.Cells(r1.Rows.Count,1).Row -
_
r1.Cells(r1.Rows.Count,1).End(xlUp).Row)
End If
End If
Next
Msgbox "There a " & DataCells & " data cells and" & _
vbcrlf & BlankCells & " blank cells after the data cells"
End Sub

Hope I got what you were asking.

Charles
David wrote:
I have a fixed range of cells (D8:D31) on over 100 sheets that I need to get
two things:
1. The count of cells with data (0) and
2. The count of empty cells AFTER the cells with data to D31.

Once data is started in any period, there should be no break in the data,
i.e. if sales start in D14 and the last cell with data is D24, all the cells
inbetween will also have data.

Depending on when sales start, there can be empty cells starting at D8 to
when the first sales for a period are entered (these are new locations added
during the year).

Maybe setting a new range based on the first cell that is greater than 0 to
D31, and then doing the count on that range. I have no idea how to code this.
I need to write the results to two cells, but think I can do that part, but
need to to the same thing for all worksheets beginning with REV. The names
would be like REV001, REV002, etc.

Any ideas would be appreciated.