find a particular date in another sheet

[macangc]

Hi there

Relative newcomer to Visual Basic and i would like some help with the following if possible

I have a cell that i will enter a date into and when i click the button below it will find the same date in another sheet, then move down one cell and across one.

Thanks in advance

 

[Joe4]

Welcome to the board!

Much of the code you need can be obtained using the macro recorder. Give this a go. Obviously, you will need to change the sheet names and range to suit your worksheet.

Sub MyFind()

'   Capture value to find from sheet 1 
    Dim myFindDate As Date
    myFindDate = Sheets("Sheet1").Range("F1")
    
'   Find value on sheet 2
    Sheets("Sheet2").Activate
    On Error GoTo err_chk
    Cells.Find(What:=myFindDate, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Offset(1, 1).Activate
    On Error GoTo 0
    
    Exit Sub
'   Error handling if it cannot find value
err_chk:
    If Err.Number = 91 Then
        Sheets("Sheet1").Activate
        MsgBox "Cannot find the value " & myFindDate
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
    
End Sub

 

[macangc]

Thanks very much, that worked a treat. Appreciate the help