Consulting

Results 1 to 10 of 10

Thread: SUMPRODUCT returning FALSE

  1. #1

    Cool 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
    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

  2. #2
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,778
    Location
    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
    Last edited by Aussiebear; 04-27-2023 at 12:08 PM. Reason: Reduced whitespace
    Be as you wish to seem

  3. #3
    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

  4. #4
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,778
    Location
    It doesn't - I missed that. You need to replace =A2 with ='Vehicle Listing'!A2 in the formula string.
    Be as you wish to seem

  5. #5
    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
    Last edited by Aussiebear; 04-27-2023 at 12:10 PM. Reason: Reduced whitespace

  6. #6
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,778
    Location
    It is not possible to use SUMPRODUCT that way in code. You have to use Evaluate.
    Be as you wish to seem

  7. #7
    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

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,778
    Location
    I'm glad you said that, Bob - thought I was going nuts.
    Be as you wish to seem

  10. #10
    Great information!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •