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