SUMPRODUCT returning FALSE
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
Code:
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