got to cell on seperate worksheet

[mpb]

Good Afternoon all,
Searched through this group & programming but no luck.

I have a command button which I would like to take me to the cell in
one worksheet (in row A), which is equal to the value of an 'input'
cell on a different worksheet.

Ie value 10009 in cell A1 in worksheet1, then vba to take me to
worksheet2 & find the value in row A

I hope I have explained this okay.

Any help much appreciated.

Cheers,

Mathew

 

[Dave Peterson]

This worked ok for me:

Option Explicit
Private Sub CommandButton1_Click()
Dim res As Variant
Dim wks As Worksheet

Set wks = Worksheets("Sheet2")

With wks
res = Application.Match(Me.Range("A1").Value, .Range("A:A"), 0)

If IsError(res) Then
'no match found
Beep
Else
Application.Goto wks.Cells(res, "A"), Scroll:=True 'false???
End If
End With

End Sub

 

[Don Guillett Excel MVP]

This worked ok for me:

Option Explicit
Private Sub CommandButton1_Click()
     Dim res As Variant
     Dim wks As Worksheet

     Set wks = Worksheets("Sheet2")

     With wks
         res = Application.Match(Me.Range("A1").Value, .Range("A:A"), 0)

         If IsError(res) Then
             'no match found
             Beep
         Else
             Application.Goto wks.Cells(res, "A"), Scroll:=True 'false???
         End If
     End With

End Sub

You said ROW A. Since there is no row A, this works for row 1 on
sheet4

Sub SAS_GotoAddressOfValueInCell()
Application.Goto Sheets("sheet4").Rows(1).Find(What:=Range("a1"), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext)
End Sub