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
> > >
> > >