Sumif criteria with for loop in VBA

Question

Dim i As Integer, j As Integer, k As Integer, l As Integer

i = ActiveSheet.PivotTables(1).TableRange2.Rows.Count + 1
j = ActiveSheet.PivotTables(1).TableRange2.Columns.Count 
k = ActiveSheet.PivotTables(1).TableRange2.Rows.Count - 7 

For l = 2 To j
    Cells(i + 2, l).Value = WorksheetFunction.SumIf(Range(Cells(9, l), Cells(i, l)), ">0", Range(Cells(9, l), Cells(i, l)))
Next l

Hi, I am trying to use sumif function in vba. The point is to do this in loop, which depends on the number of columns. I do not actually now what is wrong here, but I think that probably there is something with criteria I took. I want to sum all cells with number higher than 0 and not sure how to write this.

Answer 1

If in your SumIf you are using the Range you are comparing with the Criteria (>0) also as the Range to sum, there is no need to add it inside the SumIf as the third argument.

You could go with the code below:

For l = 2 To j
    Cells(i + 2, l).Value = WorksheetFunction.SumIf(Range(Cells(9, l), Cells(i, l)), ">0")
Next l

Answer 2

Use the following function. No need to use "*" for comparing

Cells(i + 2, l).Value = Aplication.WorksheetFunction.SumIf(Range(Cells(9, 2), Cells(i, 2)), "> 0", Range(Cells(9, 2), Cells(i, 2)))