VBA Code not recognizing dates in certain format

Question

I am working on a code that uses a unique ID number and associate date to see if a same record already exists in the worksheet. Here is my code:

Part of first code:

Else
   'If all the data has been entered, go to New_Record
   Check_Record ID:=Sheets("Information").Range("A1").Value, vDate:=Sheets("Information").Range("A2").Value
End If
End Sub

Second code that follows first code:

Function Record(ID As String, vDate As String)

    Dim Current_ID_List As Range
    Dim vCaseWasFound, vDateWasFound, vLastDataRow As Range
    Dim DestinationRow As Integer
    Dim Go_ahead_msg As String

    Set ID_List = Sheets("Records").Range("A:A")
    Set Date_List = Sheets("Records").Range("D:D")

    '-- determine whether record exists
    Set vCaseWasFound = ID_List.Find(What:=ID, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
    Set vDateWasFound = Date_List.Find(What:=vDate, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
    Set vLastDataRow = Sheets("RawData").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows)

    If Not vCaseWasFound Is Nothing And Not vDateWasFound Is Nothing Then
        Go_ahead_msg = "The record already exists."
    Else
        Go_ahead_msg = "This is a new record."
    End If

    If MsgBox(Go_ahead_msg, vbQuestion + vbYesNo) = vbYes Then
    New_Record
    Sheets("Sheet1").Activate
    Else
        With Sheets("Records")
        .Activate
        .Range("A1").Select
    End With

       End If

End Function

I am having issues with code not recognizing same dates in certain formats. If I have a same ID and same date (in format like 12/12/2012) then the code would recognize that as a same record and will give a message that "record already exists". However, if the dates are in format e.g. 1/1/2013 or 4/15/2012 or 4/1/2013, the code doesn't recognize it as the same date.

Will Rickards · Accepted Answer · 2013-11-11 22:32:36Z

1

That is because it is a string comparison and not a date comparison.

You won't be able to use the Range.Find method if you want to search for dates. You'd have to convert the string to a date and then search the range converting each value to a date and then comparing.

You may be able to format the date column first and then do the search. That might work if you formatted them all with MM/DD/YYYY. Then you could compare the strings. But you'd also have to format the one you have passed in.

Formatting the string value to a date:

Set rngMatch = rngSearchMe.Find(What:=Format(strDateValue, "MM/DD/YYYY"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)

Formatting the range to mm/dd/yyyy:

   Set rngSearchMe = Sheets("Sheet1").Range("D:D")
   rngSearchMe.NumberFormat = "mm/dd/yyyy;@"

edited Nov 11, 2013 at 22:32

answered Nov 11, 2013 at 14:54

Will Rickards

2,8112 gold badges20 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

datacentric Over a year ago

Thanks Will. All the date columns in the excel file have been formatted as MM/DD/YYYY. Do I need to change anything in the code? If so, could you please tell me what do I need to change?

datacentric Over a year ago

Should I change "vDate As String" to "vDate As Date"?

datacentric Over a year ago

I made the following changes to my code and it seems to be working fine for dates like 12/12/2012 and 2/15/2012 but not for dates like 1/1/2013 or 1/4/2012. Any suggestions? The changes are: "vDate As Date" and "Set vDateWasFound = .........LookAt:=xlDate, SearchOrder..."

Collectives™ on Stack Overflow

VBA Code not recognizing dates in certain format

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related