breynolds0431
Active Member
- Joined
- Feb 15, 2013
- Messages
- 303
- Office Version
- 365
- 2016
- Platform
- Windows
Hello. I have a userform that should check to see if the new entry is already logged in the data table. This duplicate check evaluates the account number, Date, and ID letter and then searches the sheet's Column A to see if it's listed. The sheet has this information concatenated in column A of each data row.
The problem I'm running into is the date piece. On the sheet, the date is formatted as a date (not text) and is a date serial in the concatenate cell. The userform views this date as a text string, so a duplicate is never found. I tried to convert the date from text to date and then attempted to use DateValue to obtain the serial (i.e.; DateValue(CDate(Me.FYEDate))), but it seems to always return the m/d/yyyy format.
Any thoughts on how I can convert the Me.FYEDate to a serial number to work with the sheet? Also, I cannot modify the sheet to convert the dates to text.
Thank you for your time.
The problem I'm running into is the date piece. On the sheet, the date is formatted as a date (not text) and is a date serial in the concatenate cell. The userform views this date as a text string, so a duplicate is never found. I tried to convert the date from text to date and then attempted to use DateValue to obtain the serial (i.e.; DateValue(CDate(Me.FYEDate))), but it seems to always return the m/d/yyyy format.
Any thoughts on how I can convert the Me.FYEDate to a serial number to work with the sheet? Also, I cannot modify the sheet to convert the dates to text.
Thank you for your time.
VBA Code:
'will check for duplicates
Dim lRow As Long, ws As Worksheet, c As Range
Set ws = Worksheets("log")
lRow = ws.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
With ws.Range(ws.Cells(3, 1), ws.Cells(lRow, 1))
Set c = .Find(Val(Me.Acct) & Me.FYEDate.Text & Me.ID.Text, LookIn:=xlValues, LookAt:=xlWhole)
End With
If c Is Nothing Then
Else
MsgBox Me.Acct.Text & " " & Me.FYEDate.Text & " " & Me.ID.Text & " has already been logged. " & _
"Please verify the details and re-enter, if necessary.", vbOKOnly, "Duplicate Entry"
Me.ID.SetFocus
End If