finding

[Guest]

I have two textboxes on a form. I want to be able to use textbox1 to search
column A and textbox2 to search columnB. I need to be able to search and
find the two numbers but the numbers have to be in the same row. Anyone got
any ideas?

 

[Bernie Deitrick]

Assign this to a commandbutton on your userform: works with string or numbers....

Private Sub CommandButton1_Click()
Dim c As Range
Dim strF1 As String
Dim strF2 As String
Dim strAdd As String

strF1 = UserForm1.TextBox1.Text
strF2 = UserForm1.TextBox2.Text

With ActiveSheet.Range("A:A")
Set c = .Find(strF1, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
strAdd = c.Address
If c(1, 2).Value = strF2 Then GoTo Notify
Else:
MsgBox "Not Found"
Exit Sub
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address <> strAdd Then
Do
If c(1, 2).Value = strF2 Then GoTo Notify
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> strAdd
End If
End With

Notify:
MsgBox """" & strF1 & """ is next to """ & _
strF2 & """ in cells " & c.Resize(1, 2).Address

End Sub

HTH,
Bernie
MS Excel MVP

 

[Guest]

Private Sub CommandButton1_Click()
If TextBox5 = "" Then
MsgBox "Please enter Part Number"
UserForm3.TextBox5.SetFocus
ElseIf TextBox6 = "" Then
MsgBox "Please enter Sequence Number"
UserForm3.TextBox6.SetFocus
Else
Set rnga = range("B:B")

Set a = rnga.Find(UserForm3.TextBox5, LookIn:=xlValues, LookAt:=xlWhole)
If Not a Is Nothing Then
MsgBox "Number found"




UserForm3.TextBox5 = ""
UserForm3.Hide

UserForm1.OptionButton1 = False
UserForm1.OptionButton2 = False
UserForm1.TextBox5.Enabled = True
UserForm1.TextBox6.Enabled = True
UserForm1.TextBox7.Enabled = True
UserForm1.TextBox8.Enabled = True
UserForm1.OptionButton3 = False
UserForm1.OptionButton4 = False
UserForm1.TextBox9.Enabled = True
UserForm1.TextBox10.Enabled = True
UserForm1.TextBox11.Enabled = True

UserForm1.OptionButton5 = False
UserForm1.OptionButton6 = False
UserForm1.TextBox12.Enabled = True
UserForm1.TextBox13.Enabled = True
UserForm1.TextBox14.Enabled = True

UserForm1.OptionButton7 = False
UserForm1.OptionButton8 = False
UserForm1.TextBox15.Enabled = True
UserForm1.TextBox16.Enabled = True
UserForm1.TextBox17.Enabled = True

UserForm1.TextBox2.Enabled = False
UserForm1.TextBox1.SetFocus
UserForm1.TextBox1 = Cells(a.Row, 1)
Cells(a.Row, 1).ClearContents
UserForm1.TextBox2 = Cells(a.Row, 2)
Cells(a.Row, 2).ClearContents
UserForm1.TextBox3 = Cells(a.Row, 3)
Cells(a.Row, 3).ClearContents
UserForm1.TextBox4 = Cells(a.Row, 4)
Cells(a.Row, 4).ClearContents
UserForm1.TextBox5 = Cells(a.Row, 5)
Cells(a.Row, 5).ClearContents
UserForm1.TextBox6 = Cells(a.Row, 6)
Cells(a.Row, 6).ClearContents
UserForm1.TextBox7 = Cells(a.Row, 7)
Cells(a.Row, 7).ClearContents
UserForm1.TextBox8 = Cells(a.Row, 8)
Cells(a.Row, 8).ClearContents
UserForm1.TextBox9 = Cells(a.Row, 9)
Cells(a.Row, 9).ClearContents
UserForm1.TextBox10 = Cells(a.Row, 10)
Cells(a.Row, 10).ClearContents
UserForm1.TextBox11 = Cells(a.Row, 11)
Cells(a.Row, 11).ClearContents
UserForm1.TextBox12 = Cells(a.Row, 12)
Cells(a.Row, 12).ClearContents
UserForm1.TextBox13 = Cells(a.Row, 13)
Cells(a.Row, 13).ClearContents
UserForm1.TextBox14 = Cells(a.Row, 14)
Cells(a.Row, 14).ClearContents
UserForm1.TextBox15 = Cells(a.Row, 15)
Cells(a.Row, 15).ClearContents
UserForm1.TextBox16 = Cells(a.Row, 16)
Cells(a.Row, 16).ClearContents
UserForm1.TextBox17 = Cells(a.Row, 17)
Cells(a.Row, 17).ClearContents
UserForm1.TextBox18 = Cells(a.Row, 19)
Cells(a.Row, 19).ClearContents
UserForm1.TextBox19 = Cells(a.Row, 18)
Cells(a.Row, 18).ClearContents
Columns("A:s").Select
Selection.Sort Key1:=range("A2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
range("A2").Select


Else
MsgBox "No match found"
UserForm3.TextBox5 = ""
UserForm3.TextBox5.SetFocus
UserForm3.TextBox5 = ""
End If

End If
End Sub

This is the code I have so far. I want to modify if to use two textboxes
and find two numbers in seperate columns on the same row and return the
values to another form.

 

[Bernie Deitrick]

Try just the finding part:

Private Sub CommandButton1_Click()
Dim c As Range
Dim strF1 As String
Dim strF2 As String
Dim strAdd As String

If UserForm3.TextBox5.Text = "" Then
MsgBox "Please enter Part Number"
UserForm3.TextBox5.SetFocus
Exit Sub
End If

If UserForm3.TextBox6.Text = "" Then
MsgBox "Please enter Sequence Number"
UserForm3.TextBox6.SetFocus
Exit Sub
End If

strF1 = UserForm3.TextBox5.Text
strF2 = UserForm3.TextBox6.Text

'Assumes that Part numbers are in column B
With ActiveSheet.Range("B:B")
Set c = .Find(strF1, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
strAdd = c.Address
If c(1, 2).Value = strF2 Then GoTo Notify
Else:
MsgBox "Not Found"
Exit Sub
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address <> strAdd Then
Do
If c(1, 2).Value = strF2 Then GoTo Notify
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> strAdd
End If
End With

Notify:
MsgBox """" & strF1 & """ is next to """ & _
strF2 & """ in cells " & c.Resize(1, 2).Address

End Sub