Date Serial from Userform Textbox

breynolds0431

Board Regular
Joined
Feb 15, 2013
Messages
78
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.

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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Forum statistics

Threads
1,143,837
Messages
5,721,088
Members
422,339
Latest member
SHIVATVM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top