Hi,
XL2003
I would like to Sum a named range of cells containing only True or False or
blank.
I tried:
Application.WorksheetFunction.Sum(Range("MyRange") )
But this gave zero, even if 'True' was present in the range.
And BTW, is there no SUM function in VBA?
I think I can use AVERAGEA, then multiply by COUNTA(MyRange), but that seems
a bit extreme.
Regards - Dave.

Boolean means true or false. I think you would need to count trues or count
falses.


"Dave" wrote:

Hi,
XL2003
I would like to Sum a named range of cells containing only True or False or
blank.
I tried:
Application.WorksheetFunction.Sum(Range("MyRange") )
But this gave zero, even if 'True' was present in the range.
And BTW, is there no SUM function in VBA?
I think I can use AVERAGEA, then multiply by COUNTA(MyRange), but that seems
a bit extreme.
Regards - Dave.

The conversion is killing you. Put TRUE in A1 and FALSE in A2 and run this:

Sub dural()
MsgBox CInt(Range("A1").Value)
MsgBox CInt(Range("A2").Value)
MsgBox CInt(Range("A1").Value) + CInt(Range("A2").Value)
End Sub

to see what I mean. What is True on the worksheet may not be True in VBA.
--
Gary''s Student - gsnu201001


"Dave" wrote:

Hi,
XL2003
I would like to Sum a named range of cells containing only True or False or
blank.
I tried:
Application.WorksheetFunction.Sum(Range("MyRange") )
But this gave zero, even if 'True' was present in the range.
And BTW, is there no SUM function in VBA?
I think I can use AVERAGEA, then multiply by COUNTA(MyRange), but that seems
a bit extreme.
Regards - Dave.

On 2/22/2010 7:49 PM, Dave wrote:
Hi,
XL2003
I would like to Sum a named range of cells containing only True or False or
blank.
I tried:
Application.WorksheetFunction.Sum(Range("MyRange") )
But this gave zero, even if 'True' was present in the range.
And BTW, is there no SUM function in VBA?
I think I can use AVERAGEA, then multiply by COUNTA(MyRange), but that seems
a bit extreme.
Regards - Dave.


Sounds like you want to "Count"

One of a few examples...

Sub Demo()
Debug.Print WorksheetFunction.CountIf(Range("MyRange"), True)
'or..
Debug.Print WorksheetFunction.CountIf([MyRange], False)
Debug.Print WorksheetFunction.CountIf([MyRange], "")
End Sub

= = = = = = =
HTH :)
Dana DeLouis

Hi GS,
Thanks.
So 'True' in VBA equates to -1. OK.
I also found that:
CInt(Range("A1").Value) + CInt(Range("A2").Value)
and
Range("A1") + Range("A2")
both returned -1.
So how do we SUM in VBA when there's lots of cells?

Regards - Dave




"Gary''s Student" wrote:

The conversion is killing you. Put TRUE in A1 and FALSE in A2 and run this:

Sub dural()
MsgBox CInt(Range("A1").Value)
MsgBox CInt(Range("A2").Value)
MsgBox CInt(Range("A1").Value) + CInt(Range("A2").Value)
End Sub

to see what I mean. What is True on the worksheet may not be True in VBA.
--
Gary''s Student - gsnu201001


"Dave" wrote:

Hi,
XL2003
I would like to Sum a named range of cells containing only True or False or
blank.
I tried:
Application.WorksheetFunction.Sum(Range("MyRange") )
But this gave zero, even if 'True' was present in the range.
And BTW, is there no SUM function in VBA?
I think I can use AVERAGEA, then multiply by COUNTA(MyRange), but that seems
a bit extreme.
Regards - Dave.

Hi Dana,
Thanks. That works.
But still puzzeled as to how we Sum a lot of cells in VBA
Regards - Dave.

"Dana DeLouis" wrote:

On 2/22/2010 7:49 PM, Dave wrote:
Hi,
XL2003
I would like to Sum a named range of cells containing only True or False or
blank.
I tried:
Application.WorksheetFunction.Sum(Range("MyRange") )
But this gave zero, even if 'True' was present in the range.
And BTW, is there no SUM function in VBA?
I think I can use AVERAGEA, then multiply by COUNTA(MyRange), but that seems
a bit extreme.
Regards - Dave.


Sounds like you want to "Count"

One of a few examples...

Sub Demo()
Debug.Print WorksheetFunction.CountIf(Range("MyRange"), True)
'or..
Debug.Print WorksheetFunction.CountIf([MyRange], False)
Debug.Print WorksheetFunction.CountIf([MyRange], "")
End Sub

= = = = = = =
HTH :)
Dana DeLouis
.

But still puzzeled as to how we Sum a lot of cells in VBA

Hi. Here's one way...

Debug.Print WorksheetFunction.Sum(Range("A1:A10"))

HTH
Dana DeLouis


On 2/22/2010 9:10 PM, Dave wrote:
Hi Dana,
Thanks. That works.
But still puzzeled as to how we Sum a lot of cells in VBA
Regards - Dave.

"Dana DeLouis" wrote:

On 2/22/2010 7:49 PM, Dave wrote:
Hi,
XL2003
I would like to Sum a named range of cells containing only True or False or
blank.
I tried:
Application.WorksheetFunction.Sum(Range("MyRange") )
But this gave zero, even if 'True' was present in the range.
And BTW, is there no SUM function in VBA?
I think I can use AVERAGEA, then multiply by COUNTA(MyRange), but that seems
a bit extreme.
Regards - Dave.


Sounds like you want to "Count"

One of a few examples...

Sub Demo()
Debug.Print WorksheetFunction.CountIf(Range("MyRange"), True)
'or..
Debug.Print WorksheetFunction.CountIf([MyRange], False)
Debug.Print WorksheetFunction.CountIf([MyRange], "")
End Sub

= = = = = = =
HTH :)
Dana DeLouis
.





--
= = = = = = =
HTH :)
Dana DeLouis

Hi Dana,
But when we have boolean entries, Range("A1")+Range("A2")+Range("A3") works,
summing False's as 0's, and True's as -1's.
But WorksheetFunction.Sum(Range("A1:A10")) does not seem to work with
boolean entries, always returning 0.

Dave.

"Dana DeLouis" wrote:

But still puzzeled as to how we Sum a lot of cells in VBA

Hi. Here's one way...

Debug.Print WorksheetFunction.Sum(Range("A1:A10"))

HTH
Dana DeLouis

But WorksheetFunction.Sum(Range("A1:A10")) does not seem to work with
boolean entries, always returning 0.

Hi. Summing 'Boolean' entries doesn't make much sense compared to
"Counting."
However, to do so, I believe you need to first convert the boolean to a
value so that 'Sum can work.

Here's one way to Sum 'True as the value 1.

Sub Demo()
Dim x
x = WorksheetFunction.SumProduct([--MyRange])
End Sub

Just note that on a Worksheet, True is +1
I still think "Count" is what you want though :)

= = = = = = =
HTH :)
Dana DeLouis





On 2/22/2010 10:33 PM, Dave wrote:
Hi Dana,
But when we have boolean entries, Range("A1")+Range("A2")+Range("A3") works,
summing False's as 0's, and True's as -1's.
But WorksheetFunction.Sum(Range("A1:A10")) does not seem to work with
boolean entries, always returning 0.

Dave.

"Dana DeLouis" wrote:

But still puzzeled as to how we Sum a lot of cells in VBA

Hi. Here's one way...

Debug.Print WorksheetFunction.Sum(Range("A1:A10"))

HTH
Dana DeLouis

Hi Dana,
OK, I concede. Count it is.
Dave.

"Dana DeLouis" wrote:

But WorksheetFunction.Sum(Range("A1:A10")) does not seem to work with
boolean entries, always returning 0.

Hi. Summing 'Boolean' entries doesn't make much sense compared to
"Counting."
However, to do so, I believe you need to first convert the boolean to a
value so that 'Sum can work.

Here's one way to Sum 'True as the value 1.

Sub Demo()
Dim x
x = WorksheetFunction.SumProduct([--MyRange])
End Sub

Just note that on a Worksheet, True is +1
I still think "Count" is what you want though :)

= = = = = = =
HTH :)
Dana DeLouis





On 2/22/2010 10:33 PM, Dave wrote:
Hi Dana,
But when we have boolean entries, Range("A1")+Range("A2")+Range("A3") works,
summing False's as 0's, and True's as -1's.
But WorksheetFunction.Sum(Range("A1:A10")) does not seem to work with
boolean entries, always returning 0.

Dave.

"Dana DeLouis" wrote:

But still puzzeled as to how we Sum a lot of cells in VBA

Hi. Here's one way...

Debug.Print WorksheetFunction.Sum(Range("A1:A10"))

HTH
Dana DeLouis


.