PDA

View Full Version : Solved: Identifying Duplicates Prior to Data Entry.



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

Rlb53
10-06-2012, 08:19 PM
I made a change that seems to have rid the error message when unique values are entered. I have also determined that the CountIf Portion is working correctly.

The error I am needing to correct is the returned value with the .find command(s).

If I have a countif value of "8", and "8" actually exists in the column with associated Line Data, the messagebox is returning the Line Data from the first value encountered in the column, whether it be 28, 182, 1280, etc.

Is there a way to express the value with textbox "MBFTrailerNumber" as an absolute for the .Find command as expressed below ?

Thanks Again !




With ActiveSheet

Range("a1").Value = MBFTrailerNumber

Dim x As Double
Dim Reply As Long
Dim Reply1 As Long
Dim Reply2 As Long

x = Application.WorksheetFunction.CountIf(Range("A:A"), MBFTrailerNumber.Text)
If x > 1 Then
Reply = MsgBox(" I Am Showing That Trailer " & _
Sheets("boltempdata").Range("a:a").Find(MBFTrailerNumber.Text).Offset(0, 0).Value & " Pulled By " & _
Sheets("boltempdata").Range("a:a").Find(MBFTrailerNumber.Text).Offset(0, 6).Value & " Weighed In At " & _
Sheets("boltempdata").Range("a:a").Find(MBFTrailerNumber.Text).Offset(0, 11).Value & " Lbs on " & _
Sheets("boltempdata").Range("a:a").Find(MBFTrailerNumber.Text).Offset(0, 2).Value & " at " & _
Sheets("boltempdata").Range("a:a").Find(MBFTrailerNumber.Text).Offset(0, 3).Value & " " & _
" Do You Wish To Keep This Data? ", vbYesNo)

Krishna Kumar
10-07-2012, 02:13 AM
Hi

May be

With ActiveSheet

Range("a1").Value = MBFTrailerNumber

Dim x As Double
Dim Reply As Long
Dim Reply1 As Long
Dim Reply2 As Long
Dim r As Range

x = Application.WorksheetFunction.CountIf(Range("A:A"), MBFTrailerNumber.Text)
If x > 1 Then
Set r = Sheets("boltempdata").Range("a:a").Find(MBFTrailerNumber.Text, looat:=xlWhole)
Reply = MsgBox(" I Am Showing That Trailer " & _
r.Offset(0, 0).Value & " Pulled By " & _
r.Offset(0, 6).Value & " Weighed In At " & _
r.Offset(0, 11).Value & " Lbs on " & _
r.Offset(0, 2).Value & " at " & _
r.Offset(0, 3).Value & " " & _
" Do You Wish To Keep This Data? ", vbYesNo)

Rlb53
10-07-2012, 03:00 AM
Cool !! I see where you are going with that !

Gonna give it a try... will let you know if it will restrict the .find to the "Whole" value instead of the partials.

Thanks !

Rlb53
10-07-2012, 03:11 AM
I think this is heading in the correct direction... but with this syntax I'm getting an error message of "Named Argument Not Found". Could there be another command to work with the "xlWhole" request?

Rlb53
10-07-2012, 03:36 AM
Oh !... Got it ! Typographical error... the "k" was left out of the Lookat:=xlWhole.

Just What I Needed ! Thank You Immensely !!