[SOLVED] finding

[enyaw]

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


"enyaw" <[email protected]> wrote in message
news:[email protected]...
>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?

[enyaw]

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" wrote:

> 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
>
>
> "enyaw" <[email protected]> wrote in message
> news:[email protected]...
> >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]

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


--
HTH,
Bernie
MS Excel MVP


"enyaw" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> 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
>>
>>
>> "enyaw" <[email protected]> wrote in message
>> news:[email protected]...
>> >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?
>>
>>
>>