Select Cell that equals Today (2K/2K3)

Topic

New Reply

I have =today() in cell A1 and column A from A12 down, has every day of the year in the format dd/mmm/yy, 16-May-06, 17-May-06,etc, ad infinitum. Is there a formulae to select the date that corresponds to the date in A1 on opening the worksheet, or is it a VBA solution? I have looked at Index and Match, but have had no joy.

Thank you.

Reply | Quote

Replies

Hi there

This is a VBA solution for your. Right hand mouse click the worksheet tab and select View Code and paste this into the editor.

Private Sub Worksheet_Activate()
Dim strDate As Date

Range(“A1”).Select

strDate = ActiveCell.Value

Cells.Find(What:=strDate, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=True).Activate
End Sub

Reply | Quote

A formula cannot select another cell, it can only return a value in the cell that contains it. To select a cell, you need VBA code. For example:
– Activate the Visual Basic Editor (Alt+F11).
– Double click the ThisWorkbook node belonging to your workbook.
– Paste the following code into the module that appears:

Private Sub Workbook_Open()
Dim wsh As Worksheet
Dim rng As Range
On Error GoTo ErrHandler
Application.EnableEvents = False
‘ Substitute sheet name below
Set wsh = Worksheets(“Sheet1”)
wsh.Activate
Set rng = wsh.Range(“A12:A377”).Find(What:=wsh.Range(“A1”), _
LookIn:=xlValues, LookAt:=xlWhole)
If Not rng Is Nothing Then
rng.Select
End If
ExitHandler:
Application.EnableEvents = True
Set rng = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

– Substitute the correct sheet name.
– Switch back to Excel, and save the workbook.

Reply | Quote

Thank you both for your swift replies. Visitors have arrived at 2100 (local) so I will test tomorrow.

Why is it wife’s won’t let you play on computers when you have visitors?

Reply | Quote

Hans,

Thank you for that. Works as advertised.

Jerry,

I get a “Compile error: Syntax Error. and the line of code starting: Cells.find.. is in red. Am not able to troubleshoot as I haven’t a clue what to do!

Problem now solved, so thanks to both of you.

Reply | Quote

Thanks worked on mine I was thinking afterwards that I should have put an error trap into it like Hans’s.

Reply | Quote

If farside tested on Excel 2000, the SearchFormat argument of the Find method was not valid – it was introduced in Excel 2002.

BTW, the Worksheet_Activate event will run when the user switches to the worksheet from another worksheet within the same workbook. It won’t run when the workbook is opened. That’s why I used the Workbook_Open event.

Reply | Quote

thanks

I was taking the assumption of when farside opened the worksheet as he suggested but I see your point

Reply | Quote

My code only runs when you open the workbook (the .xls fdile). Jerry’s code only runs when you switch from another worksheet within the workbook to the one with the dates. If you want the cell to be selected in both cass, you must add both pieces of code: the Workbook_Open procedure to the ThisWorkbook module, and the Worksheet_Activate procedure to the worksheet module, Here is a version of Jerry’s code with error handling, and without the argument that wouldn’t work in Excel 2000:

Private Sub Worksheet_Activate()
Dim rng As Range
On Error GoTo ErrHandler
Application.EnableEvents = False
Set rng = Range(“A12:A377”).Find(What:=Range(“A1”), _
LookIn:=xlValues, LookAt:=xlWhole)
If Not rng Is Nothing Then
rng.Select
End If
ExitHandler:
Application.EnableEvents = True
Set rng = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

Reply | Quote

Hans (and Jerry!)

Thank you for your comprehensive reply on this issue. The workbook and worksheets perform as you have briefed.

Big thank you:-)

Reply | Quote