Excel VBA loop reading cells issues

applebar 0
Viewed 30

I'm new to Excel VBA, but I can't find a solution for this issue.

I'm trying to create a FOR EACH loop which reads floats inside a range. Inside this loop i should understand if the floats in the cell are 0s or floats greater than 0 and, in the second scenario, put this float in a list/array.

the function should return the first number of the list or 0, if in the range there aren't any floats greater than 0.

in a python env, the code would look like this:

rng = list of floats in the range

empty_list = []

for element in rng:

   if element>0:

      empty_list.append(element)

if len(empty_list)>0:

   return empty_list[0]

if len(empty_list)==0:

   return 0

what I wrote so far in VBA:

Function getNumber()
    Dim rng As Range
    Dim cell As Range
    Dim ArrayLen As Integer
    Dim arr() As Integer
    Dim counter As Integer
    
    ReDim arr(1)
    
    counter = 0
    For Each cell In rng
        If cell > 0 Then
            arr(counter) = cell.Value
            counter = counter + 1
        End If
    Next
    
    ArrayLen = UBound(arr) - LBound(arr) + 1
    If ArrayLen = 0 Then
        getNumber = 0
    End If
    
    If ArrayLen > 0 Then
        getNumber = 10
    End If
    
End Function

Thanks in advance to anybody!

error-handling function vba excel arrays
2 Answers

Arrays in VBA don't really have an equivalent to list.append. One option is resize an array as needed using ReDim, but it's an expensive operation and should be done sparingly.

You don't even need an array though:

Function getNumber(ByVal rng As Range) As Double
    If WorksheetFunction.Countifs(rng, ">0") = 0 Then Exit Function

    Dim cell As Range
    For Each cell In rng
        If cell.Value > 0 Then
           getNumber = cell.Value
           Exit Function
        End If
    Next
End Function
BigBen0

I'm not sure what the end goal is, but here you can use the counter to find out how many, or output the array of found numbers somewhere.

Option Explicit
Sub Testfunction()
Debug.Print getNumber
End Sub
Function getNumber()
    
    Dim I As Long
    Dim Rng As Range
    Dim Cell As Range
    Dim ArrayLen As Integer
    Dim InArray()
    Dim OutArray()
    Dim Counter As Integer
    
    Set Rng = Sheet1.Range("A1:A10")
    InArray = Rng
    Counter = 0
    
    For I = 1 To UBound(InArray, 1)
        Debug.Print InArray(I, 1)
        If InArray(I, 1) > 0 Then Counter = Counter + 1
    Next I
    
    ReDim OutArray(1 To Counter)
    Counter = 1
    
    For I = 1 To UBound(InArray, 1)
        If InArray(I, 1) > 0 Then
            OutArray(Counter) = InArray(I, 1)
            Counter = Counter + 1
        End If
    Next I
    
    getNumber = UBound(OutArray)
    
'    counter = 0
'    For Each Cell In Rng.Cells
'        If Cell.Value > 0 Then
'            arr(counter) = Cell.Value
'            counter = counter + 1
'        End If
'    Next
'
'    ArrayLen = UBound(arr) - LBound(arr) + 1
'    If ArrayLen = 0 Then
'        getNumber = 0
'    End If
'
'    If ArrayLen > 0 Then
'        getNumber = 10
'    End If
    
End Function
Cameron Critchlow0
Related