Excel - Go to date cell (i.e. today"s date)

jworton
jworton used Ask the Experts™
on
I have a simple worksheet, dates are in Column B. I wan to have have Excel go to the cell to the left of Today's Date Cell. Thanks in advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
Are you doing this in the Excel UI, in a formula, or in VBA?
Here is a macro that will do it.  I dont' know where you want to put it, but if you put it in a module and run it, it will do what you want.  You can put this code in whatever event you need it to be in..

:-)
Albert

Sub FindDate()
Dim r As Range
Dim MaxRow As Long

Set r = Range("B1")
MaxRow = Range("B" & Cells.Rows.Count).End(xlUp).Row
    Do Until r.Row > MaxRow
        If IsDate(r.Value) Then
            If DateValue(r.Value) = DateValue(Now()) Then
                r.Offset(0, -1).Select
                Exit Sub
            End If
        End If
            Set r = r.Offset(1, 0)
    Loop
    MsgBox "Todays date was not found"
    
End Sub

Open in new window

Author

Commented:
matthewspatrick - I should have prefaced the question - I am just a bit above novice. I have been trying to use VBA and conditional formatting

ahammar - that macro worked but I have to run it when I open the spreadsheet - can I automate on opening?
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

At what point do you want the macro to run?  When you open the workbook...or when you select a certain sheet?
If you want it to run when you open the workbook, then double click on "ThisWorkbook" in VB editor, then paste this code in the blank module window that opens.  You have to change the name of the sheet in this code though to the name of the sheet that it needs to be on when the code runs...It will be line 4 where you put the name of the worksheet.
Save it and close it.  When you open the workbook, that sheet will select, then the rest of the code will run as before except it will be automatic..

:-)
Albert



Private Sub Workbook_Open()
Dim r As Range
Dim MaxRow As Long
Sheets("YourSheetNameHere").Select


Set r = Range("B1")
MaxRow = Range("B" & Cells.Rows.Count).End(xlUp).Row
    Do Until r.Row > MaxRow
        If IsDate(r.Value) Then
            If DateValue(r.Value) = DateValue(Now()) Then
                r.Offset(0, -1).Select
                Exit Sub
            End If
        End If
            Set r = r.Offset(1, 0)
    Loop
    MsgBox "Todays date was not found"

End Sub

Open in new window

Author

Commented:
Albert - thank you!

As I said I am just above novice, but your instruction allowed me to achieve my goal. Thank you again!

Joe
You're welcome.  Thanks for the points and the grade!  I'm glad you got it working..

:-)
Albert

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial