Do Until with Multiple "LastRow" Conditions

[reberryjr]

I'm adding records to an existing sheet, and trying to update certain columns with "pre-set data". I've got the code below, which does everything I want, except for update the records.

When I step through the code, I see:
Data ends in Column A, at row 595. tLastRow is identifying the correct row.
Data ends in Column U, at row 600. aLastRow is identifying the correct row.
But no updates are occurring.

Sub CompleteSetup()
Application.ScreenUpdating = False
Dim mws2 As Worksheet
Set mws2 = ThisWorkbook.Sheets("Active_Inv")
tLastRow = mws2.Range("A" & Rows.Count).End(xlUp).Row
aLastRow = mws2.Range("U" & Rows.Count).End(xlUp).Row
Do Until aLastRow
mws2.Range("A" & tLastRow + 1) = "=Today()"
mws2.Range("B" & tLastRow + 1) = Format(Now(), "MM/DD/YY")
mws2.Range("C" & tLastRow + 1) = "=Workday(RC[27],10)"
mws2.Range("D" & tLastRow + 1) = "=RC[-1]-RC[-3]"
mws2.Range("E" & tLastRow + 1) = "=IF(RC[-1]<0,""Past Due"",IF(RC[-1]<4,""0 - 3"",IF(RC[-1]<8,""4 - 7"",""8 - 10"")))"
mws2.Range("S" & tLastRow + 1) = "=IF(RC[-7] = ""Y"",""Complete - Exception"",IF(RC[-5]="""",""Pending Initial Review"",IF(RC[-3]="""",""Pending Secondary Review"",IF(RC[-2]="""",""Pending ILQA Review"",IF(RC[-1]="""",""Pending EOD Completion"",""Complete"")))))"
Loop
Application.ScreenUpdating = True
End Sub

What am I missing?

 

[igold]

I believe you are missing a condition which makes the "Do Until" stop.

Do Until aLastRow    'Until aLastRow is what

You could probably be better served with a For/Next loop.

I did not look at the code in between the Do and Loop section, only what caught my eye is that there is nothing to trigger the "Do"

I hope this makes sense...

 

[offthelip]

It is your loop control which is wrong:
the "Do until" construct is expecting a boolean which if it is false it carries on looping, when it is true it exits the loop. you are handing it a number (alastrow) which excel will treat as "False" if it is zero and "true" if it is anything else. so you never execute the loop
I am not sure how you are trying to control the loop but the code inside the loop doesn't look correct to me either: the address :Range("A" & tLastRow + 1) wil always be the same cell every loop unless you update tlastrow which you currently aren't.
I suggest you read up about how to use loop in vba:
https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/do-loop-statement

 

[reberryjr]

I'll read up on the link you provided. I did make sure that the code was updating the fields correctly, before adding the do until and the loop, so that part is good. Thank you for the response and the info to research.

 

[reberryjr]

Ok, so it would appear that the For/Next might be the better option. My struggle (that I've been working on for over 3 hours this morning) is being able to identify the number of times the code has to repeat. These lines of code are recognizing the correct rows, but I can't find anything on the web that instructs me how to find the difference in rows.

If tLastRow = row #595 and aLastRow = row #600 , then I need the code to repeat 5 times.

tLastRow = mws2.Range("A" & Rows.Count).End(xlUp).Row
aLastRow = mws2.Range("U" & Rows.Count).End(xlUp).Row

Apologies if this is simple, and I appreciate the feedback.

 

[offthelip]

is this what you are trying to do? This write a new data in on rows 596 to 600

Sub CompleteSetup()
Application.ScreenUpdating = False
Dim mws2 As Worksheet
Set mws2 = ThisWorkbook.Sheets("Active_Inv")
tlastrow = mws2.Range("A" & Rows.Count).End(xlUp).Row
aLastRow = mws2.Range("U" & Rows.Count).End(xlUp).Row
For i = tLastRow + 1 To alastrow
mws2.Range("A" & i) = "=Today()"
mws2.Range("B" & 1) = Format(Now(), "MM/DD/YY")
mws2.Range("C" & i) = "=Workday(RC[27],10)"
mws2.Range("D" & i) = "=RC[-1]-RC[-3]"
mws2.Range("E" & i) = "=IF(RC[-1]<0,""Past Due"",IF(RC[-1]<4,""0 - 3"",IF(RC[-1]<8,""4 - 7"",""8 - 10"")))"
mws2.Range("S" & i) = "=IF(RC[-7] = ""Y"",""Complete - Exception"",IF(RC[-5]="""",""Pending Initial Review"",IF(RC[-3]="""",""Pending Secondary Review"",IF(RC[-2]="""",""Pending ILQA Review"",IF(RC[-1]="""",""Pending EOD Completion"",""Complete"")))))"
Next i
Application.ScreenUpdating = True
End Sub

 

[reberryjr]

YES!!!! That's it!!!

I haven't used For like that before, and was struggling with it. So that I understand, my LastRow coding is fine. What we're saying is, i is almost like saying it's a row. So, for the first row (or i), which is really the tLastRow+1, do this....and repeat until we hit the aLastRow.

Correct?

I want to make sure I understand it, so I can leverage it in the future.

 

[offthelip]

One thing you need to be clear about is what is a variable and what is a constant ( even if it is only temporarily a constant) Alastrow and Tlastrow are both constants at the time of the start of the loop, they reflect the numbers of cells as measured by your statements with End(xlup) . When you are going round a loop where you are intending to write into each row you need a loop variable to determine the row number for each itearation round the loop, this is what i is. It is a loop variable. It was precisely the lack of a loop variable that caused me to say:

I am not sure how you are trying to control the loop but the code inside the loop doesn't look correct to me either: the address :Range("A" & tLastRow + 1) wil always be the same cell every loop unless you update tlastrow which you currently aren't.

The easiest way of generating a loop variable is to use the

for i = 1 to 10

format. 95% of the loops that I write use this format. the default step is +1, but you can loop backwards which is useful at times.

for i = 10 to 1 step -1

You can do double loops

for i = 1 to 10
 for j = 3 to 20
   ' some code 
next j
next i

you can do loops that increment a variable

 for distance = 65.5 to 96.3 step 0.1
           next distance

 

[reberryjr]

@offthelip, I appreciate the explanation. I hadn't had to use the loop variable before (or loops much at all), so I was naïve in how it was to be used. I saw it in a ton of code when searching for my own answer, but didn't see anything that explained what it was. Your explanation helps tremendously! I do have a separate question, related to the same topic. I want to add some data validation (drop downs) along with the other updates I'm making. I have the following code, but it is returning an "invalid or unqualified reference" error.

mws2.Range("F" & i) = .Validation
    .Add Type:=xlValidateList, Operator:=xlEqual, Formula1:="=IProcessor", AlertStyle:=xlValidAlertStop
    .ErrorTitle = "Invalid Entry"
    .ErrorMessage = "Please select a valid name from the drop down box."

With all of the examples that I'm seeing on the web, they are all updating a specified range, using With. Note that IProcessor, is a named range, where the drop down values are stored. I debated on implementing a workaround, where I would go to the new last row (after all of the updates are made), and then copy validation down; but I'm trying to stay away from workarounds, as they won't help me learn the proper ways.

 

[offthelip]

try this ( note i don't have mws2 defined so this operates on the active worksheet change this as required)

 Range(Cells(i, 6), Cells(i, 6)).Select    
With Selection.Validation
    .Add Type:=xlValidateList, Operator:=xlEqual, Formula1:="=IProcessor", AlertStyle:=xlValidAlertStop
    .ErrorTitle = "Invalid Entry"
    .ErrorMessage = "Please select a valid name from the drop down box."
End With