VBA - Do Until Loop is Not Looping

[nsampair1123]

Here is my code. It stops after it prints the first PDF. Can anyone tell me what I am missing that it is not completing the Do Until...Loop?


Sub MeritCommunication()


Dim irow As Integer
Dim EmployeeName As String
Dim EmployeeID As Double
Dim JobTitle As String
Dim CurrentBaseSalary As Double
Dim IncreasePercent As Double
Dim NewBaseSalary As Double
Dim FileName As String
Dim CheckNewBaseSalary As Double


Worksheets("Master Merit").Select
irow = 15

Do Until IsEmpty(Cells(irow, 1))


'Populate Distribution Sheet



Worksheets("Master Merit").Select
EmployeeName = Cells(irow, 2)
EmployeeID = Cells(irow, 1)
JobTitle = Cells(irow, 3)
CurrentBaseSalary = Cells(irow, 5)
IncreasePercent = Cells(irow, 10)
NewBaseSalary = Cells(irow, 21)


Worksheets("Merit Communication").Select
Range("E7").Value = EmployeeName
Range("E8").Value = EmployeeID
Range("E9").Value = JobTitle
Range("E11").Value = CurrentBaseSalary
Range("E12").Value = IncreasePercent
Range("E13").Value = NewBaseSalary

'Print to PDF

ThisWorkbook.Worksheets("Merit Communication").ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:="C:\Users\NSampair\Box\Working File\Comp\2023 Merit Process\Merit_Letters\" & Range("S8").Value, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

irow = irow + 1

Loop


End Sub

 

[Joe4]

Welcome to the Board!

I am guessing it is looping just fine, but it appears that you may just be overwriting the file each time, as I don't see the file name changing with each loop:

FileName:="C:\Users\NSampair\Box\Working File\Comp\2023 Merit Process\Merit_Letters\" & Range("S8").Value

 

[nsampair1123]

S8 updates each time that E8 updates (Employee ID). The code works just fine when it has this quality check piece at the end of the code, but breaks when I delete it.

Sub MeritCommunication()

Dim irow As Integer
Dim EmployeeName As String
Dim EmployeeID As Double
Dim JobTitle As String
Dim CurrentBaseSalary As Double
Dim IncreasePercent As Double
Dim NewBaseSalary As Double
Dim FileName As String
Dim CheckNewBaseSalary As Double


Worksheets("Master Merit").Select
irow = 5

Do Until IsEmpty(Cells(irow, 1))

'Populate Distribution Sheet

Worksheets("Master Merit").Select
EmployeeName = Cells(irow, 2)
EmployeeID = Cells(irow, 1)
JobTitle = Cells(irow, 3)
CurrentBaseSalary = Cells(irow, 5)
IncreasePercent = Cells(irow, 10)
NewBaseSalary = Cells(irow, 21)

Worksheets("Merit Communication").Select
Range("E7").Value = EmployeeName
Range("E8").Value = EmployeeID
Range("E9").Value = JobTitle
Range("E11").Value = CurrentBaseSalary
Range("E12").Value = IncreasePercent
Range("E13").Value = NewBaseSalary

'Print to PDF

ThisWorkbook.Worksheets("Merit Communication").ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:="C:\Users\NSampair\Box\Comp\2023 Merit Process\Merit_Letters\" & Range("S8").Value, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

'Quality Control

CheckNewBaseSalary = Range("E13").Value
Worksheets("Master Merit").Select
Cells(irow, 21).Value = CheckNewBaseSalary


irow = irow + 1



Loop

End Sub

 

[Joe4]

Without having access to your workbook, where we can see all the structure, data, and formulas, it is hard to tell what the issue might be.

Have you tried stepping through it one line at a time using the F8, so you can see it loop through each step?
If you do that and hover over any variable, you can see its value at that point in time.
This is a great debug technique, and many times the problem becomes evident, as you step through it.

 

[nsampair1123]

Without having access to your workbook, where we can see all the structure, data, and formulas, it is hard to tell what the issue might be.

Have you tried stepping through it one line at a time using the F8, so you can see it loop through each step?
If you do that and hover over any variable, you can see its value at that point in time.
This is a great debug technique, and many times the problem becomes evident, as you step through it.

Thank you - that was helpful. It is not looping because it recognizes the Do Until IsEmpty as True after it runs through the code once.

 

[Joe4]

I have sometimes had issues with using "Do Until..." myself.
Maybe try replacing this:

Do Until IsEmpty(Cells(irow, 1))

with something like this:

Do
    If Cells(irow, 1) = "" Then Exit Do

 

[nsampair1123]

I have sometimes had issues with using "Do Until..." myself.
Maybe try replacing this:

Do Until IsEmpty(Cells(irow, 1))

with something like this:

Do
    If Cells(irow, 1) = "" Then Exit Do

Thank you - this gives me the same result as the Do Until Is Empty where it is evaluating the statement as true and exiting the loop. Ugh So frustrating.

 

[Joe4]

I think I know why, It is because at the end of your code, you are switching to the "Merit Communication" sheet. But the "irow" you want to check is on the "Master Merit" sheet.
So you need to switch back to that at the beginning of your code, i.e.

Do
    Worksheets("Master Merit").Select
    If Cells(irow, 1) = "" Then Exit Do

or

Do
    Sheets("Master Merit").Cells(irow, 1) = "" Then Exit Do

 

[nsampair1123]

I think I know why, It is because at the end of your code, you are switching to the "Merit Communication" sheet. But the "irow" you want to check is on the "Master Merit" sheet.
So you need to switch back to that at the beginning of your code, i.e.

Do
    Worksheets("Master Merit").Select
    If Cells(irow, 1) = "" Then Exit Do

or

Do
    Sheets("Master Merit").Cells(irow, 1) = "" Then Exit Do

That was it - thank you so so so much!!!

 

[Joe4]

You are welcome.

Yeah, that is common when bouncing around between sheets. If you have not qualified your ranges with sheet names, you may not be on the sheet you think you are when a certain line of code.
Often times, I will move my code to one monitor while I watch the workbook on the other monitor. Then as I step through the code, I can see what sheet I am on when that line of code runs (and then it often becomes obvious what the problem is!).