How to execute one do loop after another?

David Rojas 0
Viewed 49

a few days ago I used one piece of coding from someone who helped me create a list in a new sheet with a program that checks a column in a different sheet, and at each cell, it makes sure that the value is the string "daily". I wanted to do the same after it's done with the first do-until but it does not seem to execute the second do-until.

Private Sub PopulateTaskList()
 Dim wMS As Worksheet, wsTL As Worksheet, rngC As Range
 Dim boolExists As Boolean, i As Long
   
    'Create new sheet named
    For i = 1 To Worksheets.Count 'check if the sheet exists
        If Worksheets(i).Name = "Task List" Then
            Set wsTL = Worksheets(i)
            boolExists = True
            Exit Sub
        End If
    Next i
    If Not boolExists Then
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Task List"
        Set wsTL = Worksheets("Task List")
    End If
    
    Set wMS = Worksheets("MSS")
    
    Set rngC = wMS.Range("C3")
    Do Until rngC.Value = ""
        If rngC.Value = "Daily" Then
            wsTL.Rows("2:2").EntireRow.Insert
            rngC.Copy wsTL.Range("B2") 'if you place the range in clipboard, it will be copied during the row insertion...
            
            rngC.Offset(0, 1).Copy wsTL.Range("D2")
            
            rngC.Offset(0, -2).Copy wsTL.Range("C2")
            
            rngC.Offset(0, -1).Copy wsTL.Range("A2")
        End If
        Set rngC = rngC.Offset(1) 'set the range as the next cell down
    Loop
    
    Do Until rngC.Value = ""
        If rngC.Value = "Weekly" Then
            wsTL.Rows("2:2").EntireRow.Insert
            rngC.Copy wsTL.Range("B2") 'if you place the range in clipboard, it will be copied during the row insertion...
            
            rngC.Offset(0, 1).Copy wsTL.Range("D2")
            
            rngC.Offset(0, -2).Copy wsTL.Range("C2")
            
            rngC.Offset(0, -1).Copy wsTL.Range("A2")
        End If
        Set rngC = rngC.Offset(1) 'set the range as the next cell down
    Loop
End Sub

Basically, I want to do the first do until with the if condition having the daily and then the second do until with the if condition having the weekly but it only executes the first one. I tried adding an else if but as soon as it hits a weekly on the column that it evaluates it starts doing the weekly if and the idea is that it first finishes all the dailys and then moves on to the weeklys.

vba excel
1 Answers

The way your code is written will guarantee that the second loop is skipped because the conditions to stop both loops are both the same. Your code is essentially comparable as the following:

Dim i As Long
i = 0

do while i < 5
   i=i+1
loop 
 
'when i = 5, the first loop stops, but the second one is 
'skipped because i is not less than 5.
do while i < 5 'no action happens because i is already 5.
i = i + 'please accept
loop

There are several ways to fix this, I'll take a guess and say it's one of these two...

First Guess Of Issue

You're missing an Exit For statement when the if condition is true. This would mean your first loop would stop immediately and carry forward to next loop.

Do Until rngC.Value = ""
If rngC.Value = "Daily" Then
    ' your regular code that isn't relevant...
    Exit For
End If
     Set rngC = rngC.Offset(1) 'set the range as the next cell down
Loop

Do Until rngC.Value = ""  'in this case rngC is definitely not blank because we quit when it was daily.
   If rngC.Value = "Weekly" Then
   ''
   End If
   Set rngC = rngC.Offset(1) 'set the range as the next cell down
Loop

Second Guess Of Issue

This is just a guess, but were you expecting to start the second loop from the begining? If so, just set rngC to the original parameter of c3. See below:

set rngC = wMS.Range("C3")
    
    Do Until rngC.Value = ""
        If rngC.Value = "Weekly" Then
            wsTL.Rows("2:2").EntireRow.Insert
'.....
pgSystemTester0
Related