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.

 

[Georgiboy]

Don't forget to look at post 7 as this worked with the formulas that you explained that you had.

 

[Gavlaar]

Thank you so much for you efforts so far!

I've tried changing
Set fRng = rng.Find(Date, , , xlWhole)
to
Set fRng = rng.Find(Date, , xlValues, xlWhole)
= no joy

The first cell in the range (F2) is 27/03/2024, the next cell to the right is J2 (because for each say 4 cells are merged) and the formula in J2 is "=F2+1"
this formula is then filled across the whole range "F2:AGP2". The merged cell format is

interestingly I entered today date 08/05/2024 and the first code (without xlValues) worked, however I've tried again since, in exactly the same way and just get "not found". When it was sort of working it seemed to highlight all the columns from yet more merged cells that are above the date formulas (with the month showing in the picture above)

 

[Gavlaar]

i thought this pic might help

 

[Gavlaar]

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

works like a charm Alex, Thank you!

I dont suppose this could be entered as a hyperlink on the sheet is there? Having Macro's/code seems to be causing a headache with some users security

 

[Alex Blakenburg]

Glad to hear that worked. I am afraid I can't help you with the Hyperlink I suspect it would still involve the use of a macro.
Perhaps @Georgiboy or @Joe4 will have a suggestion.

@Georgiboy
I have historically found using "Find" with dates involving formulas to be quite fragile.
When you use a formula such as previous date + 1, you have to switch to using xlValues and once you do that it becomes very sensitive to the Date Format that is used.
If you use your default system date as the format it will work but when you deviate from that it more often than not doesn't work.
In this case I believe row 2 is formatted as "d" which causes the combination of using xlValues and looking for a date to fail.

 

[Gavlaar]

Glad to hear that worked. I am afraid I can't help you with the Hyperlink I suspect it would still involve the use of a macro.
Perhaps @Georgiboy or @Joe4 will have a suggestion.

@Georgiboy
I have historically found using "Find" with dates involving formulas to be quite fragile.
When you use a formula such as previous date + 1, you have to switch to using xlValues and once you do that it becomes very sensitive to the Date Format that is used.
If you use your default system date as the format it will work but when you deviate from that it more often than not doesn't work.
In this case I believe row 2 is formatted as "d" which causes the combination of using xlValues and looking for a date to fail.

Thank you for the insight!

 

[Gavlaar]

Thank you for the insight!

Being new to mrexcel.com should I really close this thread and mark as complete (or whatever i can do) and then repost that question since it is different really?

 

[Georgiboy]

I don't think you will get it to select exactly where you want with a hyperlink but the below will take you to todays date:

=HYPERLINK("#"&CELL("address",INDEX(F2:AGP2,MATCH(TODAY(),F2:AGP2,0))),"Today")

 

[Georgiboy]

I believe row 2 is formatted as "d" which causes the combination of using xlValues and looking for a date to fail.

Thanks for that, I use find in my spreadsheets with dates, but then I rarely use the "dd" format tbh

 

[Gavlaar]

I don't think you will get it to select exactly where you want with a hyperlink but the below will take you to todays date:

=HYPERLINK("#"&CELL("address",INDEX(F2:AGP2,MATCH(TODAY(),F2:AGP2,0))),"Today")

You are spot on, not quite what I needed. Thanks for trying!