Object Error當我運行該功能時，我找不到出現的 。我不明白為什么會這樣。它應該可以正常作業，但不可以。我希望得到一些幫助，任何幫助將不勝感激。
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
uj5u.com熱心網友回復：
VBASumIf使用.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
如果你堅持按照自己的方式去做，因為回圈是必要的，所以效率較低，你可以這樣做......
' 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
因為第二個引數criteria不應該是一個范圍： ......數字、運算式、單元格參考、文本或函式形式的標準......
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/373153.html