CODEWITHSUNDEEP
vbadateexcel
datacentric
datacentric

Reputation: 135

Code not recognizing unique values in excel

I am working on a code in excel that uses a unique number and associated 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

RESOLVED: The issue I had and was resolved was that that if there is a record in excel file with ID 1234567 and date 10/10/2013, and I am trying to enter another record with ID 1234 and date 10/10/2013, the code still gives a message that " the record already exists". Its not looking at the entire ID value. Even if the part of existing ID matches the new ID, the code will not recognize it as a new ID.

NEW: Now I am having issues with date. If I have a same ID and same date (like 12/12/2012) then the code would recognize that as a same record and will give a message that record already exists. Howvere, if the dates are in format e.g. 1/1/2013 or 4/15/2012 or 4/1/2013, the code doesnt recognize it as the same date.

I hope my question is making sense. Please let me know if I can clarify.

Thanks a ton for your help.

Upvotes: 0

Views: 335

Answers (2)

paddy
paddy

Reputation: 63481

One problem is that you are doing two unrelated Find calls. If you have an ID 1234 and date 10/10/2013 that appear on different lines, this will still assume it's a duplicate.

It could also be that Find is doing partial matches. I've never used the excel VBA interface so I don't know what the defaults are. Try adding LookAt:=xlWhole to the parameter list.

What you will need to do is link your searches. Just search on one of the records. You will have to go through all matches and then compare the other record from the same row. This is assuming that you want to allow more than one ID per day, and that you can have the same ID on different days.

You can use .Offset(1,0) on a range value returned from Find to return the cell to the right of it.

Upvotes: 0

AndASM
AndASM

Reputation: 10348

Nitpicking and side notes

Your code won't even compile, nevermind run.

Function Record(ID As String, Date As String)

Is not a valid function definition. Date is a reserved keyword and cannot be used as a variable or parameter name.

The answer

Your problem is with Range.Find. As seen here in the documentation Range.Find has a parameter named LookAt which takes a value from the XlLookAt enumeration, either xlPart or xlWhole. I believe find by default uses the last setting from the last search, and when Excel first starts it defaults to xlPart. You of course want to do a search for xlWhole, you want to search for a cell who's entire contents match your search string.

So update your code to something like ID_List.Find(What:=ID, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows) and do the same for the other call to that method.

Upvotes: 1

Related Questions