Run-time error 13

[josros60]

hi,


I have this table and the code below but when run it get run-time error 13 mismatch, please help.

[TABLE="width: 1327"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Contact[/TD]
[TD]A/C[/TD]
[TD]Date[/TD]
[TD]Due Date[/TD]
[TD]Days[/TD]
[TD]Inv#[/TD]
[TD]$[/TD]
[TD]Due in days[/TD]
[TD]Email[/TD]
[/TR]
</tbody>[/TABLE]

***Note: due date is column "F"

Sub popup()
Dim lstRow As Long
Dim i As Long
Dim msg As String
msg = "The following items are almost due" & vbCrLf & vbCrLf
lstRow = Range("F" & Rows.Count).End(xlUp).Row
For i = 3 To lstRow
    If Range("F" & i) - Date <= 6 Or Range("F" & i) - Date < 0 Then
        msg = msg & Range("A" & i).Value & " due in " & Range("F" & i) - Date & " Days" & vbCrLf
    End If
Next i
MsgBox msg
Call settimer
End Sub
 
Sub settimer()
Application.OnTime Now + TimeValue("02:00:00"), "popup"
End Sub

thank you

 

[Trebor76]

Hi josros60,

There are entries in Col. F that are not dates. Even strings that look like dates will error out as will errors like #N/A. Try this where I've used the IsDate VBA function to test is the cell is a date before executing further and used the CDate VBA function for strings that look like dates to be treated as dates:

Option Explicit
Sub popup()
Dim lstRow As Long
Dim i As Long
Dim msg As String
msg = "The following items are almost due" & vbCrLf & vbCrLf
lstRow = Range("F" & Rows.Count).End(xlUp).Row
For i = 3 To lstRow
    If IsDate(Range("F" & i)) = True Then
        If CDate(Range("F" & i)) - Date <= 6 Or CDate(Range("F" & i)) - Date < 0 Then
            msg = msg & Range("A" & i).Value & " due in " & CDate(Range("F" & i)) - Date & " Days" & vbCrLf
        End If
    End If
Next i
MsgBox msg
Call settimer
End Sub
Sub settimer()
Application.OnTime Now + TimeValue("02:00:00"), "popup"
End Sub

Regards,

Robert

 

[josros60]

Thank you so much, worked fantastic.

 

[josros60]

Hi,


Sorry I forgot to ask,


how can i make the popup reminder only shows if is due in 4 days and also when i open the workbook.


guess need to modify this line of code, any help please.

Sub settimer()
Application.OnTime Now + TimeValue("02:00:00"), "popup"
End Sub

thanks again

 

[Trebor76]

how can i make the popup reminder only shows if is due in 4 days

To be honest I'm confused by your code so I can't help with this I'm afraid There will be someone on the forum that will be able I'm sure

and also when i open the workbook

So the code will need to be a workbook event macro. To do this follow these five steps:

1. Copy (Ctrl + C) the body of the macro (i.e. exclude the name and end sub part of the macro) you want to run when the workbook is opened
2. Open the Visual Basic Editor (Alt + F11) while on the the workbook in question
3. Double click on ThisWorkbook in the Project - VBAProject (left-hand side) window
4. Ensure your code from step 1 goes into the Workbook_Open event macro
5. From the File menu select Close and Return To Microsoft Excel

Regards,

Robert