Need to sum above the active cell until bold

[elpoder]

Hi,

Ive been trying to change the code related to post http://www.excelforum.com/excel-gene...ntil-bold.html in order to sum above the active cell until it hits a bold cell.
Currently the code sums below the active cell until it finds a bold one.

Function SumNotBold(rng As Range) As Double
Dim c As Range, acall As Range
Set acall = Application.Caller.Offset(1)
Set funcrng = Intersect(Range(acall, acall.End(xlDown)), rng, Application.Caller.Parent.UsedRange)
For Each c In funcrng
    If c.Font.Bold = True Then Exit For
    SumNotBold = SumNotBold + IIf(IsNumeric(c), c, 0)
Next
End Function

I've tried changing it to:

Function SumNotBold(rng As Range) As Double
Dim c As Range, acall As Range
Set acall = Application.Caller.Offset(-1)
Set funcrng = Intersect(Range(acall, acall.End(xlUp)), rng, Application.Caller.Parent.UsedRange)
For Each c In funcrng
    If c.Font.Bold = True Then Exit For
    SumNotBold = SumNotBold + IIf(IsNumeric(c), c, 0)
Next
End Function

with no success.

Can anyone help me or explain me where to start?

Thanks in advance!

[alansidman]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(Added this time. Please read the rules and adhere to them.)

[elpoder]

Sorry, It will not happen again. Thank you.

[patel45]

attach please a sample file with data and desired result

[HaHoBe]

Hi, elpoder,

you must alter the logic from going down until a cell is bold to to start from when a cell is bold.

Maybe try:

Function SumNotBold(rng As Range) As Double
Dim c As Range, acall As Range, funcrng As Range, blnAdd As Boolean
Set acall = Application.Caller.Offset(-1)
Set funcrng = Range(acall, acall.End(xlUp))
blnAdd = False
For Each c In funcrng
    If c.Font.Bold = True Then blnAdd = True
    If blnAdd Then SumNotBold = SumNotBold + IIf(IsNumeric(c), c, 0)
Next c
End Function

Ciao,
Holger

[elpoder]

I appreciate the code provided by HaHoBe, but it's still not working.

I'm attaching the excel sheet for more details. I hope this will be more helpful.

Thank you!

[HaHoBe]

Hi, elpoder,

I hope this will be more helpful.

Sorry but not for me as I see only a nomal sum function in the cells. Maybe you could just use a different formula like

=SUMIF($B$1:C5,B6,$C$1:C5)

Ciao,
Holger

[HaHoBe]

Hi, elpoder,

the range being used for the sample is like

=SumNotBoldUp($C$1:C5)

for C6 and should be starting with C1 as well for the other cells.

UDF being used:

Function SumNotBoldUp(rng As Range) As Double
Dim aCall As Range
Dim funcRng As Range
Dim lngCounter As Long

Set aCall = Application.Caller.Offset(-1)
Set funcRng = Range(aCall, aCall.End(xlUp))

For lngCounter = funcRng.Cells.Count To 1 Step -1
    With funcRng.Cells(lngCounter)
      If funcRng.Cells(lngCounter).Font.Bold = True Then Exit For
      SumNotBoldUp = SumNotBoldUp + IIf(IsNumeric(.Value), .Value, 0)
    End With
Next lngCounter

Set funcRng = Nothing
Set aCall = Nothing
End Function

Ciao,
Holger

[elpoder]

Hi Holger,

I really appreciate your time and excel expertise. It worked out perfectly!