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

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

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

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related