VBA macro question

[asiralia]

HI,

I have a macro that works, but I need to tweek it and dont know how. I have 2 tabs and in Tab1 in column B there is an indicator 'y' or blank.when its 'y' and the user clicks on a find button, it takes to Tab2 and finds the ID from Tab1 (columnA) and finds that record on the 2nd tab (but the ID Field is in Column X, I would like the macro to go to Column A of the row that it finds in Tab2. Can someone please help.

Thanks

Sub testing()
'
' testing Macro
' Macro recorded 08/01/2007 by u470004
'
'
    Dim val As Variant
    Dim cell As String
    Dim cell2 As String
    
    Dim r   As Range
    
    Sheets("Tab1").Activate

    cell = "AJ" & ActiveCell.Row
    val = Range(cell, cell).Value
    
    Sheets("Tab2").Select
    
    
    Set r = Cells.Find(val)

    If (Not r Is Nothing) Then
          cell2 = "A" & r.Select (me trying to fix it - IT DOESNT WORK )
       
    Else
     Sheets("Tab1").Activate

        MsgBox val & "NOTHING FOUND!", vbOKOnly + vbExclamation, "Lookup"
    End If

End Sub

EDIT: added Code tags - Moderator

 

[Datsmart]

Adjusted your code some:

Sub testing2()
' Macro recorded 08/01/2007 by u470004
Dim val As Variant
Dim cell As String
Dim cell2 As String
Dim r As Range
Dim rw As Integer

Sheets("Tab1").Activate
    cell = "AJ" & ActiveCell.Row
    val = Range(cell).Value

Sheets("Tab2").Select
    Set r = Cells.Find(val)
 
    If (Not r Is Nothing) Then
       rw = Cells.Find(val).Row 
       Cells(rw, 1).Select
    Else
        Sheets("Tab1").Activate
        MsgBox val & "NOTHING FOUND!", vbOKOnly + vbExclamation, "Lookup"
    End If
End Sub

All the code does is select a cell in column A of the Sheet named Tab2 as you requested.