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.

 

[My Aswer Is This]

Try this:

Sub Test()
Application.ScreenUpdating = False
Dim c As Range
Dim ans As String
Dim Lastrow As Long
ans = ActiveCell.Value
Lastrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
    For Each c In Sheets("Sheet2").Range("A1:A" & Lastrow)
        If c.Value = ans Then Application.Goto Reference:=Sheets("Sheet2").Range(c.Address): Exit Sub
    Next
Application.ScreenUpdating = True
End Sub

 

[SQUIDD]

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

 

[kentster]

Thanks! Worked like a charm. Now the last step to improve it Once it finds the value in column A, can I have the active cell be the same row but in Column AB?

That would seal the deal for me

 

[SQUIDD]

replace
ActiveCell.Select
with
ActiveCell.Offset(0, 27).Select

dave

if using my code

 

[kentster]

Awesome! Worked perfectly. Thank so much for the assist!

My Aswer Is This - Thanks for the reply as well.

 

[SQUIDD]

Glad to help.

depents how ofen you are going to execute the code and how large your project is, but you may want to ammend the code to not do so much on the selction side of things.

Dave

 

[kentster]

Thanks - I was actually reading around and thought that rather than have a user go to the top of the page for the "button" to active the code, they could just double-click on the cell where the customer is located and it would go to the detail tab (similar to a pivot, but not using pivot).

I did some reading around and tried the following:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


rngY = ActiveCell.Value


Sheets("SW-Cons Match Detail").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

However, when I double-clicked it came back with "Range class failed" and highlighted Columns("A:A").Select

Is there away to update the code so that on a double-click it goes that cell's value in another sheet?

 

[SQUIDD]

So. You want to double on a cell and find the value of that cell in a different sheet.
And once found move to column ab. And highlight that cell.

Correct?

 

[SQUIDD]

That's was supposed to say double click.

Out of interest, where did you paste the code on your previous post?