This is my current code. It allows me to search two columns and returns
where the values are via a message box. I want to be able to select the row
the values are in. Can anyone help?

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
ActiveCell.Select
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
Selection.EntireRow.Select
End Sub

Change this:
Selection.EntireRow.Select
To this
Rows(c.Row).Select

Charles
enyaw wrote:
This is my current code. It allows me to search two columns and returns
where the values are via a message box. I want to be able to select the row
the values are in. Can anyone help?

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
ActiveCell.Select
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
Selection.EntireRow.Select
End Sub

is there a way of selecting cells a to s instead of the entire row as i want
to copy the info.

"Die_Another_Day" wrote:

Change this:
Selection.EntireRow.Select
To this
Rows(c.Row).Select

Charles
enyaw wrote:
This is my current code. It allows me to search two columns and returns
where the values are via a message box. I want to be able to select the row
the values are in. Can anyone help?

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
ActiveCell.Select
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
Selection.EntireRow.Select
End Sub

Range("A" & C.Row & ":S" & C.Row).Select

Charles

enyaw wrote:
is there a way of selecting cells a to s instead of the entire row as i want
to copy the info.

"Die_Another_Day" wrote:

Change this:
Selection.EntireRow.Select
To this
Rows(c.Row).Select

Charles
enyaw wrote:
This is my current code. It allows me to search two columns and returns
where the values are via a message box. I want to be able to select the row
the values are in. Can anyone help?

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
ActiveCell.Select
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
Selection.EntireRow.Select
End Sub