Can you put a couple of dummy worksheets in your workbook.

Call one First and one Last and insert any worksheets you want included in that
formula between those two sheets.

Then you could use:

Dim myStr As String
myStr = "CountA('first:last!A13:A100)"
range("I18").value = Evaluate(myStr)

And not have to worry about the worksheet names at all.

(I'd put a warning in A1 of each of those dummy sheets and protect the
sheet--just so I can remember what they're there for--and I don't delete them by
accident.)


Mike Fogleman wrote:

Right, the 2 permanent sheets are first in the workbook tabs and the 2nd one
is hidden. I believe that changing Sheet2 to Sheet200 in the VBE tree also
changes the sheet index number, which is what I am coding. Maybe I should
create a new workbook with just these 2 sheets as 1 & 2 and code from sheet3
on up.
"KL" wrote in message
...
Hi Mike,

Just a watch out: the syntax all proposed codes use don't care about the
sheet names you change in the VBE Project tree (these names are different
property than the ones you see on the sheet tabs in the worksheet view).
So you may want to check the sheets order and names on the workbook tabs.

Regards,
KL



"Mike Fogleman" wrote in message
...
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









--

Dave Peterson