sumif in vba on array

Question

enter code herei try to sum all positive value in my array but every time object not found or parameter misssing error seen.

Option Explicit

Sub trail2()
    With ThisWorkbook
        Dim arr1 As New ArrayList
        arr1.Add 20
        arr1.Add -15
        arr1.Add 20.77
        msgbox  Application.WorksheetFunction.CountIf(arr1, ">0")
    end with
end sub

error = object not found or parameter misssing error seen

i also try on range but thats also not working

Option Explicit
Sub trail2()
dim rr as variant
dim i as variant
 With ThisWorkbook
 rr = Range(.Sheets("data").Cells(2, 5), .Sheets("data").Cells(4, 5))
 msgbox Application.WorksheetFunction.CountIf(rr, ">0")
end with
end sub

same data as per array but error seen. error = object required i don't want that sheet data so i need only array for sumif. how to slove it ?

The first parameter of `CountIf` must be a `Range`, so that approach won't work. Use a loop. — BigBen, Jul 21 '23 at 13:01
"I also try on range": you actually didn't. `rr` is a `Variant` array, not a `Range`. — BigBen, Jul 21 '23 at 13:35
You need Set rr = Range(.Sheets("data").Cells(2, 5), .Sheets("data").Cells(4, 5)) — Tom Sharpe, Jul 21 '23 at 13:42

score 1 · Accepted Answer · answered Jul 21 '23 at 13:37

When assigning a range to a variable, you need to use Set

You should also use the worksheet your range is on to avoid it pulling from an active sheet.

Option Explicit
Sub trial2()

Dim rr As Range

Dim i As Variant

    With ThisWorkbook.Sheets("Data")
        Set rr = .Range(.Cells(2, 5), .Cells(4, 5))
        MsgBox Application.WorksheetFunction.CountIf(rr, ">0")
    End With
End Sub

sumif in vba on array

1 Answers1