Philcjr
03-17-2008, 06:47 AM
All,
I need some help here, my "SUMPRODUCT" is not working and I just can not figure out what is wrong :dunno
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
I need some help here, my "SUMPRODUCT" is not working and I just can not figure out what is wrong :dunno
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