Using VBA to search entire column for entered date

[SMit07]

Hi,

I am trying to write a piece of code to prevent duplicate date entry. The document is designed to have new data entered at the end of each work day (Saturdays included). Sundays and public holidays not included. The way I would like it done is when the user runs the macro, a inputbox appears and asks the user to enter the date ("DD/MM/YYYY"). I can't for the life of me work out how to implement a duplicate entry detection system.

My idea to solve this is to search the entire column which contains the date (G) for the entered date.
I've been googling for a couple of hours now with no solution.
This is the closest I've gotten which still does not work.

newdate = Application.InputBox("Enter the date you would like to Add (DD/MM/YYYY)", "Day Diary Addition", FormatDateTime(Date, vbShortDate), Type:=1)
DateFINDER = Application.Match(newdate, Range("G:G"), 0)

If newdate = 0 Then 'prevents no input being entered
    MsgBox "Entered Date not valid"
    Exit Sub
End If

'checks to see if date has already been inserted
Set foundcell = ActiveSheet.Columns(7).Find(newdate, LookIn:=xlValues, lookat:=xlWhole)
If Not foundcell Is Nothing Then
    MsgBox "Duplicate Entry Warning"
    End Sub
End If

Note: The date is stored as a value i.e. 2nd of Jan 2020 (2/1/2020) = 43832

I am quite new to this so any help is appreciated ! Thanks!

 

[footoo]

Try this :

Sub New_Date()
Dim newDate$, dateFINDER As Variant
On Error Resume Next
newDate = InputBox("Enter the date you would like to Add (DD/MM/YYYY)", "Day Diary Addition", FormatDateTime(Date, vbShortDate))
If Not IsDate(newDate) Or Not newDate Like "*/*/*" Then
    MsgBox "Wrong date format"
    Exit Sub
End If
On Error GoTo 0
dateFINDER = Application.Match(CLng(CDate(newDate)), Range("G:G"), 0)
If Not IsError(dateFINDER) Then MsgBox "Duplicate Entry Warning"
End Sub

 

[SMit07]

Worked like a charm, Just had to add in this to convert it to a double. Thanks so much!

newdateDBL = CDbl((CDate(newDate)))

 

[footoo]

I don't see why you need to use CDbl instead of CLng.

The following macro gives a warning message re duplicates and prevents input of Sundays, holidays, and dates earlier than one year ago.
You will need to have a sheet named "Holidays" (can be hidden) that lists the holiday dates in column A.

Sub New_Date()
Dim newDate, nDte As Date, earliestDate As Date
earliestDate = Date - 365 'Within pevious one year from today. Change as required
newDate = Application.InputBox("Enter the date you would like to Add (DD/MM/YYYY)", "Day Diary Addition", FormatDateTime(Date, vbShortDate))
If newDate = 0 Then
    Exit Sub
ElseIf newDate = "" Then
    MsgBox "No date entered"
    Exit Sub
ElseIf Not IsDate(newDate) Or Not newDate Like "*/*/*" Then
    MsgBox "Wrong date format"
    Exit Sub
End If
nDte = DateValue(newDate)
If nDte < earliestDate Then
    MsgBox "The date entered is before 1/1/2020 - not allowed"
    Exit Sub
ElseIf Weekday(nDte) = 1 Then
    MsgBox "The date entered is a Sunday - not allowed"
    Exit Sub
ElseIf Not IsError(Application.Match(CLng(nDte), Sheets("Holidays").[A:A], 0)) Then
    MsgBox "The date entered is a Holiday - not allowed"
    Exit Sub
ElseIf Not IsError(Application.Match(CLng(nDte), Range("G:G"), 0)) Then
    MsgBox "Duplicate Entry Warning"
End If
End Sub