OK. There are 2 sheets permanently in the workbook. I changed their index
property in the Project tree to be Sheet125 & 126. These sheetnumbers will
always be greater than the number of sheets added. I always delete the added
sheets and save before running the code again, so new added sheets always
start with 1. This run added 103 sheets and they are correctly consecutive
in the tree. Sheets.Count = 105. Sheets.Count-2 = 103. So Sheet1:Sheet103!
is correct, and it matches the tree. The 2 permanent sheets are not included
in the range.
BTW I just counted them all manually and got 767. A cell formula gives 750?
I dunno.
"KL" wrote in message
...
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