CODEWITHSUNDEEP
excelvba
PyGuy
PyGuy

Reputation: 33

Trying to fix a Do While loop in VBA

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!

Upvotes: 1

Views: 233

Answers (1)

chris neilsen
chris neilsen

Reputation: 53125

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

Upvotes: 2

Related Questions