CODEWITHSUNDEEP
vbadateexcelexcel-2007
datacentric
datacentric

Reputation: 135

VBA Code not recognizing dates in certain format

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.

Upvotes: 0

Views: 2918

Answers (1)

Will Rickards
Will Rickards

Reputation: 2786

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;@"

Upvotes: 1

Related Questions