erutherford
Active Member
- Joined
- Dec 19, 2016
- Messages
- 449
This code performs fine. There is just a small tweak that I would like to do. I was able to modify the code to work separately, but not together. I thought it was just a simple If Else statement, but I get a loop error.
Here is the code
I thought I could just add Else, cut and paste and reverse the "<=" to ">=", but I get a "loop without Do"
Any ideas?
Here is the code
Code:
Private Sub workbook_open()
Dim cCar As Range, sh As Worksheet, strAlert As String, dtTest As Date, wkb As Workbook
'This sets the program & insures Col.D is formated correctly
Set sh = ThisWorkbook.Worksheets("CReminders")
Set wkb = ThisWorkbook
wkb.Sheets("Dashboard").Activate ' opens dashboard first, then any popups closed, dashboard is first up.
wkb.Sheets("Dashboard").Range("B2").Select
Call MCarData.MyCarsCombined ' Insures cYear, cBrand & cLicense are formated in Col.D ("MyCars)
' *****************************************************************************************
'This checks for expired reminders
Set cCar = sh.Cells(2, 1) 'cCar is c=column & Cars is column description
' ****************************************************************************************** Reminder #1
Do Until cCar.Value = "" 'loop until the first row with no car name
dtTest = cCar.Offset(0, 2) 'DDate is 2 columns over from the Car Name - Column C
dtTest = DateAdd("d", cCar.Offset(0, 3) * -1, dtTest) 'Deduct the number of days from column E - 4 columns over from the Car name
If dtTest <= Date And cCar.Offset(0, 4).Value Then 'This checks the adjusted date, and TRUE/FALSE (cCar.Offset(0, 4)= 4 columns over from cCar
strAlert = strAlert & cCar.Value & " - " & cCar.Offset(0, 1) & " " & "expires on " & Format(cCar.Offset(0, 2), "mm-dd-yyyy") & " in " & cCar.Offset(0, 3) & " day(s) " & vbCrLf 'vbCrLf puts a newline (Carriage Return, Line Feed) in
'CarID ************ Description **************************************** DDate ************************************ ADate
End If
Set cCar = cCar.Offset(1, 0) 'Moves to the next row
Loop
If strAlert = "" Then strAlert = "No reminders today!"
MsgBox strAlert
Set sh = Nothing
Set wkb = Nothing
End Sub
I thought I could just add Else, cut and paste and reverse the "<=" to ">=", but I get a "loop without Do"
Any ideas?