VBA code for summing values of multiple categories in same column

[FrenchCelt]

Hello, I'm trying to build a macro that will sum the values from Column E based on multiple categories in Column D. Column D is comprised of various job functions (e.g. RECEIVE PALLET, CUTTER, and LETDOWN REACH) and Column E is comprised of the quantity completed for those job functions. In doing this manually, I filter by job function and sum the number of quantity completed for that function, but I would prefer to run a macro to do this for me. Does anyone have any suggestions? I figure all I need is a template for one of the job functions like RECEIVE PALLET and then I can customize for the rest of them.

 

[mumps]

Does this help?

Sub SumCells()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("D1:D" & LastRow).AutoFilter Field:=1, Criteria1:="CUTTER"
    MsgBox WorksheetFunction.Sum(Range("E2:E" & LastRow).SpecialCells(xlCellTypeVisible))
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

 

[JLGWhiz]

Have you tried a SumIf() formula?

=SUMIF(D:D, "RECEIVE PALLET",E:E)

Same thing for the other text values in column D and just put the formula where you want the sum to appear.

 

[FrenchCelt]

Does this help?

Sub SumCells()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("D1:D" & LastRow).AutoFilter Field:=1, Criteria1:="CUTTER"
    [COLOR=#ff0000]MsgBox WorksheetFunction.Sum(Range("E2:E" & LastRow).SpecialCells(xlCellTypeVisible))[/COLOR]
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

When I tried inserting that into my macro and ran it, I got "Run-time error '1004': No cells were found" for the code highlighted in red.

 

[mumps]

Do you have the job function "CUTTER" in column D?

 

[FrenchCelt]

Have you tried a SumIf() formula?

=SUMIF(D:D, "RECEIVE PALLET",E:E)

Same thing for the other text values in column D and just put the formula where you want the sum to appear.

I can't get it to work as written. I tried to put this in cell M3, which is where the value for the sum of RECEIVE PALLET needs to appear:

Range("M3").Select
Selection.ActiveCell.FormulaR1C1 = SUMIF(D:D, "RECEIVE PALLET",E:E)

and I got a syntax error. I've tried looking at other instances where I've inserted formulas into specific cells in other macros and I can't figure out how I should parse the code appropriately.

 

[FrenchCelt]

Do you have the job function "CUTTER" in column D?

Yes, several instances.

 

[mumps]

Is there a header in cell D1 and data starting at row 2?

 

[FrenchCelt]

Does this help?

Sub SumCells()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("D1:D" & LastRow).AutoFilter Field:=1, Criteria1:="CUTTER"
    MsgBox WorksheetFunction.Sum(Range("E2:E" & LastRow).SpecialCells(xlCellTypeVisible))
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

I should specify that I would need the sum of CUTTER to appear in cell M7 of my worksheet. Each job function will have its sum appear in various cells. I will have to run more formulas once I get the sum of each job function, but this is the first step.

 

[FrenchCelt]

Is there a header in cell D1 and data starting at row 2?

Yes.