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

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

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

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

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

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

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

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

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

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

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