CODEWITHSUNDEEP
vbaexcelloops
Jason Dennis
Jason Dennis

Reputation: 23

VBA in Excel is skipping 2nd Do Until Loop Entirely

I have been working on some code which contains several Do Until Loops but when I run it the first loop works as expected but the second and other loops are completely bypassed.

I have run the individual loops in independent sub routines and they worked as needed.

I have also checked previous threads and can't seem to find an example of the same problem.

I initially tried re-declaring i = 2 for the second loop thinking i still valued > than lastRow from the first loop. On top of this I have also tried changing the variable from i to j for the second loop but this also made no difference.

Here an example of the code I am using:

Dim i As Long
i = 2   
Do Until i > lastRow
     Cells(i, 16).Select
     If ActiveCell.Offset(rowOffset:=0, columnOffset:=-13) <> "Invoice" Then
        ActiveCell = ""
        ElseIf ActiveCell.Offset(rowOffset:=0, columnOffset:=-12) <> "" Then
        ActiveCell = ""
        ElseIf ActiveCell.Offset(rowOffset:=0, columnOffset:=-9) <> "Usage Actual" Then
        ActiveCell = ""
        ElseIf ActiveCell.Offset(rowOffset:=0, columnOffset:=-3) = "Final" Then
        ActiveCell = ""
        Else: ActiveCell. _
        FormulaR1C1 = ' vlookup formula to data source
        End If
     i = i + 1
Loop

        ' Additional code to paste out formula and save work book   
Dim j As Long   
j = 2   
Do Until j > lastRow
' from here the process skips right past the next loop to the Additional code to paste out formula and save work book noted below
     Cells(j, 17).Select
     If ActiveCell.Offset(rowOffset:=0, columnOffset:=-1) = "Yes" Then
        ActiveCell.FormulaR1C1 = ' vlookup formula to data source
        Else: ActiveCell = ""
        End If
     j = j + 1
Loop
        ' Additional code to paste out formula and save work book

I have been working on this for several days and have run out of ideas.

Upvotes: 1

Views: 134

Answers (1)

Jason Dennis
Jason Dennis

Reputation: 23

I have finally managed to get the code to work. The method I ended up using involved 2 changes.

Firstly I gave each loop it's own Do Until variable - where the code posted above started with i as the first variable, the next became j, then k and so on.

This on it's own did not initially resolve the issue until I added additional processes between each loop.

For example, following the first loop I added code to perform a lookup in another column and to copy that lookup down to lastRow but without using a loop to do it. I followed this with the next loop and followed that with further additional code, also not requiring a loop to complete.

Fortunately I had enough additional processes to break up all of the loops in the project. Although I still believe running multiple loops one after another shouldn't be a problem, I have yet to find a more effective solution.

Upvotes: 1

Related Questions