Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > search for duplicates
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 108
Default search for duplicates
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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default search for duplicates
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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 108
Default search for duplicates
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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default search for duplicates
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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 108
Default search for duplicates
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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 108
Default search for duplicates
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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Search for duplicates across three worksheets Sarah_Lund Excel Worksheet Functions 1 May 20th 08 12:29 AM
create a macro to search an excel spread for duplicates jamalin Excel Worksheet Functions 4 February 16th 07 10:58 AM
how do I search a colum for duplicates automatically Andrew Excel Discussion (Misc queries) 2 February 8th 06 02:45 PM
search range for duplicates Doug Loewen[_2_] Excel Programming 3 April 16th 04 04:18 PM
Word Search from Excel results in Duplicates Sarvesh Excel Programming 2 December 23rd 03 05:23 PM


All times are GMT +1. The time now is 08:57 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement