Macro question

[Ross Bennett]

I want a macro to select a row (or cell in a particular row) on a worksheet
that corresponds with a value in a cell eg.
Cell shows value 5. Macro selects row 5 on another sheet

or

Cell shows value 5. Macro selects cell at intersection of row 5 and a
specific column (say column 2 ?) on another sheet

What do I need to include in my macro to do this?
Can anyone help ?

Regards
Ross Bennett

 

[Dave Peterson]

One way to select the row:

Option Explicit
Sub testme()

Dim myRng As Range
Dim otherWks As Worksheet

Set otherWks = Worksheets("sheet2")

Set myRng = Nothing
On Error Resume Next
Set myRng = otherWks.Rows(ActiveCell.Value)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Invalid value"
Else
Application.Goto myRng, scroll:=True
End If

End Sub

And if you want to pick out a cell to go to, change this:
Set myRng = otherWks.Rows(ActiveCell.Value)
to:
Set myRng = otherWks.Cells(ActiveCell.Value, 2)