Mike,

I believe there is something that you are forgetting to tell. Something that
is critical to understand why both Dave and my solutions don't work for you
and do work for us (BTW Dave and I gave just exactly the same solution).
Possibly, the order of the sheets is not in accordance with the numbers
after the word "Sheet". Or maybe the sheets are called differently at all,
in which case the second solution I posted above should probably work.

Regards,
KL

"Mike Fogleman" wrote in message
...
Hi Dave, I am starting to believe it too. Your formula is the same as one
of my tries. It gives a result of 1. Jim's loop gave a result of 831,
which I would say is more accurate. However using a cell formula I get
750, which is accurate.
"Dave Peterson" wrote in message
...
How about...

Dim myStr As String
myStr = "CountA('Sheet1:Sheet" & Sheets.Count - 2 & "'!A13:A100)"
range("I18").value = Evaluate(myStr)

I don't think you can use a 3D reference like this in VBA. (Well, I
couldn't!)

(You could loop throught the worksheets to achieve the same effect,
though.)



Mike Fogleman wrote:

I am trying to put a calculated value in a cell without putting the
formula
in the cell. VB will calculate and put result.
The syntax is killing me. What is it for this:

Range("I18").Value =
Application.WorksheetFunction.CountA('Sheet1:Sheet (Sheets.Count-2)'!A13:A100)

Mike F

--

Dave Peterson