Reminder formula

[erutherford]

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

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?

 

[AlphaFrog]

I thought I could just add Else, cut and paste and reverse the "<=" to ">=", but I get a "loop without Do"

Can you show what you added and where?

 

[erutherford]

Can you show what you added and where?

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
 
'added it here

else

 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
                       
    
    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

 

[AlphaFrog]

Try this...

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

'added it here

ElseIf 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
                      
   
    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

 

[erutherford]

That solved that problem, thank you. I'll read a little about the diff between Else and ElseIF.

Can you briefly tell me what is happening here :

dtTest = DateAdd("d", cCar.Offset(0, 3) * -1, dtTest)
[/code}

Im not sure what the " *-1" is doing?

 

[MARK858]

Im not sure what the " *-1" is doing?

It just changes whatever the positive value in cCar.Offset(0, 3) is to a minus number (or a minus number to a positive although I doubt that is what is in cCar.Offset(0, 3)).

 

[erutherford]

Thank you. The code works fine when only one record is in the worksheet. Once a second record is entered, the result is both reminders are the same.
The worksheet is setup as follows
A = Car Name
B = Reminder description
C = Due Date
D = Alert Days (days before the due date)
E = Status (True/False)

Any data set that meets the "If statement" should read "expires in "x" days"
Any data set the meets the ElseIf statement should read "expired "X" days ago"

When two or more rows of data is entered then all row copy the first rows results.

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 D - 3 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
 'Changes to past due
 
    ElseIf 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) & " " & "expired on " & Format(cCar.Offset(0, 2), "mm-dd-yyyy") & " - " & cCar.Offset(0, 3) & " day(s) ago " & vbCrLf 'vbCrLf puts a newline (Carriage Return, Line Feed) in
    
    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

 

[AlphaFrog]

That solved that problem, thank you. I'll read a little about the diff between Else and ElseIF.

You're welcome.

You tried to do this in your original code, but omitted the 2nd End If.

If Blah Then

Else
    If Blah2 Then

    End If
End If

This is the same as above

If Blah Then

ElseIf Blah2 Then

End If

 

[JackDanIce]

@erutherford Curious about code, formula and logic used

IF datediff is >= Today's date Then
'Do something
Else
'Do something else
End if

However, your code is:

IF datediff is >= Today's date Then
'Do something
ElseIf datediff is <= Today's date Then
'Do something else
End if

This implies, when datediff = Today's Date, it will execute TRUE part and not "expires 0 days ago". Doesn't that make ElseIf redundant?

Here, seems datediff should be:
- Greater than or equal to today [test case]
- otherwise, Less than today [default case]

Your output messages also only deal with 2 cases:
- Expires [test case]
- Expired [default case]

And no 3rd case "Expires today". Are you sure you need ElseIf in your code and not Else?

You could test for this in your loop e.g. If cCar.Offset(0, 3).Value <> 0

Separately, if cCar.Offset(0, 3).value is a number that indicates number of days for expiration status, I'm guessing it's formula driven?
If so, you already have values in column D, do you need to use DateDiff in the code to figure out days for expiration status?

Also, in your code, try moving all your comments above the line they're for, not to the right - makes it easier to read with less horizontal scrolling

 

[erutherford]

@erutherford Curious about code, formula and logic used

IF datediff is >= Today's date Then
'Do something
Else
'Do something else
End if

However, your code is:

IF datediff is >= Today's date Then
'Do something
ElseIf datediff is <= Today's date Then
'Do something else
End if

This implies, when datediff = Today's Date, it will execute TRUE part and not "expires 0 days ago". Doesn't that make ElseIf redundant?

Here, seems datediff should be:
- Greater than or equal to today [test case]
- otherwise, Less than today [default case]

Your output messages also only deal with 2 cases:
- Expires [test case]
- Expired [default case]

And no 3rd case "Expires today". Are you sure you need ElseIf in your code and not Else?

You could test for this in your loop e.g. If cCar.Offset(0, 3).Value <> 0

Separately, if cCar.Offset(0, 3).value is a number that indicates number of days for expiration status, I'm guessing it's formula driven?
If so, you already have values in column D, do you need to use DateDiff in the code to figure out days for expiration status?

Also, in your code, try moving all your comments above the line they're for, not to the right - makes it easier to read with less horizontal scrolling

Thanks Jack for the comments. I don't write code daily, but do enjoy it when I get to, so all comments are really helpful.
I really like the 3rd case logic and will try to incorporate that.
In my code, I used DATEAdd. Are you saying I should use datediff instead of dateAdd?