Select row from macro
 

What a wonderful forum. I have received excellent advice regarding another
issue, which has saved me tons of time. Thanks to all. So, may I have
another?....

I have a date on page 1 (cell D2) and a column of dates on page 2, in
sequential order, in column A. Each date has a row of information next to
it, typically 8 to 10 cells worth.

I am looking for a way, using a macro, to select the entire row of a
certain date on page 2 that matches the date on page 1 (which is
selectable).

Thank you,
Randy

Select row from macro
 

Hi Randy,

One way:

'===========================
Sub Tester()
Dim rng As Range
Dim searchStr As Long
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim irow As Long

Set sh1 = ThisWorkbook.Sheets(1)
Set sh2 = ThisWorkbook.Sheets(2)

searchStr = sh1.Range("D2").Value

If Not IsError(Application. _
Match(searchStr, sh2.Columns(1).Cells, 0)) Then
irow = Application.Match _
(searchStr, sh2.UsedRange.Columns(1), 0)
Set rng = Intersect(sh2.Rows(irow), sh2.UsedRange)
Else
MsgBox Format(searchStr, "MM/DD/YY") _
& " not found on " & sh2.Name
End If

If Not rng Is Nothing Then Application.Goto rng

End Sub
'<<===========================


---
Regards,
Norman



"RAP" wrote in message
...
What a wonderful forum. I have received excellent advice regarding
another
issue, which has saved me tons of time. Thanks to all. So, may I have
another?....

I have a date on page 1 (cell D2) and a column of dates on page 2, in
sequential order, in column A. Each date has a row of information next to
it, typically 8 to 10 cells worth.

I am looking for a way, using a macro, to select the entire row of a
certain date on page 2 that matches the date on page 1 (which is
selectable).

Thank you,
Randy

Select row from macro
 

Norman,

Vunderbar!! It worked beautifully! Thank you very much. I never would
have come up with that.
Randy

"Norman Jones" wrote:

Hi Randy,

One way:

'===========================
Sub Tester()
Dim rng As Range
Dim searchStr As Long
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim irow As Long

Set sh1 = ThisWorkbook.Sheets(1)
Set sh2 = ThisWorkbook.Sheets(2)

searchStr = sh1.Range("D2").Value

If Not IsError(Application. _
Match(searchStr, sh2.Columns(1).Cells, 0)) Then
irow = Application.Match _
(searchStr, sh2.UsedRange.Columns(1), 0)
Set rng = Intersect(sh2.Rows(irow), sh2.UsedRange)
Else
MsgBox Format(searchStr, "MM/DD/YY") _
& " not found on " & sh2.Name
End If

If Not rng Is Nothing Then Application.Goto rng

End Sub
'<<===========================


---
Regards,
Norman



"RAP" wrote in message
...
What a wonderful forum. I have received excellent advice regarding
another
issue, which has saved me tons of time. Thanks to all. So, may I have
another?....

I have a date on page 1 (cell D2) and a column of dates on page 2, in
sequential order, in column A. Each date has a row of information next to
it, typically 8 to 10 cells worth.

I am looking for a way, using a macro, to select the entire row of a
certain date on page 2 that matches the date on page 1 (which is
selectable).

Thank you,
Randy