sumif in vba on array [duplicate]

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
– BigBen, Commented Jul 21, 2023 at 13:01
"I also try on range": you actually didn't. rr is a Variant array, not a Range. — BigBen
– BigBen, Commented Jul 21, 2023 at 13:35
You need Set rr = Range(.Sheets("data").Cells(2, 5), .Sheets("data").Cells(4, 5)) — Tom Sharpe
– Tom Sharpe, Commented Jul 21, 2023 at 13:42

Kavorka · Accepted Answer · 2023-07-21 13:37:58Z

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

Collectives™ on Stack Overflow

sumif in vba on array [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related