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