how to test is dynamic array contains a zero

[ilcaa]

i am creating Dynamic arrays from ranges and need to check if it contains a zero, if it does do this, else do that..

whats the best way to test if a returned dynamic array contains a zero? thanks

 

[gifariz]

=IF(COUNTIF(A1#,0),dothis,dothat)

 

[bebo021999]

I believe COUNTIF does not work with arrray...
Try this:
=IF(OR(array=0), do this, do that)

 

[gifariz]

I believe COUNTIF does not work with arrray...
Try this:
=IF(OR(array=0), do this, do that)

Curious, why it works in mine?

 

[bebo021999]

Curious, why it works in mine?
View attachment 76713

Because this

i am creating Dynamic arrays from ranges and need to check if it contains a zero, if it does do this, else do that..

whats the best way to test if a returned dynamic array contains a zero? thanks

I though OP want "Dynamic arrays from range" not "Dynamic range".
While your formula works for "Dynamic range": A1:B5

 

[kevin9999]

One possibility

Sub Test_For_Zero()
    Dim Arr, i As Long, j As Long, k As Long
    Arr = Range("A1", Cells(Rows.Count, "B").End(xlUp)) '<< change range to suit
    
    For i = 1 To UBound(Arr, 1)
        For j = 1 To UBound(Arr, 2)
            If Arr(i, j) = 0 Then
                k = k + 1
            End If
        Next j
    Next i
    
    If k > 0 Then
        'Do something
    Else
        'Do something else
    End If
End Sub

 

[bebo021999]

One possibility

Sub Test_For_Zero()
    Dim Arr, i As Long, j As Long, k As Long
    
End Sub

Why do not stop right after zero found?

Sub Test_For_Zero()
    Dim Arr, i As Long, j As Long, k As Long
    Arr = Range("A1", Cells(Rows.Count, "B").End(xlUp)) '<< change range to suit
     For i = 1 To UBound(Arr, 1)
        For j = 1 To UBound(Arr, 2)
            If Arr(i, j) = 0 Then
                 Do something
                  Exit sub 
            End If
        Next j
    Next i
   Do something else
  End Sub

Or using FIND should be simpler

Sub Test_For_Zero()
    Dim Arr, i As Long, j As Long, k As Long
    Arr = Range("A1", Cells(Rows.Count, "B").End(xlUp)) '<< change range to suit
    If not Arr.Find 0 is nothing then 
           Do something
     else
         Do Something else
    end if
end sub

 

[kevin9999]

Why do not stop right after zero found?

Sub Test_For_Zero()
    Dim Arr, i As Long, j As Long, k As Long
    Arr = Range("A1", Cells(Rows.Count, "B").End(xlUp)) '<< change range to suit
     For i = 1 To UBound(Arr, 1)
        For j = 1 To UBound(Arr, 2)
            If Arr(i, j) = 0 Then
                 Do something
                  Exit sub 
            End If
        Next j
    Next i
   Do something else
  End Sub

Or using FIND should be simpler

Sub Test_For_Zero()
    Dim Arr, i As Long, j As Long, k As Long
    Arr = Range("A1", Cells(Rows.Count, "B").End(xlUp)) '<< change range to suit
    If not Arr.Find 0 is nothing then 
           Do something
     else
         Do Something else
    end if
end sub

@bebo021999 yes, good point!

 

[ilcaa]

=IF(COUNTIF(A1#,0),dothis,dothat)

just using the COUNTIF(A1#,0)... this works in a direct formula against a dynamic array like you show in your example and i tested with Sequence()

but fails to return a scalar Count result within a LET (which is where I will use it on), I am expecting a single scalar value, >0 or 0,

within a LET it returns an array of #VALUE! errors... why? how can i return a scalar within a LET

=LET(x, SEQUENCE(4,,1,1),
COUNTIF(x,0)
)

 

[Fluff]

How about

=LET(x, SEQUENCE(4,,1,1),COUNT(FILTER(x,x=0)))