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

Thanks Tom

"enyaw" wrote:

> 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.
>
> "Tom Ogilvy" wrote:
>
> > 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.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> >
> > "enyaw" wrote:
> >
> > > Tom i am having trouble getting the code to work. Can you help?
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > >
> > > > "enyaw" wrote:
> > > >
> > > > > 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:D").Select
> > > > > Columns("A:D").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
> > > > >
> > > > >