A range reference always has a parent. The parent is the sheet on which it
resides.

A string can be use with evaluate since evaluate acts as a virtual cell

mycnt = Evaluate("Countif(D8:D31,""0"")")

but even in this usage, the D8:D31 is refering to the activesheet.

You use evaluate as a virtual cell on the activesheet. It is mandatory if
you want to do array formulas (or sumproduct used in an array formula
fashion) strictly in VBA because VBA worksheetfunctions don't operate as
array formulas. Evaluate is useful in a few other situations. A bad use of
evaluate is -

Some people like to use

set rng = [A1]
using the shortcut for evaluate.

However, it is silly to go to the Excel application and ask it to evaluate
the string A1 and return a range. This usage is much slower than set rng
= Range("A1")

It isn't all bad - there are some times when the square brackets or evaluate
are more efficient or more useful in a similar context - I don't recall any
good examples at the moment.


--
regards,
Tom Ogilvy


"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.