excel - VBA 的 Sumif 始终使用前一列的前一个单元格作为参数，直到列结束

Question

我正在尝试制作 SUMIF使用 VBA 对值求和直到列的末尾，始终使用前一列的前一个单元格作为参数来更改求和的标准。

    Sub SOMA()

Dim r As Range

For Each r In Range("E1")
r = ("E1" + 1)
LastRow = Range("E" & Rows.Count).End(xlUp).Row
  
    Range("F1:F" & LastRow) = WorksheetFunction.SumIfs(Range("A:A"), Range("B:B"), Range(r))
    
    Next r
    
End Sub

r应始终更改为 E1、E2、E3 等。直到专栏结束。因为 E永远是一个新的标准。

编辑1:

对不起，我的解释不好，我期望的是制作 SUMIF(A:A;E1;B:B)将值逐行放置在 F 列上总是将条件更改为 E2 , E3 , E4 ，ETC。直到列的结尾 F .

Answer 1

VBA SumIfs

Option Explicit

Sub SOMA()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Source
    Dim sws As Worksheet: Set sws = wb.Worksheets("Sheet1")
    Dim slRow As Long: slRow = sws.Range("B" & sws.Rows.Count).End(xlUp).Row
    Dim scrg As Range: Set scrg = sws.Range("B2:B" & slRow) ' Criteria Range
    Dim ssrg As Range: Set ssrg = sws.Range("A2:A" & slRow) ' Sum Range
    
    ' Destination
    Dim dws As Worksheet: Set dws = wb.Worksheets("Sheet1")
    Dim dlRow As Long: dlRow = dws.Range("E" & dws.Rows.Count).End(xlUp).Row
    Dim dcrg As Range: Set dcrg = dws.Range("E2:E" & dlRow) ' Criteria Range
    Dim dsrg As Range: Set dsrg = dws.Range("F2:F" & dlRow) ' Sum Range
    Dim dcOffset As Long: dcOffset = dsrg.Column - dcrg.Column
    
    Application.ScreenUpdating = False
    
    Dim dcCell As Range ' Criteria Cell
    
    ' Loop.
    For Each dcCell In dcrg.Cells
        dcCell.Offset(, dcOffset).Value = Application.SumIfs(ssrg, scrg, dcCell)
    Next dcCell
    
    Application.ScreenUpdating = True
    
    MsgBox "Soma is done.", vbInformation
    
End Sub