View Full Version : SUMPRODUCT returning FALSE

Billy C

01-27-2014, 03:41 AM

Hi I am new to this forum and new to the use of SUMPRODUCT both as a formula and within VBA codeI am using excel 2003 and I am trying to summarise the total charges against various car registration numbersIn sheet Vehicle listing column “A” I have a distinct list of vehicle registration numbersIn sheet Event Repair – OK in column “A” I have vehicle registrations that may repeat more than onceIn sheet Event Repair – OK in column “P” I have the charges for servicesI would like to sum all charges against each registration number and place the answer / formula in the Vehicle Listing sheet column “D” of each rowI have copied with pride some code which I believed would work but it returns FALSE into each cellCan you please review my code and suggest how to fix itMuch appreciated, this has been tormenting me for days

Sub SumCharges()

Dim ws As Worksheet:

Set ws = ThisWorkbook.Worksheets("Vehicle Listing")

Dim LastRow As Long:

Let LastRow = ws.Range("A65536").End(xlUp).Row

Dim cReg As Range:

Set cReg = ws.Range("A2")

Dim ws1 As Worksheet:

Set ws1 = ThisWorkbook.Worksheets("Event Repair - OK")

Dim LastRow1 As Long:

Let LastRow1 = ws1.Range("A65536").End(xlUp).Row

Dim tReg As Range:

Set tReg = ws.Range("A2:A" & LastRow)

Dim tAmount As Range:

Set tAmount = ws.Range("P2:P" & LastRow)

Dim R As Long

'Used for Rows

Dim sFormula As String

For R = 2 To LastRow

'Used for populating

RowsIf Cells(R, 1).Value = "" Then

GoTo ZZ:

sFormula = Evaluate("sumproduct((tReg" = "cReg)* (tAmount))")

Cells(R, 4).Value = ws1.Evaluate(sFormula)ZZ:

Next R

End Sub

Aflatoon

01-27-2014, 09:59 AM

I'm not sure why you are using SUMPRODUCT rather than SUMIF, but you could use:

Sub SumCharges()

Dim ws As Worksheet

Dim LastRow As Long

Dim ws1 As Worksheet

Dim LastRow1 As Long

Dim tAmount As Range

Dim R As Long 'Used for Rows

Dim sFormula As String

Set ws = ThisWorkbook.Worksheets("Vehicle Listing")

LastRow = ws.Range("A65536").End(xlUp).Row

Set ws1 = ThisWorkbook.Worksheets("Event Repair - OK")

LastRow1 = ws1.Range("A65536").End(xlUp).Row

Set tAmount = ws.Range("P2:P" & LastRow)

For R = 2 To LastRow 'Used for populating Rows

If Cells(R, 1).Value <> "" Then

sFormula = "sumproduct((A2:A" & LastRow & "=A2)*P2:P" & LastRow & ")"

Cells(R, 4).Value = ws.Evaluate(sFormula)

End If

Next R

End Sub

Billy C

01-27-2014, 10:34 AM

Hi

Thank you for looking at this. I can't see in the code where it takes the value in worksheet Vehicle Listing range A2 as the criteria and sums corresponding entries from Event Repair - OK worksheet

Can you clarify

Aflatoon

01-27-2014, 01:27 PM

It doesn't - I missed that. You need to replace =A2 with ='Vehicle Listing'!A2 in the formula string.

Billy C

01-28-2014, 02:53 AM

I have created a simple workbook that only has two sheets and on the Data sheet I have populated columns A + BOn the Target worksheet I have populated four rows in column A that has registration numbers that are also in the data sheetI have modified the code as below but still get a “Type Mismatch” message on the row

Cells(R, 2).Value = Application.WorksheetFunction.SumProduct((DataLst = Cells(R, 1).Value) * (DataAmount))

Any ideas, I feel I am so close but frustratingly so far away

Sub SumCharges()

'***Reference to current workbook ***

Dim MyWorkbook As Workbook

Set MyWorkbook = Workbooks("SumProductVBA2.xls")

'***Reference Each Worksheet

Dim SourceSht As Worksheet

Set SourceSht = MyWorkbook.Sheets("Target")

Dim DataSht As Worksheet

Set DataSht = MyWorkbook.Sheets("Data")

'***Establish size of data on each sheet (Last Row) ***

Dim SourceLastRow As Long

SourceLastRow = SourceSht.Range("A65536").End(xlUp).Row

Dim DataLastRow As Long

DataLastRow = DataSht.Range("A65536").End(xlUp).Row

'*** Set Data Ranges ***

Dim DataLst As Range

Set DataLst = DataSht.Range("A2:A" & DataLastRow)

Dim DataAmount As Range

Set DataAmount = DataSht.Range("B2:B" & DataLastRow)

'*** Using SumProduct calculate the total value against each Reg No ***

Dim R As Long 'Used for Rows

For R = 2 To SourceLastRow 'Used for populating Rows

If Cells(R, 1).Value "" Then

Cells(R, 2).Value = Application.WorksheetFunction.SumProduct((DataLst = Cells(R, 1).Value) * (DataAmount))

End If

Next R

End Sub

Aflatoon

01-28-2014, 04:43 AM

It is not possible to use SUMPRODUCT that way in code. You have to use Evaluate.

Billy C

01-28-2014, 05:02 AM

I have never used Evaluate. Can you explain how i would do this with the code I have provided?Sorry about the format, i didnt know how to show it in a separate screen as you did #2

Bob Phillips

01-29-2014, 02:16 AM

You've never used Evaluate? then what are you using in your first post, where incidentally, you also used code tags. Just use it as you did there. But as has been said, you don't need SUMPRODUCT, you can use SUMIF which can be used as an application or WorksheetFunction method.

Aflatoon

01-29-2014, 03:57 AM

I'm glad you said that, Bob - thought I was going nuts. ;)

patcell67

05-24-2016, 10:51 AM

Great information!

Powered by vBulletin® Version 4.2.5 Copyright © 2021 vBulletin Solutions Inc. All rights reserved.