Search Specific Column for a matched entry

[Guest]

Hello All,

I use the code listed below to insert values entered on a UserForm, into the
next empty row in the 'Stockpile Reading Log'. Everything works great except:

Before the entry on the user form is added to the 'Stockpile' sheet, i want
the system to take the date entered in the 'Reading Date' field of the user
form, and search column 'A' of the 'Stockpile Log'. If the date exists
already in the column, i want the system to alert the user that the date
already exists, and cancel the process. I do not want rows to be entered
into the sheet that contain a date that already exists.

Can anyone help me with this request?

 

[Guest]

What code??? That would help.

 

[Guest]

It's stealth code, Barb!

What code??? That would help.

 

[Guest]

oops...Sorry guys!

Private Sub CmdSubmit_Click()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim sStr As String
Dim rFound As Range
Dim nResult As Long

Set WB = ThisWorkbook
Set SH = WB.Sheets("Stockpile Reading Log")
Set Rng = SH.Columns("A:A")
sStr = Me.dtpReadingDate.Value


Set rFound = SH.Range("A:A").Find(What:=sStr, LookIn:=xlValues)
If rFound Is Nothing Then

If MsgBox("Submit Stockpile Readings to Site Log?", vbYesNo) = vbYes Then

ActiveWorkbook.Sheets("Stockpile Reading Log").Activate
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = dtpReadingDate.Value
ActiveCell.Offset(0, 1) = Me.txtPreparedBy.Value
'NW - #1 North
ActiveCell.Offset(0, 2) = Me.cbo1N1.Value
ActiveCell.Offset(0, 3) = Me.cbo1N2.Value
ActiveCell.Offset(0, 4) = Me.cbo1N3.Value
ActiveCell.Offset(0, 5) = Me.cbo1N4.Value
ActiveCell.Offset(0, 6) = Me.cbo1N5.Value
ActiveCell.Offset(0, 7) = Me.cbo1N6.Value
ActiveCell.Offset(0, 8) = Me.cbo1N7.Value
'NW - #2 North - E
ActiveCell.Offset(0, 9) = Me.cbo2N1E.Value
ActiveCell.Offset(0, 10) = Me.cbo2N2E.Value
ActiveCell.Offset(0, 11) = Me.cbo2N3E.Value
ActiveCell.Offset(0, 12) = Me.cbo2N4E.Value
ActiveCell.Offset(0, 13) = Me.cbo2N5E.Value
ActiveCell.Offset(0, 14) = Me.cbo2N6E.Value
ActiveCell.Offset(0, 15) = Me.cbo2N7E.Value
ActiveCell.Offset(0, 16) = Me.cbo2N8E.Value
....
Else
MsgBox "Your submission request has been cancelled."
End If
End If
End Sub