Move view to column with todays date a row

[Gavlaar]

Hi, looking for some help if possible.

On row2 of a sheet I have dates in range F2: AGP2, I'm trying to write some code that will move the current view to show the column with todays date in it and have it showing to the hand side of the screen (plus to show 4 cells left as well as that will mean the previous day is also visible) but without moving the current view vertically, just moving the view horizontally.

 

[Gavlaar]

any idea or help would be greatly appreciated

 

[Gavlaar]

I have this code working as I would want but I can not figure out how to have the column where I would like it on the screen.

Sub Today()

Dim c As Long

Dim r As Range

Set r = ActiveSheet.Range("F2: AGP2")

On Error Resume Next

c = Application.WorksheetFunction.Match(CLng(Date), r, 0)

On Error GoTo 0

If c Then

ColumnLetter = Split(Cells(1, c + 6).Address, "$")(1) ' this is the column you want to move too!

ActiveSheet.Range(ColumnLetter & ActiveCell.Row).Activate

Else

MsgBox "Not Found"

End If

End Sub

 

[Georgiboy]

Perhaps this will help:

Sub Today()
    Dim fRng As Range
    Dim rng As Range
   
    Set rng = Range("F2:AGP2")
    Set fRng = rng.Find(Date, , , xlWhole)
   
    If Not fRng Is Nothing Then
        Application.Goto Columns(fRng.Column - 4), True
    Else
        MsgBox "Not Found"
    End If
End Sub

 

[Gavlaar]

Thanks for that Georgiboy!
at the moment I always get a "Not Found" so far, the dates are calculated in a formula.

 

[Joe4]

Thanks for that Georgiboy!
at the moment I always get a "Not Found" so far, the dates are calculated in a formula.

Please post the formula you use in calculating your dates.

 

[Georgiboy]

Change:

Set fRng = rng.Find(Date, , , xlWhole)

To:

Set fRng = rng.Find(Date, , xlValues, xlWhole)

 

[Gavlaar]

Please post the formula you use in calculating your dates.

it is + the cell to the left +1
with the first cell being a typed in date

 

[Joe4]

it is + the cell to the left +1
with the first cell being a typed in date

So how is the very first date to the most left being entered/calculated?
What is the exact value/formula in that cell?
What I am really trying to ascertain is whether or not you have a complete, valid date in that cell, or just the day number (so a partial date).

BTW, the image in your original post is unreadable, so not helpful at all in aiding in your question.

 

[Alex Blakenburg]

I am probably in the minority but I am quite partial to using Match for this sort of thing and since you said you had that working give this a try.

Sub MoveToToday()

    Dim c As Range
    Dim r As Range
    
    Set r = ActiveSheet.Range("F2: AGP2")
    
    On Error Resume Next
    With Application
        Set c = .Index(r, 0, .Match(CLng(Date), r, 0))
    End With
    On Error GoTo 0
    
    If Not c Is Nothing Then
        ActiveWindow.ScrollColumn = c.Offset(, -4).Column
    Else
        MsgBox "Not Found"
    End If

End Sub