find
 

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.

find
 

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)

"enyaw" wrote in message
...
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.

find
 

I cant get this code to work for me.

"Bob Phillips" wrote:

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)

"enyaw" wrote in message
...
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.

find
 

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" wrote in message
...
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)

"enyaw" wrote in message
...
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.

find
 

Bob this code still isnt working for me.

"Bob Phillips" wrote:

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" wrote in message
...
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)

"enyaw" wrote in message
...
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.

find
 

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)

"enyaw" wrote in message
...
Bob this code still isnt working for me.

"Bob Phillips" wrote:

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" wrote in message
...
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)

"enyaw" wrote in message
...
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.

find
 

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

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)

"enyaw" wrote in message
...
Bob this code still isnt working for me.

"Bob Phillips" wrote:

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" wrote in message
...
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)

"enyaw" wrote in message
...
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.

find
 

and it says?

--
HTH

Bob Phillips

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

"enyaw" wrote in message
...
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" wrote:

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)

"enyaw" wrote in message
...
Bob this code still isnt working for me.

"Bob Phillips" wrote:

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" wrote in message
...
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)

"enyaw" wrote in message
...
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.

find
 

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?

"Bob Phillips" wrote:

and it says?

--
HTH

Bob Phillips

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

"enyaw" wrote in message
...
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" wrote:

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)

"enyaw" wrote in message
...
Bob this code still isnt working for me.

"Bob Phillips" wrote:

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" wrote in message
...
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)

"enyaw" wrote in message
...
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.