Macro to find cell value in another sheet

[kentster]

Hi - I spent time trying to find it in the FAQs but I couldn't find the answer. Apologies if it is out there.

I am trying to create a macro which will take the value from the active cell in "Sheet1" and find the matching value in column A in Sheet 2. When it is found, my cursor would be sitting in Sheet 2 on the matched value.

 

[kentster]

yep - that's the plan. I pasted the code into the worksheet by right clicking on the tab and selecting View Code...then selected Worksheet and the DoubleClick command on the right side pull down menu. Then just pasted in your original code.

 

[SQUIDD]

OK

Not sure if there is an easier way?

but this works for me

put

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, [A:A]) Is Nothing Then
        Call find
    End If
End Sub

in the sheet code as you done previously

then put this in a module

Sub find()
rngY = ActiveCell.Value

    Sheets("Sheet2").Select
    Columns("A:A").Select
    Selection.find(What:=rngY, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
      
ActiveCell.Offset(0, 27).Select
End Sub

now when you double click a cell in sheet 1 column A, it will fin the value in sheet 2 and offset ot column AB

hope this is what you required.

Dave

 

[kentster]

Perfect! Thank you again.

 

[SQUIDD]

you welcome, glad to help

BTW

try changing the module code to this

Sub find()
rngY = ActiveCell.Value
Sheets("Sheet2").Activate
Cells.find(What:=rngY).Offset(0, 27).Activate
End Sub

works for me and should be faster, if nothing else it looks better.

Dave

 

[kentster]

nice - thanks!

 

[My Aswer Is This]

Next time you have a request it would save us a lot of work if you spelled out what you want in your original post. And not step us through things one step at a time.

 

[kentster]

My apologies. I agree 100%. The only thing was I was finding more streamlined approaches to make my sheet more productive along the way of learning it all. I appreciate the feedback and will try to consider all possibilities before trying to find the best approach. That way it will save you all time. Again, my apologies.

 

[ipbr21054]

Hi kentster

welcome, something like this??

Sub find()
rngY = ActiveCell.Value

    Sheets("Sheet2").Select
    Columns("A:A").Select
    Selection.find(What:=rngY, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
     
ActiveCell.Select
End Sub

Why when i copy & paste this i get thet rngY is not defined.
So i add Dim rngY As Range but then something else pops up.

Others say thanks it works so what as i missing for it not to be able to copy then paste on my sheet for it to just work ?

 

[SQUIDD]

Why when i copy & paste this i get thet rngY is not defined.
So i add Dim rngY As Range but then something else pops up.

Others say thanks it works so what as i missing for it not to be able to copy then paste on my sheet for it to just work ?

hi, i just tested, it seems to work, however, try the below.

Sub find()
    a = ActiveCell
    Sheets("Sheet2").Select
    Columns("A:A").find(What:=a, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Select
End Sub

 

[ipbr21054]

Maybe im putting it in the wrong place