Results 1 to 4 of 4

Thread: combine macro that sums per color with a specific date (day)

  1. #1

    combine macro that sums per color with a specific date (day)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    hi Gurus...LOL
    I have a plan that sums my production per colors, works, but it is like this:

    job.......due .................cut......qty
    Job1.....22/09.................18/09......
    job2.....22/09..................19/09.....
    job3....22/09...................19/09.....
    job4....22/09...................18/09.....
    job5....22/09...................19/09.....


    after rum the macro, I got as answer obvious green 55, red 70

    but this is not an accurate result, the job was due for the same day, but was produced in different days (18 & 19)

    that is a way to separate by date? maybe insert a sumif to compare dates? to have something like this:

    Cut.........18/09....19/09
    Green.......25.........30
    Red.........30.........40

    tkx for help me

    Leandro Jorge




    using the following funcion:

    Function SumByColor(CellColor As Range, SumRange As Range)
    Dim myCell As Range
    Dim iCol As Integer
    Dim myTotal
    iCol = CellColor.Interior.ColorIndex
    For Each myCell In SumRange
    If myCell.Interior.ColorIndex = iCol Then
    myTotal = WorksheetFunction.Sum(myCell) + myTotal
    End If
    Next myCell
    SumByColor = myTotal
    End Function

  2. #2

  3. #3
    guys sorry, I don't use forums often, I just thought the forums were independent each order, that why I even made easy to everyone, I kept the same title.

    the problem is, my solutions seems not be possible.

    tkx anyway

  4. #4
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Create a UDF as follows

    Code:
    Function IColor(CellColor As Range) As Long
        IColor = CellColor.Interior.ColorIndex    'can get fancy and return a text colour here but quick and easy fix here
    End Function
    Then add a column =IColor(D2)

    Then run a pivot table on that.

Tags for this Thread

Posting Permissions

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