VBA code - jump to first line with specific date

[DarkSmile]

Hi,

I'm looking for a VBA code that does the following:

I have a cell (B2), where I wanna put in any date (see Printscreen), I would like to push a button and jump to the first row with that date.
Note, the first 4 rows are frozen, so it would be nice if it "scrolled" to the right cell (see second printscreen).

If looked on google, but it's a lot of 'today', but I need any date.

Any help would be really apricated.

Thank you !

David

 

[HongRu]

try this.

Sub sbDate()
    ActiveWindow.ScrollRow = Cells.Find(What:=Range("B2"), After:=Range("B2"), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, MatchByte:=False, SearchFormat:=False).Row
End Sub

 

[aaewalsh]

Can you Try THis

Sub JumpToDate()
    Dim dateToFind As Date
    Dim i As Long
    
    dateToFind = Range("B2").Value

    For i = 5 To Cells(Rows.Count, 1).End(xlUp).Row
        If Cells(i, 1).Value = dateToFind Then
            Range(Cells(i, 1), Cells(i, 1)).Activate
            ActiveWindow.ScrollRow = i
            Exit For
        End If
    Next i
End Sub

This code will take the date entered in cell B2 and search through column A starting at row 5 (since the first 4 rows are frozen) for that date. When it finds the date, it activates the cell and uses the ActiveWindow.ScrollRow property to scroll the worksheet so that the row containing the date is at the top of the visible area. You can assign this code to a button or shape by right-clicking the button, selecting "Assign Macro", and selecting the "JumpToDate" macro.

 

[bebo021999]

You can either:
1) Assign below coe to button:

Sub JumpToDate()
Range("A" & WorksheetFunction.Match(Range("B2"), Columns(1), 0)).Select
End Sub

or
2) Put this code in worksheet module, to trigger any change in cell B2
(Right click on tab name, view code then paste below code)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) <> "B2" Then Exit Sub
Range("A" & WorksheetFunction.Match(Target, Columns(1), 0)).Select
End Sub

 

[DarkSmile]

try this.

Sub sbDate()
    ActiveWindow.ScrollRow = Cells.Find(What:=Range("B2"), After:=Range("B2"), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, MatchByte:=False, SearchFormat:=False).Row
End Sub

Sub JumpToDate() Dim dateToFind As Date Dim i As Long dateToFind = Range("B2").Value For i = 5 To Cells(Rows.Count, 1).End(xlUp).Row If Cells(i, 1).Value = dateToFind Then Range(Cells(i, 1), Cells(i, 1)).Activate ActiveWindow.ScrollRow = i Exit For End If Next i End Sub

Both worked great, thank you !