Stuck trying to build a sumifs in vba

Question

I am currently trying to teach myself some basic excel vba and I am stuck on a sumifs.

I have two Worksheets (Sheet1 and Sheet2) in the same workbook. I am trying to have cells Sheet 1 D4:D20 sum up the data in sheet 2 based on two criteria. The first criteria (Y) is variable and stored in Sheet1 C4:C20, while the second criteria is E. Unfortunately, I keep getting errors when I try to calculate the last row. What I have so far is:

Sub Test_Sumifs()

Dim EndRow As Long
Dim i As Integer

EndRow = Cells(row.Count, "C").End(x1Up).row

Set SumRange = Worksheets("Sheet2").Range("D2:D17")
Set Criteria1 = Worksheets("Sheet2").Range("B2:B17")
Set Criteria2 = Worksheets("Sheet2").Range("C2:C17")

For i = 4 To EndRow
  Cells(i, 4).Value = WorksheetFunction.SumIfs(SumRange, Criteria1, [C4], Criteria2, ["E"])

Next i
End Sub

I've looked around and tried various methods but can't figure out whats wrong.

Greatly appreciate your help,

My bad should've specified, the error is error 424 on EndRow = Cells(row.Count, "C").End(x1Up).row, also I am not sure if the following in the worksheet function criteria1, [C4] is working or if excel is just comparing everything to C4 instead of going down to each cell — Ario
For starters it should be End(xlUp) with an L rather than a 1. — SJR
Also, it looks like the value you want to give the cell is a combination of function and value.... — Mistella

Vityata Vityata · Accepted Answer · 2018-06-07T17:08:43

Option Explicit

Sub TestSumifs()

    Dim EndRow As Long
    Dim i As Long

    EndRow = Cells(Rows.Count, "C").End(xlUp).Row
    Dim sumRange As Range
    Dim criteria1 As Range, criteria2 As Range

    Set sumRange = Worksheets(2).Range("D2:D17")
    Set criteria1 = Worksheets(2).Range("B2:B17")
    Set criteria2 = Worksheets(2).Range("C2:C17")
    Dim wf As WorksheetFunction

    For i = 4 To EndRow
        Cells(i, 4) = wf.SumIfs(sumRange, criteria1, Range("C4"), criteria2, "E")
    Next i

End Sub

x1Up is really not present in VBA (Although there are 110 questions containing it. Any idea why & how?)
EndRow = Cells(Rows.Count, "C").End(xlUp).Row - this returns the last row of C correctly
Option Explicit - forces to declare variables

Stuck trying to build a sumifs in vba

1 Answers