on startup scroll across columns to the current date

x = Day(Date)
fails - you need to find dd/mmm string.

My recommended code:

Private Sub Workbook_Open()
On Error Resume Next
Application.Goto Sheet1.Rows(2).Find(Format$(Date,"dd/mmm"), , xlValues, xlWhole).Item(2, 1), True
End Sub

Change 'Sheet1' with the codename of your sheet; if the workbook has only one sheet, write simply
Application.GoTo Rows(2)...

Hi friends,
Both codes worked fine in the sample spreadsheet provided, but did not in my application. Disabling the error protection, it came up with RunTime Error 91: Object or With Block variable not set
Rob

Try this for fun.

With Sheet17
    Application.Goto .Cells(2, Application.Match(CDbl(Date), .Rows(2), 0)), True
End With

I've written this short code for you:

Private Sub Workbook_Open()
On Error Resume Next
Application.Goto Sheet1.Rows(2).Find(Format$(Date,"dd/mmm"), , xlValues, xlWhole).Item(2, 1), True
End Sub

If (without the line 'On Error Resume Next') it fails, try only this:

1. Write in the immediate window

? Sheet1.Name

(don't forget the leading '?'). If it fails, you wrote incorrect sheet codename; use the correct one. If the codename 'Sheet1' is correct, then

2. write in the immediate window (use copy/paste of course)

? Sheet1.Rows(2).Find(Format$(Date,"dd/mmm"), , xlValues, xlWhole) Is Nothing

If it returns 'True', the date wasn't found, it's missing in your sheet; fix it. That's why the subroutine begins with 'On Error Resume Next'. If you want to say something to user in this case, change the code into

Private Sub Workbook_Open()
On Error Resume Next
Application.Goto Sheet1.Rows(2).Find(Format$(Date,"dd/mmm"), , xlValues, xlWhole).Item(2, 1), True
if Err Then Application.Speech.Speak "Sorry buddy, the today's date is missing in the sheet", True
End Sub

Jan Mach, your recommendations overall are excellent. Thanks for the alternate ideas.


As for using the Sheet1 syntax the downfall of this method comes when a sheet is deleted and recreated with the same name, not an uncommon thing. By using the sheet names in your code, the vba survives the codename changes. Noted here for clarity of why it so often the preferred approach.

@ Jan

It's said by many programmers that the use of On Error Resume Next is just a way to avoid writing extensive Error Handling and it's to be used with caution.

You provide some debugging tips which is a good thing but the tip needed to get your code to work isn't included since it throws an Error 91 when deleting the On Error ....

Did you test your code in the example file provided in Post#4 because the code I provided in the post just before yours works just fine in that file ?