SUMIF Using VBA has Object Error But Unable to Resolve-CodePudding

I am unable to find the Object Error is appearing when i run the function. I do not why this is happening. It should work fine but no it does not. I hope to get some help and any help will be appreciated.

Sub SumIF()

Dim LastRow As Long
Dim sh As Worksheet

Set sh = Sheets("SumIF")

LastRow = sh.Cells(Rows.Count, 1).End(xlUp).Row

Table1 = sh.Range("A2:A" & LastRow) 'Need to Match this with Table3
Table2 = sh.Range("B2:B" & LastRow) 'Need to Sum this in K2:K
Table3 = sh.Range("J2:J" & LastRow)

sh.Range("K2:K" & LastRow) = Application.WorksheetFunction.SumIF(Table1, Table3, Table2)

End Sub

CodePudding user response：

VBA `SumIf` Using `.Formula`

Option Explicit

Sub VBASumIfFormula()
    
    ' Workbook, Worksheet
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("SumIf")
    
    ' Source Column Ranges
    Dim slRow As Long: slRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If slRow < 2 Then Exit Sub ' no data in column range
    Dim slrg As Range: Set slrg = ws.Range("A2:A" & slRow) ' lookup column
    Dim svrg As Range: Set svrg = slrg.Offset(0, 1) ' values column
    
    ' Destination Column Ranges
    Dim dlRow As Long: dlRow = ws.Cells(ws.Rows.Count, "J").End(xlUp).Row
    If dlRow < 2 Then Exit Sub ' no data in column range
    Dim dlrg As Range: Set dlrg = ws.Range("J2:J" & dlRow) ' lookup column
    Dim dvrg As Range: Set dvrg = dlrg.Offset(0, 1) ' values column (empty)
    
    ' Construct formula string.
    Dim FormulaString As String
    FormulaString = "=IFERROR(SUMIF(" & slrg.Address & "," _
        & dlrg.Cells(1).Address(0, 0) & "," & svrg.Address & "),"""")"
    'Debug.Print FormulaString
    
    ' Write formulas.
    dvrg.Formula = FormulaString
    ' Convert formulas to values.
    dvrg.Value = dvrg.Value

End Sub

If you insist on doing it your way, which is less efficient since looping is necessary, you could do...

' Either...
Dim cCell As Range
For Each cCell In Table3.Cells 
    cCell.Offset(0, 1).Value = Application.WorksheetFunction _
        .SumIf(Table1, cCell.Value, Table2)
Next cCell
' ... or:
Dim cCell As Range
For Each cCell In sh.Range("K2:K" & LastRow).Cells ' or e.g. 'Table4'
    cCell.Value = Application.WorksheetFunction _
        .SumIf(Table1, cCell.Offset(0, -1).Value, Table2)
Next cCell

because the second argument, criteria, is not supposed to be a range: ... criteria in the form of a number, expression, a cell reference, text, or a function...

VBA SumIf Using .Formula

VBA `SumIf` Using `.Formula`