search for duplicates

[enyaw]

I have a code which take info from a userfor and inputs into the next empty
row in a worksheet. It also searches one of the columns for duplicate
values. I want to change the code so as to search two columns. Textbox2
will have to equal a value in column 2 and TextBox3 will have to a equal a
value in column 3. Both these values must be on the same row to return the
error message otherwise the information can be inputted. Can anyone help?

Private Sub CommandButton14_Click()
If TextBox1 = "" Then
MsgBox "Please enter Operation Number"
TextBox1.SetFocus
ElseIf TextBox2 = "" Then
MsgBox "Please enter Part Number"
TextBox2.SetFocus
ElseIf TextBox3 = "" Then
MsgBox "Please enter Sequence Number"
TextBox3.SetFocus
ElseIf TextBox4 = "" Then
MsgBox "Please enter Description"
TextBox4.SetFocus
Else
TextBox2.Enabled = True
Dim oCell As range
Dim wks As Worksheet
Dim strAddress As String
Dim FindWhat As String
Dim hawb As String
Sheets("sheet2").Select
hawb = TextBox2.Value
FindWhat = hawb
If FindWhat <= "" Then
Exit Sub
Else
For Each wks In Worksheets
'Find first occurrence in sheet
With wks.range("B:B")

Set oCell = .Find(What:=FindWhat, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If oCell Is Nothing Then

Dim nextrow As String
With Worksheets("Sheet2")
nextrow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Cells(nextrow, "A") = TextBox1.Value
.Cells(nextrow, "b") = TextBox2.Value
.Cells(nextrow, "c") = TextBox3.Value
.Cells(nextrow, "d") = TextBox4.Value
.Cells(nextrow, "e") = TextBox5.Value
.Cells(nextrow, "f") = TextBox6.Value
.Cells(nextrow, "g") = TextBox7.Value
.Cells(nextrow, "h") = TextBox8.Value
.Cells(nextrow, "i") = TextBox9.Value
.Cells(nextrow, "j") = TextBox10.Value
.Cells(nextrow, "k") = TextBox11.Value
.Cells(nextrow, "l") = TextBox12.Value
.Cells(nextrow, "m") = TextBox13.Value
.Cells(nextrow, "n") = TextBox14.Value
.Cells(nextrow, "o") = TextBox15.Value
.Cells(nextrow, "p") = TextBox16.Value
.Cells(nextrow, "q") = TextBox17.Value
.Cells(nextrow, "r") = TextBox19.Value
.Cells(nextrow, "s") = TextBox18.Value
Columns("A").Select
Columns("A").EntireColumn.AutoFit
Columns("h:h").Select
Columns("h:h").EntireColumn.AutoFit
Columns("k:k").Select
Columns("k:k").EntireColumn.AutoFit
Columns("n:n").Select
Columns("n:n").EntireColumn.AutoFit
Columns("q:q").Select
Columns("q:q").EntireColumn.AutoFit
Columns("r:s").Select
Columns("r:s").EntireColumn.AutoFit
range("A2").Select
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
TextBox5 = ""
TextBox6 = ""
TextBox7 = ""
TextBox8 = ""
TextBox9 = ""
TextBox10 = ""
TextBox12 = ""
TextBox13 = ""
TextBox16 = ""
TextBox17 = ""
TextBox15 = ""
TextBox18 = ""
TextBox19 = ""
OptionButton1 = True
OptionButton1 = False
OptionButton2 = False
OptionButton3 = True
OptionButton3 = False
OptionButton4 = False
OptionButton5 = True
OptionButton5 = False
OptionButton6 = False
OptionButton7 = True
OptionButton7 = False
OptionButton8 = False
Me.TextBox1.SetFocus
UserForm1.TextBox2.Enabled = True
End With
Else
strAddress = oCell.Address(External:=True)
Do
Application.GoTo oCell, Scroll:=True
MsgBox "Duplicate Part Number found. Please enter
another Part Number"
TextBox2.SetFocus


Exit Sub

Set oCell = .FindNext(oCell)
Sheets("sheet2").Select

Loop Until oCell.Address(External:=True) = strAddress
End If
End With
Next wks

Columns("A:S").Select
Selection.Sort Key1:=range("A2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
range("A2").Select


End If
End If
End Sub

 

[Tom Ogilvy]

Use an approach something like this: (You will have to figure out how to
apply it to your code. )


Dim bFound as Boolean
Dim sAddr as String
.. . .


With wks.range("B:B")

Set oCell = .Find(What:=FindWhat, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
bFound = False
if not oCell is noting then
sAddr = oCell.Address
do
if oCell.Offset(0,1) = Textbox3.Text then
bfound = True
exit do
end if
set oCell = .FindNext(oCell)
loop while oCell.Address <> sAddr
if Not bFound then
set oCell = Nothing
end if
If oCell Is Nothing Then

 

[enyaw]

Tom i am having trouble getting the code to work. Can you help?

 

[Tom Ogilvy]

Do your search just for the textbox2 Value as you do now. Each time you find
one, check if the cell to the right equals the textbox3 value. when it
does, you know you have found a condition that restricts entry. If it
doesn't, continue searching.

It should be a simple addition to your existing code. However, since you
are searching over multiple sheets for all instances, I don't want to guess
what your overall logic is.

 

[enyaw]

Tom I will be using this code to search only one sheet "Sheet2". I am still
having problems adding the search code into my own code.

 

[enyaw]

Got it to work. One of the lines of the program was misspelled.

Thanks Tom