Rlb53
10-06-2012, 05:28 PM
Prior to inserting Data Into a Worksheet, I am searching to see if the data entered may cause for a duplication.
What I find is that if I have a value of 6102 in the column and I prepare to enter data with a value of 102 it identifies it as a duplicate.
I also find that if I enter a unique value, it still returns a message that the Data Exists, but... it also throws and error when attempting to perform the .find command... hence insertion of the "On Error GoTo Line1"
The Message Box Returns the Information that Exists in the DataBase so that the Values can be Evaluated prior to a decision being made since it is possible that a Duplicate Trailer Number could be onsite from a different Vendor.
Any Suggestions ?? Thank You !!
With ActiveSheet
Dim X As Long
Dim LastRow As Long
Dim Reply As Long
Dim Reply2 As Long
On Error GoTo line1
LastRow = Range("A1000000").End(xlUp).Row
For X = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A1:A" & X), Range("A" & X).Text) > 1 Then
Reply = MsgBox(" I Am Showing That Trailer " & _
MBFTrailerNumber.Value & " Weighed In At " & _
Sheets("boltempdata").Range("a:a").Find(MBFTrailerNumber.Value).Offset(0, 11).Value & " Lbs on " & _
Sheets("boltempdata").Range("a:a").Find(MBFTrailerNumber.Value).Offset(0, 2).Value & " at " & _
Sheets("boltempdata").Range("a:a").Find(MBFTrailerNumber.Value).Offset(0, 3).Value & " " & _
" Do You Wish To Use This Data? ", vbYesNo)
Select Case Reply
Case vbYes
MsgBox (" You Will Return To The Main BOL Sheet. The Current Information Will Not Be Saved. ")
Case vbNo
Reply2 = MsgBox("This Action Will Delete The Previous Information Stored and Replace It With Current. Do You Wish To Continue? ", vbYesNo)
End Select
Select Case Reply2
Case vbYes
MsgBox " "
Case vbNo
MsgBox ""
End Select
Exit Sub
End If
Next X
End With
What I find is that if I have a value of 6102 in the column and I prepare to enter data with a value of 102 it identifies it as a duplicate.
I also find that if I enter a unique value, it still returns a message that the Data Exists, but... it also throws and error when attempting to perform the .find command... hence insertion of the "On Error GoTo Line1"
The Message Box Returns the Information that Exists in the DataBase so that the Values can be Evaluated prior to a decision being made since it is possible that a Duplicate Trailer Number could be onsite from a different Vendor.
Any Suggestions ?? Thank You !!
With ActiveSheet
Dim X As Long
Dim LastRow As Long
Dim Reply As Long
Dim Reply2 As Long
On Error GoTo line1
LastRow = Range("A1000000").End(xlUp).Row
For X = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A1:A" & X), Range("A" & X).Text) > 1 Then
Reply = MsgBox(" I Am Showing That Trailer " & _
MBFTrailerNumber.Value & " Weighed In At " & _
Sheets("boltempdata").Range("a:a").Find(MBFTrailerNumber.Value).Offset(0, 11).Value & " Lbs on " & _
Sheets("boltempdata").Range("a:a").Find(MBFTrailerNumber.Value).Offset(0, 2).Value & " at " & _
Sheets("boltempdata").Range("a:a").Find(MBFTrailerNumber.Value).Offset(0, 3).Value & " " & _
" Do You Wish To Use This Data? ", vbYesNo)
Select Case Reply
Case vbYes
MsgBox (" You Will Return To The Main BOL Sheet. The Current Information Will Not Be Saved. ")
Case vbNo
Reply2 = MsgBox("This Action Will Delete The Previous Information Stored and Replace It With Current. Do You Wish To Continue? ", vbYesNo)
End Select
Select Case Reply2
Case vbYes
MsgBox " "
Case vbNo
MsgBox ""
End Select
Exit Sub
End If
Next X
End With