Consulting

Results 1 to 3 of 3

Thread: SUMPRODUCT in VBA Code

  1. #1
    VBAX Tutor Philcjr's Avatar
    Joined
    Jul 2005
    Location
    Bedminster, NJ
    Posts
    208
    Location

    SUMPRODUCT in VBA Code

    All,
    I need some help here, my "SUMPRODUCT" is not working and I just can not figure out what is wrong

    The file I am working in is a personal file with my finance... hence why a file is not attached.

    So here goes...


    Sub SumCharges()
    Dim X As Long, C As Long
    Dim ws As Worksheet: Set ws = Application.Worksheets("Charges")
    Dim LastRow As Long: Let LastRow = ws.Range("A65536").End(xlUp).Row
    Dim ws1 As Worksheet: Set ws1 = Application.Worksheets("Roll-Up")
    Dim LastRow1 As Long: Let LastRow1 = ws1.Range("A65536").End(xlUp).Row
    Dim LastCol As Long: Let LastCol = ws1.Range("A" & LastRow1).End(xlToRight).Column
    With Application
    .DisplayAlerts = False
    .EnableEvents = False
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    End With
    'Define the ranges used in SUMPRODUCT
    Dim rDate As Range: Set rDate = ws.Range("C2:C" & LastRow)
    Dim rCode As Range: Set rCode = ws.Range("A2:A" & LastRow)
    Dim rAmount As Range: Set rAmount = ws.Range("F2:F" & LastRow)
    For C = 2 To LastCol 'Used for populating all Columns
    For X = 5 To LastRow1 - 5 'Used for populating all Rows
    If Cells(X, 1).Value = "" Then GoTo ZZ:
    Cells(X, C).Value = Application.WorksheetFunction.SumProduct _
    ((rCode = Cells(1, C).Value) * (rDate = "2005/02") * (rAmount))
    ZZ:
    Next X
    Next C
    With Application
    .DisplayAlerts = True
    .EnableEvents = True
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    End With
    End Sub

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    SUMPRODUCT is not exposed to Worksheetfunction, you have to evaluate itt

    sFormula = "SumProduct(--(A2:A" & LastRow & "=" & Cells(1, C).Value & _
                         "),--(C2:C" & LastRow & "=""2005/02""),(F2:F" & LastRow & ")"
                Cells(X, C).Value = ActiveSheet.Evaluate(sFormula)
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Tutor Philcjr's Avatar
    Joined
    Jul 2005
    Location
    Bedminster, NJ
    Posts
    208
    Location
    Bob,
    As usual you save the day. THANKS for your help.

    Here is the final code, if anyone wishes to comment or revise the code... feel free... as of now it takes about 10 seconds to complete:


    Sub SumCharges()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Charges")
    Dim LastRow As Long: Let LastRow = ws.Range("A65536").End(xlUp).Row
    Dim rDate As Range: Set rDate = ws.Range("C2:C" & LastRow)
    Dim rCode As Range: Set rCode = ws.Range("A2:A" & LastRow)
    Dim rAmount As Range: Set rAmount = ws.Range("F2:F" & LastRow)
    Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Worksheets("Roll-Up")
    Dim LastRow1 As Long: Let LastRow1 = ws1.Range("A65536").End(xlUp).Row
    Dim LastCol As Long: Let LastCol = ws1.Range("A" & LastRow1).End(xlToRight).Column
    Call Settings.SettingsOff
    Dim R As Long, C As Long 'Used for Rows and Columns
    Dim sFormula As String, sYear As String, sMonth As String
    For C = 2 To LastCol 'Used for populating Columns
    Application.StatusBar = Format(((C - 1) / LastCol) * 100, "0.0") & "% Complete..."
    For R = 2 To LastRow1 - 5 'Used for populating Rows
    If Cells(R, 1).Value = "" Then GoTo ZZ:
    sYear = ws1.Evaluate("Year(A" & R & ")") & "/"
    sMonth = Format(ws1.Cells(R, 1), "mm")
    sFormula = "SumProduct((Code" & "=""" & Cells(1, C).Value & """)*(Date=""" & sYear & sMonth & """)*(Amount))"
    Cells(R, C).Value = ws1.Evaluate(sFormula)
    ZZ:
    Next R
    Next C
    Call Settings.SettingsOn
    End Sub

Posting Permissions

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