Sumif by VBA using the previous cell of the previous column always as parameter until the end of the column

user_10000255 0
Viewed 153

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.

Edit 1:

enter image description here

Sorry for my bad explaning, what i expected was make a SUMIF(A:A;E1;B:B) row by row placing the values on column F always changing the criteria to E2,E3,E4,etc. until the end of the column F.

ms-office excel-formula vba excel
3 Answers

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
user_98140690

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 WorksheetFunction
Dim r_A As Range, r_B As Range, r_E As Range, r_F As Range

Set wf = Application.WorksheetFunction

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

user_104325250

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
user_189008430
Related