2

I am trying to automate sumIfs using multiple criteria for the below sample table.

Column A to F is the data and the output table is displayed in column H through K. The output table sums the values in the data based on condition1 and condition2. I am able to use excel built in formulas to do this task, however the values are 0 when I use a vba code to do this.

enter image description here

Can someone help?

I am new to VBA and the code produced 0 when tried in a For loop for my actual data.

Here is my code that I used for this sample, but this throws an error with the SumRange.

Sub Macro3()

    Dim ws As Worksheet, Lr1 As Long, Lc1 As Long
    Set ws = Worksheets("Sheet1")
    
    Lr1 = ws.Range("A" & Rows.Count).End(xlUp).Row
    Lc1 = ws.Range("A" & Columns.Count).End(xlToLeft).Column
    
    
    For i = 3 To Lr1
   
            ws.Range("I" & i).Formula = "=sumifs(range("B"&i&":"F"&i),B1:F1,I1,B2:F2,I2)"
     
    Next i

End Sub

Also, tried using this:

Sub Macro3()

    Dim ws As Worksheet, Lr1 As Long, Lc1 As Long
    Set ws = Worksheets("Sheet1")
    
    Lr1 = ws.Range("A" & Rows.Count).End(xlUp).Row
    Lc1 = ws.Range("A" & Columns.Count).End(xlToLeft).Column
    
    For i = 3 To Lr1
    
    Range("I" & i).Value = Application.WorksheetFunction.SumIfs(Range(Cells(i, 2), Cells(i, Lc1)), Range("B1:F1"), Range("I1"), Range("B2:F2"), Range("I2"))
     
     Next i
 End Sub

Error 1004: unable to get sumifs property of the worksheet function class

VBasic2008
  • 44,888
  • 5
  • 17
  • 28
Sree
  • 21
  • 2
  • 4
    Where is the code? We need you to provide a [mcve]. We are not going to write code from scratch, but we can help you find the error in your existing code. – Scott Craner Jan 04 '23 at 19:36
  • Turn on the Macro Recorder and click the cell that has the SUMIF function in it. – ASH Jan 05 '23 at 01:25

1 Answers1

1

Writing Formulas With VBA: SUMIFS

Option Explicit

Sub WriteSumIfsFormula()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
    
    Dim LastRow As Long: LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Dim rg As Range: Set rg = ws.Range("I3", ws.Cells(LastRow, "K"))
    
    Dim Formula As String ' Pay attention to the dollar signs!
    Formula = "=SUMIFS($B3:$F3,$B$1:$F$1,I$1,$B$2:$F$2,I$2)"
    'Debug.Print Formula ' check until you got it right
    
    rg.Formula = Formula

End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28