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?

 

[JackDanIce]

No problem, seemed to suggest you were open to suggestions!

In my code, I used DATEAdd. Are you saying I should use datediff instead of dateAdd?

I mean, what is the difference between column D and what code does with DATEAdd?

Untested and only considers 2 cases, >= or < however, try:

Private Sub Workbook_Open()

    SetUp_On_Open
    
    'Something random formatting stuff, could be included in SetUp_On_Open
    mCardData.MyCarsCombined
    
End Sub

Private Sub SetUp_On_Open()

    Dim Last_Row    As Long
    Dim sData       As String
    
    'Collect Dasbhoard data and pass as array to Check_Data function
    With Sheets("Dashboard")
        'Set focus to Dashboard sheet if not active sheet
        If ActiveSheet.Name <> .Name Then .Select
        
        'Find last row in column A with a value
        Last_Row = .Cells(.Rows.Count, 1).End(xlUp).row - 1
        
        'If this is not row 1 then function check_data(with data range) and return relevant msg
        If Last_Row > 0 Then
            sData = Check_Data(.Cells(2, 1).Resize(Last_Row, 5).Value)
            'If sData is not empty
            If sData <> "" Then
                MsgBox sData, vbxclamation + vbOKOnly, "Car Checks Due"
            Else
                MsgBox "No reminders today!", vbInformation + vbOKOnly, "No Reminders Today"
            End If
        End If
        
        'select B2
        .Cells(2, 2).Select
    End With
    
End Sub

Private Function Check_Data(ByRef data As Variant) As String
'Function to evaluate data and return a string

    Dim x               As Long
    Dim temp_string     As String
    Dim expire(1 To 3)  As String
    Dim dTest           As Date
    Const dFORMAT       As String = "mm-dd-yyyy"
    
    'An array to hold different values of Expire(s/d)
    expire(1) = " expires": expire(2) = " expired"
    
    'Loop over function argument data (dataay)
    For x = LBound(a, 1) To UBound(a, 1)
        'Default value for expires variable
        expire(3) = expire(2)
        
        'Test if TRUE and some date difference, replace required expire(3) term
        If data(x, 5) And DateAdd("d", a(x, 4) * -1, a(x, 3)) >= Date Then expire(3) = expire(1)
        
        'Create a temporary string
        s = Check_Data & data(x, 1) & " - " & data(x, 2) & expire(3) & " on " & Format(a(x, 3), dFORMAT) & " in " & data(x, 4) & IIf(data(x, 4) <> 1, "days", "day") & " time" & vbCrLf
        
        'Adjust for expire(3) i.e. "Expired"
        If expire(3) = " expired" Then s = Trim(Replace(s, "time", "ago"))
        
        'Build string for output
        Check_Data = s
    Next x
    
    'If Check_Data is empty, then default
    If Check_Data = "" Then Check_Data = "No reminders today!"
    
End Function

 

[erutherford]

Wow, this will take me a little time to digest, but I will report back to you. And yes it is easier to follow with comments above the code.
thanks again and talk again soon.

 

[erutherford]

I mean, what is the difference between column D and what code does with DATEAdd?

Just thought I would answer your question even though we are headed in a different direction.

Col D's value is set by the user through the form. Its the number of days they what to be notified before the due date. If I read my code correctly, dtTest is the results of DateAdd taking that value (Col.D), subtracting it from the DueDate (Col C).
Then dtTest is used in the formula. It works fine, but on only one row of data. It needs to work on multiple rows which different names.

Private Sub workbook_open()
Dim cCar As Range, sh As Worksheet, strAlert As String, dtTest As Date, wkb As Workbook
If dtTest >= Date And cCar.Offset(0, 4).Value Then
        
            'vbCrLf puts a newline (Carriage Return, Line Feed) in
        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
                                          
 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

 

[JackDanIce]

This part in my code:

data(x, 5) And DateAdd("d", a(x, 4) * -1, a(x, 3)) >= Date

is doing that, without using dtTest as a named variable.

In formula, you are saying:
If Ex = TRUE AND DateAdd("d", Dx * -1, Cx)) >= Date Then

Where x is the row number changing for columns C, D, E

If your columns do not move, why do you use offset from A instead of referencing the columns themselves in your code?
What is different between D1 and A1.Offset(,3) if column headers do not change?

 

[erutherford]

that code was built for me based on a request through the forum. In fairness to the supplier it probably had more to do with my explaination of what I wanted. So I can't say why to your question.

 

[JackDanIce]

Fair enough - as long as you're learning!

No probs if you can't but it would be one way to review the code you were given, understand it and figure out if you can/should change it or not to fit your needs