Trying to fix a Do While loop in VBA

Question

thanks in advance for taking the time to help. I have built a Do While loop in VBA that for some reason breaks when j = 1. I have in cells C3:C7 these values: 13,14,14,13,14.

Here's the short script:

Dim i, j, n As Integer
Dim List(0) As Integer

i = o
j = 0
n = 0

Do While Cells(i + 3, 3) <> ""     

    If Cells(i + 3, 3) > 13 Then
        List(j) = i + 3
        j = j + 1
        Cells(i + 3, 4) = "Noted"
        i = i + 1
    ElseIf Cells(i + 3, 3) = 13 Then
        Cells(i + 3, 4) = "Skipped"
        i = i + 1
    Else
        i = i + 1
    End If
Loop

For n = j To n = 0
    Rows(List(n)).Delete
Next

Thanks again!

You are declaring List as an array with one element starting at zero. Once j=1 it exceeds the upper bound. — SJR
– SJR, Commented Nov 24, 2016 at 18:32
Not an answer, but Dim i, j, n As Integer is actually declaring i As Variant, j As Variant, n As Integer. If you want all your variables to be declared as Integer, you need to do it like this: Dim i As Integer, j As Integer, n As Integer — Victor Moraes
– Victor Moraes, Commented Nov 24, 2016 at 18:53
Thanks Phil - good catch. Victor, that's good to know. I hadn't realized that. Thanks for sharing. SJR, yes, that does seem to be the issue. I would have thought that including List(j) = i + 5 would add an instance/index and increase the upper bound of List() by 1 (each time I increase j). It seems to do that in other loops of mine. Is there something I should include to increase the upper bound of my array each time that IF condition is met? — PyGuy
– PyGuy, Commented Nov 24, 2016 at 19:08

chris neilsen · Accepted Answer · 2017-01-01 22:27:58Z

2

Your intent is sound, but there are quite a few errors. See commented code below for details

Sub Demo()
    ' ~~ must explicitly type each variable.  Use Long
    Dim i As Long, j As Long, n As Long
    Dim List() As Long '<~~ dynamic array

    i = 3 '<~~ eliminate the klunky +3
    j = 0
    n = 0
    ReDim List(0 To 0) '<~~ initialise dynamic array
    Do While Cells(i, 3) <> vbNullString
        If Cells(i, 3) > 13 Then
            ReDim Preserve List(0 To j) '<~~ resize array
            List(j) = i
            j = j + 1
            Cells(i, 4) = "Noted"
        ElseIf Cells(i, 3) = 13 Then
            Cells(i, 4) = "Skipped"
        End If
        i = i + 1 '<~~ simplify, its called in each if case anyway
    Loop

    ' j will end up 1 greater than size of array
    If j > 0 Then '<~~ only execute if we found some rows to delete
        For n = j - 1 To 0 Step -1 '<~~ For loop syntax
            Rows(List(n)).Delete
        Next
    End If
End Sub

edited Jan 1, 2017 at 22:27

answered Nov 24, 2016 at 20:48

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PyGuy Over a year ago

Thanks, Chris. That did the trick - testing it bit by bit, the ReDim Preserve List(0 to j) is what specifically did the trick. Thank you so much for rewriting the sub to be cleaner and better structured. I learned more than I was expecting to. Thanks again!

Collectives™ on Stack Overflow

Trying to fix a Do While loop in VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related