Using the value of one cell to locate another on a different sheet-CodePudding

I'm trying to get a VBA code together that can take the date from cell B5 on one sheet and locate the exact match from a column on a different sheet, then select that as the active cell. any ideas?

Heres what i've tried;

Sub find()
    Sheets("Details").Select
    Range("B5").Select
    rngY = ActiveCell.Value

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

CodePudding user response：

Something like this should work for you:

Public Sub FindInSheet2()
    Dim r As Range, lookupVal As Variant
    
    lookupVal = ThisWorkbook.Worksheets("Details").Range("B5").Value
    Set r = ThisWorkbook.Worksheets("Calcs").Range("C:C").Find(What:=lookupVal, LookAt:=xlWhole, LookIn:=xlValues)
    
    If Not r Is Nothing Then
        ThisWorkbook.Worksheets("Calcs").Activate
        r.Select
    Else
        MsgBox "Lookup not found", vbOKOnly
    End If
End Sub

The main difference between this code and yours is that I haven't relied on ActiveCell or Selection. It's better to be specific about which ranges you are working with. For more info, see How to avoid using Select in Excel VBA