Jump to a date (find a date) - Formual/Macro/ActiveX or Hyperlink

[SelinaR]

Please help - I've tried various options found online and nothing is working:

I have a large spreadsheet with a long date range from A:3 to A:900

what would be the best way to jump to today's date?
I have seen formulas, Macro's, Active X controls, Hyperlinks?

I would prefere a Macro if possible.

THANKS

 

[puru.sve]

You may try this :


=HYPERLINK("#"& "A" & MATCH(TODAY(),A3:A900,0)+2,TEXT(TODAY(),"m/dd/yyyy"))

 

[SelinaR]

A Genuis you are thanks puru.sve

 

[SelinaR]

Is it possible to make this hyperlink a button/form control?

 

[puru.sve]

Hi Selina, If you want to put it on buttton then you need to right VBA code ....

I can do it for you if you want.

 

[SelinaR]

that would be brilliant do you need row and coloumn references?

 

[puru.sve]

Hi Selina,

I am considering that you data is in Column A with Range A3:A900

put the below code on vba editot , Use Alt +F11

Sub searchdate()

lR = ActiveSheet.Range("A65536").End(xlUp).Row

For i = 3 To lR
    If Cells(i, 1) = Date Then
        Cells(i, 1).Select
    Exit For
    End If
Next i

End Sub

Insert any shapes or button on your activesheet .. then right click --> choose Assign macro > select searchdate

then just single click on button will do the same thing.

 

[SelinaR]

Hiya

I stepped into my exsisting Macro's and added yours in the workbook, but on slecting the button, the Macro is giving me the following error:

Compile error - Variable not defined and it is pointing to the 1R = section of the Macro....

any suggestions?

 

[puru.sve]

You need to declare the variables

Option Explicit
Sub searchdate()
Dim i, r As Integer
lR = ActiveSheet.Range("A65536").End(xlUp).Row

For i = 3 To lR
    If Cells(i, 1) = Date Then
        Cells(i, 1).Select
    Exit For
    End If
Next i

End Sub

 

[SelinaR]

I've tried your new forumla but it's giving me the same error - how do I declare the variable please?