on startup scroll across columns to the current date

[Robn02]

I have a range of dates by formula on row 2 across columns starting from G to DE. Dates are formatted as dd/mmm. Need a macro that will automatically scroll to the current date on opening the workbook. Row2 is protected, but row 3 onwards is not. The cursor should stop at Row 3, at the column with the current date. Any help appreciated. Thanks
Rob.

[Logit]

Paste into ThisWorkbook module:

Please Login or Register  to view this content.

[Jan Mach]

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

My recommended code:

Please Login or Register  to view this content.

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

[Robn02]

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

[Logit]

Please post your workbook for review.

[Robn02]

Posted on previous message. Appreciate your help.
Thanks
Rob

[Logit]

This will check ALL sheets in the workbook for the current date. So, if you will have more than sheet VAC containing date,
we'll need to change it.

Please Login or Register  to view this content.

[Robn02]

Thank you for the help. I will try this out when I get home tonight.
Rob

[Robn02]

Thanks for everyone's help.
I finally settled for this code in the Worksheet Module. Works as intended.

Please Login or Register  to view this content.

Thanks for the education. Valuable stuff.
Rob

[bakerman2]

Try this for fun.

Please Login or Register  to view this content.

[Jan Mach]

I've written this short code for you:

Please Login or Register  to view this content.

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

1. Write in the immediate window

Please Login or Register  to view this content.

(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

Please Login or Register  to view this content.

[JBeaucaire]

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.

[bakerman2]

@ 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 ?