search for duplicates

enyaw · 08-21-2006, 07:20 AM

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

Tom Ogilvy · 08-21-2006, 08:05 AM

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

enyaw · 08-21-2006, 10:45 AM

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

Tom Ogilvy · 08-21-2006, 11:15 AM

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

enyaw · 08-22-2006, 04:20 AM

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

enyaw · 08-22-2006, 10:40 AM

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