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

Hi Mike,

Try this:

Sub test()
Range("I18").Formula = _
Evaluate("=CountA('Sheet1:Sheet" & _
Sheets.Count - 2 & "'!A13:A100)")
End Sub

Regards,
KL


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

Mike,

You can loop thru the sheets and add the counts...
'--------------------
Sub HowMany()
Dim dblCount As Double
Dim lngIndex As Long

For lngIndex = 1 To (Worksheets.Count - 2)
dblCount = dblCount + Application.CountA(Worksheets(lngIndex).Range("A13 :A100"))
Next 'lngIndex
Range("I18").Value = dblCount
End Sub
'----------------------

Regards,
Jim Cone
San Francisco, USA


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

Thanks for the reply KL, but that is not doing it.
"KL" wrote in message
...
Hi Mike,

Try this:

Sub test()
Range("I18").Formula = _
Evaluate("=CountA('Sheet1:Sheet" & _
Sheets.Count - 2 & "'!A13:A100)")
End Sub

Regards,
KL


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

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

Then, how about this:

Sub test()
Range("I18").Formula = _
Evaluate("=CountA('Sheet1:" & _
Sheets(Sheets.Count - 2).Name & "'!A13:A100)")
End Sub

KL



"KL" wrote in message
...
Hi Mike,

Try this:

Sub test()
Range("I18").Formula = _
Evaluate("=CountA('Sheet1:Sheet" & _
Sheets.Count - 2 & "'!A13:A100)")
End Sub

Regards,
KL


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

Yes, Jim that does work, thank you very much. I was hoping to avoid looping
through 100+ sheets. I think Dave Peterson may have a formula with a
variable. I am going to check his out, too.
"Jim Cone" wrote in message
...
Mike,

You can loop thru the sheets and add the counts...
'--------------------
Sub HowMany()
Dim dblCount As Double
Dim lngIndex As Long

For lngIndex = 1 To (Worksheets.Count - 2)
dblCount = dblCount +
Application.CountA(Worksheets(lngIndex).Range("A13 :A100"))
Next 'lngIndex
Range("I18").Value = dblCount
End Sub
'----------------------

Regards,
Jim Cone
San Francisco, USA


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

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

Jim, your loop gave a result of 831, when the right result is 750. What went
wrong?
"Mike Fogleman" wrote in message
...
Yes, Jim that does work, thank you very much. I was hoping to avoid
looping through 100+ sheets. I think Dave Peterson may have a formula with
a variable. I am going to check his out, too.
"Jim Cone" wrote in message
...
Mike,

You can loop thru the sheets and add the counts...
'--------------------
Sub HowMany()
Dim dblCount As Double
Dim lngIndex As Long

For lngIndex = 1 To (Worksheets.Count - 2)
dblCount = dblCount +
Application.CountA(Worksheets(lngIndex).Range("A13 :A100"))
Next 'lngIndex
Range("I18").Value = dblCount
End Sub
'----------------------

Regards,
Jim Cone
San Francisco, USA


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

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

Mike,

I don't know, however, fwiw, there can be a difference between
Sheets.Count and Worksheets.Count.

Jim Cone
San Francisco, USA


"Mike Fogleman" wrote in
message ...
Jim, your loop gave a result of 831, when the right result is 750. What went
wrong?
"Mike Fogleman" wrote in message
...
Yes, Jim that does work, thank you very much. I was hoping to avoid
looping through 100+ sheets. I think Dave Peterson may have a formula with
a variable. I am going to check his out, too.
"Jim Cone" wrote in message
...
Mike,

You can loop thru the sheets and add the counts...
'--------------------
Sub HowMany()
Dim dblCount As Double
Dim lngIndex As Long

For lngIndex = 1 To (Worksheets.Count - 2)
dblCount = dblCount +
Application.CountA(Worksheets(lngIndex).Range("A13 :A100"))
Next 'lngIndex
Range("I18").Value = dblCount
End Sub
'----------------------
Regards,
Jim Cone
San Francisco, USA


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

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

True, but there are no charts. Could it be because the sheets are made on
the fly and the workbook has not been saved yet for Excel to recognize the
sheets index?
"Jim Cone" wrote in message
...
Mike,

I don't know, however, fwiw, there can be a difference between
Sheets.Count and Worksheets.Count.

Jim Cone
San Francisco, USA


"Mike Fogleman" wrote in
message ...
Jim, your loop gave a result of 831, when the right result is 750. What
went
wrong?
"Mike Fogleman" wrote in message
...
Yes, Jim that does work, thank you very much. I was hoping to avoid
looping through 100+ sheets. I think Dave Peterson may have a formula
with
a variable. I am going to check his out, too.
"Jim Cone" wrote in message
...
Mike,

You can loop thru the sheets and add the counts...
'--------------------
Sub HowMany()
Dim dblCount As Double
Dim lngIndex As Long

For lngIndex = 1 To (Worksheets.Count - 2)
dblCount = dblCount +
Application.CountA(Worksheets(lngIndex).Range("A13 :A100"))
Next 'lngIndex
Range("I18").Value = dblCount
End Sub
'----------------------
Regards,
Jim Cone
San Francisco, USA


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

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

Mike,

Do an audit...
But first add a blank sheet as the last sheet.
'---------------------
Sub HowMany()
Dim dblCount As Double
Dim dblShtCnt As Double
Dim lngIndex As Long

For lngIndex = 1 To (Worksheets.Count - 2)
dblShtCnt = Application.CountA(Worksheets(lngIndex).Range("A13 :A100"))
Worksheets(Worksheets.Count).Cells(lngIndex, 2).Value = Worksheets(lngIndex).Name
Worksheets(Worksheets.Count).Cells(lngIndex, 3).Value = dblShtCnt
dblCount = dblCount + dblShtCnt
dblShtCnt = 0
Next 'lngIndex
Range("I18").Value = dblCount
End Sub
'--------------------------

Jim Cone
San Francisco, USA


"Mike Fogleman" wrote in
message ...
True, but there are no charts. Could it be because the sheets are made on
the fly and the workbook has not been saved yet for Excel to recognize the
sheets index?



"Jim Cone" wrote in message
...
Mike,
I don't know, however, fwiw, there can be a difference between
Sheets.Count and Worksheets.Count.
Jim Cone
San Francisco, USA

Nope, that wasn't it, same results.
"Mike Fogleman" wrote in message
...
True, but there are no charts. Could it be because the sheets are made on
the fly and the workbook has not been saved yet for Excel to recognize the
sheets index?
"Jim Cone" wrote in message
...
Mike,

I don't know, however, fwiw, there can be a difference between
Sheets.Count and Worksheets.Count.

Jim Cone
San Francisco, USA


"Mike Fogleman" wrote in
message ...
Jim, your loop gave a result of 831, when the right result is 750. What
went
wrong?
"Mike Fogleman" wrote in message
...
Yes, Jim that does work, thank you very much. I was hoping to avoid
looping through 100+ sheets. I think Dave Peterson may have a formula
with
a variable. I am going to check his out, too.
"Jim Cone" wrote in message
...
Mike,

You can loop thru the sheets and add the counts...
'--------------------
Sub HowMany()
Dim dblCount As Double
Dim lngIndex As Long

For lngIndex = 1 To (Worksheets.Count - 2)
dblCount = dblCount +
Application.CountA(Worksheets(lngIndex).Range("A13 :A100"))
Next 'lngIndex
Range("I18").Value = dblCount
End Sub
'----------------------
Regards,
Jim Cone
San Francisco, USA


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

Sorry, sent wrong reply. The audit sheet (104) shows a count for the 2
permanent sheets, which throws it off by 90. And this total is different
from the one in my sheet using your original loop.
"Jim Cone" wrote in message
...
Mike,

Do an audit...
But first add a blank sheet as the last sheet.
'---------------------
Sub HowMany()
Dim dblCount As Double
Dim dblShtCnt As Double
Dim lngIndex As Long

For lngIndex = 1 To (Worksheets.Count - 2)
dblShtCnt = Application.CountA(Worksheets(lngIndex).Range("A13 :A100"))
Worksheets(Worksheets.Count).Cells(lngIndex, 2).Value =
Worksheets(lngIndex).Name
Worksheets(Worksheets.Count).Cells(lngIndex, 3).Value = dblShtCnt
dblCount = dblCount + dblShtCnt
dblShtCnt = 0
Next 'lngIndex
Range("I18").Value = dblCount
End Sub
'--------------------------

Jim Cone
San Francisco, USA


"Mike Fogleman" wrote in
message ...
True, but there are no charts. Could it be because the sheets are made on
the fly and the workbook has not been saved yet for Excel to recognize the
sheets index?



"Jim Cone" wrote in message
...
Mike,
I don't know, however, fwiw, there can be a difference between
Sheets.Count and Worksheets.Count.
Jim Cone
San Francisco, USA

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

Got it. The audit sheet helped. Even though I changed the sheet number in
VBE properties of the 2 permanent sheets, they are still the first 2 tabs in
the workbook. So this line of code:

Worksheets.Add After:=Sheets(Sheets.Count)

That makes the first added sheet the 3rd tab, etc. So we really need to deal
with 3 to WorkSheets.Count, not 1 to WorkSheets.Count - 2.
Both the audit sheet and the main sheet loop match results of 750.
"Jim Cone" wrote in message
...
Mike,

Do an audit...
But first add a blank sheet as the last sheet.
'---------------------
Sub HowMany()
Dim dblCount As Double
Dim dblShtCnt As Double
Dim lngIndex As Long

For lngIndex = 1 To (Worksheets.Count - 2)
dblShtCnt = Application.CountA(Worksheets(lngIndex).Range("A13 :A100"))
Worksheets(Worksheets.Count).Cells(lngIndex, 2).Value =
Worksheets(lngIndex).Name
Worksheets(Worksheets.Count).Cells(lngIndex, 3).Value = dblShtCnt
dblCount = dblCount + dblShtCnt
dblShtCnt = 0
Next 'lngIndex
Range("I18").Value = dblCount
End Sub
'--------------------------

Jim Cone
San Francisco, USA


"Mike Fogleman" wrote in
message ...
True, but there are no charts. Could it be because the sheets are made on
the fly and the workbook has not been saved yet for Excel to recognize the
sheets index?



"Jim Cone" wrote in message
...
Mike,
I don't know, however, fwiw, there can be a difference between
Sheets.Count and Worksheets.Count.
Jim Cone
San Francisco, USA

So Mike, if you still want to go without a loop, you can try this:

Then, how about this:

Sub test()
Range("I18").Formula = _
Evaluate("=CountA('"& Sheets(3).Name & ":" & _
Sheets(Sheets.Count).Name & "'!A13:A100)")
End Sub


Regards,
KL


"Mike Fogleman" wrote in message
...
Got it. The audit sheet helped. Even though I changed the sheet number in
VBE properties of the 2 permanent sheets, they are still the first 2 tabs
in the workbook. So this line of code:

Worksheets.Add After:=Sheets(Sheets.Count)

That makes the first added sheet the 3rd tab, etc. So we really need to
deal with 3 to WorkSheets.Count, not 1 to WorkSheets.Count - 2.
Both the audit sheet and the main sheet loop match results of 750.
"Jim Cone" wrote in message
...
Mike,

Do an audit...
But first add a blank sheet as the last sheet.
'---------------------
Sub HowMany()
Dim dblCount As Double
Dim dblShtCnt As Double
Dim lngIndex As Long

For lngIndex = 1 To (Worksheets.Count - 2)
dblShtCnt = Application.CountA(Worksheets(lngIndex).Range("A13 :A100"))
Worksheets(Worksheets.Count).Cells(lngIndex, 2).Value =
Worksheets(lngIndex).Name
Worksheets(Worksheets.Count).Cells(lngIndex, 3).Value = dblShtCnt
dblCount = dblCount + dblShtCnt
dblShtCnt = 0
Next 'lngIndex
Range("I18").Value = dblCount
End Sub
'--------------------------

Jim Cone
San Francisco, USA


"Mike Fogleman" wrote in
message ...
True, but there are no charts. Could it be because the sheets are made on
the fly and the workbook has not been saved yet for Excel to recognize
the
sheets index?



"Jim Cone" wrote in message
...
Mike,
I don't know, however, fwiw, there can be a difference between
Sheets.Count and Worksheets.Count.
Jim Cone
San Francisco, USA

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