VBA macro question

asiralia

New Member
Joined
Aug 1, 2007
Messages
2
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

Code:
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Adjusted your code some:
Code:
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.
 

Forum statistics

Threads
1,181,722
Messages
5,931,673
Members
436,798
Latest member
spprtpplcm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top