CODEWITHSUNDEEP
arraysvbaloopsfor-loop
user2918049
user2918049

Reputation: 25

VBA_Excel_For loop going 1 value too much

I am working on a VBA code and read values from a column, to then return an array with the line numbers in which the cell is empty.

Seems to be working fine, but I have one increment too much on the i loop even though it's entry is set as a fixed value. The consequence is that it creates one additional value in the "arr" array, set at 0.

Can't figure out why. Can anyone help me ?

Sub Main()
Dim NbrEnvoi As Integer: NbrEnvoi = 0

triEnvoi
NbrEnvoi = UBound(triEnvoi)

For i = 0 To NbrEnvoi
    row = triEnvoi(i)
'    appelFonctionMail (row)
Next

End Sub
'*******************************************************************************************
Function triEnvoi() As Variant
'trier les adhérents qui ont déjà eu leur reçu de don
Dim arr() As Integer
Dim sizeArray As Integer: sizeArray = 0

For i = 3 To LastLine
    If IsEmpty(Cells(i, Range("RecuEnvoye").Column)) = True Then
        ReDim Preserve arr(sizeArray + 1)
        sizeArray = UBound(arr)
        arr(sizeArray - 1) = i
    End If
Next
MsgBox (arr(0) & arr(1) & arr(2))
triEnvoi = arr
End Function

Thanks everyone :)

Upvotes: 1

Views: 67

Answers (3)

HTH
HTH

Reputation: 2031

you could use WorksheetFunction.CountBlank() function to count blanks and SpecialCells() method to gather them

as follows:

Function triEnvoi() As Variant
    Dim lastLine As Long, nBlanks As Long
    Dim cel As Range

    lastLine = 20 ' <-- fit it to your needs

    With Intersect(Range("RecuEnvoye").EntireColumn, Rows(3).Resize(lastLine - 3 + 1))
        nBlanks = WorksheetFunction.CountBlank(.Cells)
        If nBlanks > 0 Then
            ReDim arr(1 To nBlanks)
            nBlanks = 0
            For Each cel In .SpecialCells(xlCellTypeBlanks)
                nBlanks = nBlanks + 1
                arr(nBlanks) = cel.Row
            Next
            triEnvoi = arr
        End If
    End With
End Function

and your Main() sub would then be:

Sub main()
    Dim v As Variant, i As Long

    v = triEnvoi

    If Not IsEmpty(v) > 0 Then
        For i = LBound(v) To UBound(v)
            appelFonctionMail triEnvoi(i)
        Next
    End If

End Sub

Upvotes: 1

HackSlash
HackSlash

Reputation: 5805

Instead of ReDimming an array you should always use a collection when the size of the array cannot be determined at compile time.

Like this:

Option Explicit

Sub Main()
    Dim NbrEnvoi As Collection
    Set NbrEnvoi = triEnvoi

    Dim item As Variant
    For Each item In NbrEnvoi
        Row = item
    '    appelFonctionMail (row)
    Next item
End Sub


'*******************************************************************************************
Function triEnvoi() As Collection
    'trier les adhérents qui ont déjà eu leur reçu de don
    Set triEnvoi = New Collection
    Dim i As Long
    For i = 3 To LastLine
        If IsEmpty(Cells(i, Range("RecuEnvoye").Column)) = True Then
            triEnvoi.Add i
        End If
    Next
End Function

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166136

You can do something like this:

Dim arr(), sz As Long, c As Range
sz = 0

For Each c In Selection.Cells
    If Len(c.Value) = 0 Then
        ReDim Preserve arr(0 To sz)
        arr(sz) = c.Row
        sz = sz + 1 '<< this is the ubound for the *next* item (if any)
    End If
Next c

Debug.Print Join(arr, ", ")

Upvotes: 2

Related Questions