CODEWITHSUNDEEP
arraysexcelvbaloops
mojo3340
mojo3340

Reputation: 549

ReDim not working inside for loop, no restatement of array size

I have the following code below, the issue is the array size "varray" is not updated everytime a new row is inserted, i know this by checking the value of varray everytime a new row is inserted via the code in Excel. This means the code will stop at the end of a fixed array. I tried to redim in multiple points to see if that worked but it has not.

Sub test()

Dim i As Long
Dim numbers() As Integer
Dim varray As Integer

varray = Sheets("Original").Cells(Rows.Count, "A").End(xlUp).Row
ReDim Preserve numbers(varray)

For i = 10 To varray
ReDim numbers(varray)
    If Cells(i, 16).Value <> "" Then
        Cells(i + 1, 16).EntireRow.Insert
        Cells(i + 1, 1).EntireRow.Value = Cells(i, 1).EntireRow.Value
        Cells(i + 1, 6).Value = Cells(i, 16).Value
        Cells(i + 1, 1).Value = 20305
        Cells(i + 1, 11).Value = ""
        Cells(i + 1, 12).Value = ""
        Cells(i + 1, 15).Value = ""
        Cells(i + 1, 16).Value = ""
        i = i + 1
        ReDim numbers(varray)
    End If
    ReDim numbers(varray)
Next i

End Sub

Upvotes: 0

Views: 101

Answers (1)

David Zemens
David Zemens

Reputation: 53623

You've never changed the value of varray, so your ReDim statements are always resizing the array to its initial value.

varray has been assigned by:

varray = Cells(Rows.Count, "A").End(xlUp).Row

The value of this variable does not change simply by inserting cells/rows in the worksheet.

Upvotes: 1

Related Questions