Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Sum By Color (Office 2002)

    I swiped some code for a User-Defined-Function from the internet a few months back, placed it in an add-in and like the way it works except for one little problem... The code will give a correct result, but when the data is sorted and/or re-sorted, the formula results in a #Value error. [F9] will not fix the error but [Ctrl][Alt][F9] will! My spreadsheet is already set to Calculation|Automatic. Can something be added to the code that will force it to keep up with changes and sorts?

    <pre>Function SumByColor(InputRange As Range, ColorRange As Range) As Double
    ' returns the sum of each cell in the range InputRange that has the same
    ' background color as the cell in ColorRange
    ' example: =SumByColor($A$1:$A$20,B1)
    ' range A1:A20 is the range you want to sum
    ' range B1 is a cell with the background color you want to sum
    Dim cl As Range, TempSum As Double, ColorIndex As Integer
    ' Application.Volatile ' this is optional
    ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
    TempSum = 0
    On Error Resume Next ' ignore cells without values
    For Each cl In InputRange.Cells
    If cl.Interior.ColorIndex = ColorIndex Then
    TempSum = TempSum + cl.Value
    End If
    Next cl
    On Error GoTo 0
    Set cl = Nothing
    SumByColor = TempSum
    End Function</pre>

    - Ricky

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum By Color (Office 2002)

    I do not get that failure when I try it. Could you upload a workbook that shows the problem?
    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum By Color (Office 2002)

    Sure. I went ahead and added the code to a module within the worsheet that I've attached. I got the same #VALUE results. So, it's not working for me as an addin or as code in the sheet... Thanks for taking a look.
    - Ricky

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum By Color (Office 2002)

    First, one small comment about your sort macros. You can make them a little more efficient and eliminate some screen flashing by changing them to look something like this:

    <pre>Sub Button1_Click()
    '
    ' Button1_Click Macro
    ' Macro recorded 1/21/2005 by Ricky Ridgdill
    '

    '
    Range("data").Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    End Sub
    </pre>



    Now to your problem with the #Value error. It appears that when you do a sort, Excel disables access to the interior property of all cells on the worksheet, and does not enable access until after any recalculation that results from the sort has completed. That basically means that the cell color of all of the cells on the worksheet are not avaliable when the SumByColor function runs after the sort. This causes the #Value error. I am afraid I don't have a soultion to this problem. Maybe someone else will jump in with something.
    Legare Coleman

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum By Color (Office 2002)

    I removed the ( ' ) from the line Aplication.Volatile, making it active. Now [F9] works without having to press the Ctrl and Alt keys. But still not as automatic as I'd like.

    And thanks for the tip on the "sort" macros. I should've known that the range did not have to be selected first. Excel itself, is not very efficient. I used the Recorder to dish up those sorts and Excel added the Range.Select lines.
    - Ricky

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Sum By Color (Office 2002)

    You can define a named range ColorTotals for the cells containing the SumByColor function. Recalculate this range in the macros that sort:

    Sub Button1_Click()
    Range("data").Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    Range("ColorTotals").Calculate
    End Sub

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum By Color (Office 2002)

    Ahhhhh! I was looking for problems withing the SumByColor function, when the problem was really with the sorts. Once I realized that [F9] would work immediately after doing the sort, should've figured out that adding that calculate line to the end would be the answer.

    Still, I wonder why Excel doesn't recalculate automatically after the sort? Under Options|Calculate, the method is set to Automatic. And, would simply adding the code to recalculate the sheet rather than the specific range work?

    Thanks,
    - Ricky

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sum By Color (Office 2002)

    Excel doesn't consider sorting a "change" so nothing needs to be recalculated. Your function is based on formatting changes, which doesn't trigger any event to change so calculations do not need to be updated

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •