FRANK

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Thank you very much for anserwing my bold function question
but I get a #value error when I try it. Sometime it takes
me to the VB part and highlightes the .rng in the If
rng.Areas.Count Line.
The actual cells I'm using are d23:d27. What am I doing
wrong?

Following is my previous post and your reply-


Hi
you need VBA for this. Try the following

=SUMPRODUCT(--(BoldIndex(A1:A5)),A1:A5)
to sum all bold cells within the range A1:A5

Adapt this to your requirements


'---------------------------------------------------------------------
Function BoldIndex(rng As Range) As Variant
'---------------------------------------------------------------------

Dim cell As Range, row As Range
Dim ret_Bold

If rng.Areas.Count > 1 Then
BoldIndex = CVErr(xlErrValue)
Exit Function
End If

For each cell in rng
if cell.font.bold then
ret_Bold = ret_Bold + 1
end if
next
BoldIndex = ret_Bold

End Function



--
Regards
Frank Kabel
Frankfurt, Germany
Newsbeitrag
Hello,

What formula would i use to add for example a1:a5, but to
only add together cells with a number that is bold? Would
it be sumformat? I've tried but I just can't seem to get it.

I'm using excel 2003.

Thanks
Brian
Click to expand...

..
 
Hi
my fault (wrong adaption of existing code). Use the following function

'---------------------------------------------------------------------
Function BoldIndex(rng As Range) As Variant
'---------------------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim iWhite As Long, iBlack As Long
Dim aryBold As Variant
If rng.Areas.Count > 1 Then
BoldIndex = CVErr(xlErrValue)
Exit Function
End If

If rng.Cells.Count = 1 Then
If rng.Font.Bold Then
aryBold = 1
Else
aryBold = 0
End If

Else
aryBold = rng.Value
i = 0

For Each row In rng.Rows
i = i + 1
j = 0

For Each cell In row.Cells
j = j + 1

If cell.Font.Bold Then
aryBold(i, j) = 1
Else
aryBold(i, j) = 0
End If

Next cell

Next row

End If

BoldIndex = aryBold

End Function
 
Back
Top