find

[Guest]

I have a userform with two textboxes on it. I use it to find and return
values. How would i code this userform so as if i search the worksheet for
both values that it will return the values in that row? I need the first
textbox to search through column A and the second textbox to search through
column B. Both values must be found in the same row. I then need to return
the values from that row into another form. I have the code to return the
values.

 

[Bob Phillips]

Use something like this

On Error Resume Next
iRow = Application.Match(1,(TextBox1.Text = Columns(1))*(TextBox2.Text =
Columns(2)),0)
On Error Goto 0
If iRow > 0 Then
'iRow now points to the matching row in the worksheet

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

I cant get this code to work for me.

Use something like this

On Error Resume Next
iRow = Application.Match(1,(TextBox1.Text = Columns(1))*(TextBox2.Text =
Columns(2)),0)
On Error Goto 0
If iRow > 0 Then
'iRow now points to the matching row in the worksheet

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

Sorry, you are right, that won't work. Here is an alternative (which should
work)

Dim iRow As Long

On Error Resume Next
iRow = ActiveSheet.Evaluate("Match(1, (""" & TextBox1.Text &
"""=A1:A1000)*(""" & _
TextBox2.Text & """=B1:B1000), 0)")
On Error GoTo 0
If iRow > 0 Then
'iRow now points to the matching row in the worksheet

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Bob this code still isnt working for me.

Sorry, you are right, that won't work. Here is an alternative (which should
work)

Dim iRow As Long

On Error Resume Next
iRow = ActiveSheet.Evaluate("Match(1, (""" & TextBox1.Text &
"""=A1:A1000)*(""" & _
TextBox2.Text & """=B1:B1000), 0)")
On Error GoTo 0
If iRow > 0 Then
'iRow now points to the matching row in the worksheet

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

It may be wrap-around

Dim iRow As Long

On Error Resume Next
iRow = ActiveSheet.Evaluate("Match(1, (""" & TextBox1.Text & _
"""=A1:A1000)*(""" & _
TextBox2.Text & """=B1:B1000), 0)")
On Error GoTo 0
If iRow > 0 Then
'iRow now points to the matching row in the worksheet


if that doesn't work please supply details of what happens.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Still doesnt seem to work. I added the code to a command button on the
userform. I added an else statement at the end giving a message box if it
didnt work and the message box pops up when i try to run the code.

 

[Bob Phillips]

and it says?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

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

Bob this code seems to do the search for me but instead of selecting the row
it gives a message box. Any ideas on how to select the row once it has been
found?