Function to count bold cells
Greg Johnson
returns the number of bolded cells in a passed range.
Public Function countbold(myRange As Range) As Integer
Dim dRange As Range
Dim c As DataObject
countbold = 0
For Each c In dRange
If c.Font.Bold = True Then
countbold = countbold + 1
End If
Next c
End Function
All that this returns is #VALUE
It is called by CountBold(S2:S60)
Can anybody relieve my madness?
Mark Lundberg
Here's a stripped down version that works. Error traps I leave to you.
Public Function countbold(myRange As Range) As Integer
For Each c In myRange
If c.Font.Bold = True Then
countbold = countbold + 1
End If
Next c
End Function
In the code posted earlier, dRange wasn't Set to anything.
HTH,
Mark Lundberg
London, England
Greg Johnson wrote ...
Allan P. London
You were real close but you passed myRange but made your count in the
undefined dRange. Simply change dRange to myRange, you dont need dRange at
all so eliminate the Dim statement.
APL
Brian Reincke
can actually DO anything. The loop has to work on an internal object to the
module. I have done this sort of thing before by putting the results in an
array first, but you cannot make it work with a sheet function - there is
nothing to initiate the filling of the array. In excel 97 you can link it to
an event, but that defeats the object, because you presumably want to react
to changes in bold.
Perhaps someone has found a way round, but it is bound to be devious.
Brian R
>I've been workin on this for hours. I wanr to write a function that
>returns the number of bolded cells in a passed range.
>
>Public Function countbold(myRange As Range) As Integer
>
>Dim dRange As Range
>Dim c As DataObject
>
>countbold = 0
>
>For Each c In dRange
> If c.Font.Bold = True Then
> countbold = countbold + 1
> End If
>Next c
>
>End Function
>
>All that this returns is #VALUE
>
John Green
Public Function CountBold(myRange As Range) As Long
Dim c As Range, count As Long
For Each c In myRange
If c.Font.Bold = True Then
count = count + 1
End If
Next c
CountBold = count
End Function
You could stick with Integer if you won't exceed 32000 bold cells.
If you were working with Excel 5/95 you would have to change the first
line:
Function CountBold(myRange As Object) As Long
HTH,
John Green - Excel MVP
Sydney
Australia
In article <368004...@7south.com>, Greg Johnson wrote:
> Date: Tue, 22 Dec 1998 15:43:17 -0500
> From: Greg Johnson <gjoh...@7south.com>
> Subject: Function to count bold cells
> Newsgroups: microsoft.public.excel.programming
John Green
You might like to make the function volatile:
Public Function CountBold(myRange As Range) As Long
Dim c As Range, count As Long
Application.Volatile
to force a recalc when data changes. This will not make the function
update when you bold a cell in the range, but it will ensure a recalc
when any data changes,
Myrna Larson
>I am pretty certain you are trying to do the impossible. An Excel function
>can actually DO anything.
No, it is definitely possible. Several corrections to his code have been
posted. His errors involved using an uninitialized variable (dRange) and a
declaring variable C of the wrong type.
--
Myrna Larson
e-mail to: myrna...@csi.com
Myrna Larson
wrote:
>You were real close but you passed myRange but made your count in the
>undefined dRange. Simply change dRange to myRange, you dont need dRange at
>all so eliminate the Dim statement.
I couldn't get it to work with C declared as a DataObject, which, according to
Help is "A holding area for formatted text data used in transfer operations."
Could you?