I'm trying to make a SUMIF using VBA to sum the values until the end of the column, always changing the criteria of the sum using the previus cell of the previous column as parameter.

    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

the r should always change to E1,E2,E3,etc. until end of the column. Because the E is always a new criteria.

CodePudding user response：

From what’s in your code I’m assuming:

1. column E is being SUMIF’d
2. column A is the range being assessed for the SUMIF
3. column B is providing the criteria for the SUMIF
4. the SUMIF value is being output to column F

This is how I’d do it…

Sub Soma()

Dim wf as Application.WorksheetFunction
Dim r_A as range, r_B as range, r_E as range, r_F as Range

For i = 0 to ActiveSheet.Range(“E1048576”).End(xlup).Row - 1

    Set r_A = Range(Range(“A1”),Range(“A1”).Offset(i, 0))
    Set r_B = Range(“B1”).Offset(i, 0)
    Set r_E = Range(Range(“E1”),Range(“E1”).Offset(i, 0))
    Set r_F = Range(“F1”).Offset(i, 0)

    r_F = wf.Sumif(r_A, r_B, r_E)

Next

End Sub

CodePudding user response：

I think you want multiple SUMIFS in column F where column E has the criteria. If so you don't need a loop, you can just enter the formula in F1 and use FILLDOWN to complete the rest

Sub SOMA()

Dim LastRow as Long

LastRow = Range("E" & Rows.Count).End(xlUp).Row
  
Range("F1") = WorksheetFunction.SumIfs(Range("A:A"), Range("B:B"), Range("E1"))
Range("F1:F"&LastRow).FillDown    
     
End Sub

CodePudding user response：

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