• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to allow me to go to today's date [SOLVED]

MalR

Member
Hi guys. Can't see this in the forums.

We have a tourist park. I use a home made booking calendar which has daily dates going along the top of an excel worksheet extending left to right. (say row 4). Below this are rows for our cabins. So we have therefore dates extending along the top and cabin numbers going down the sheet. This allows us to book each cabin based on date.

The collumn with today's date is where we return to because it shows who is coming in that day, what cabins need to be cleaned and who is in our park.

Regularly we can take a booking which (say) could be six months ahead and then we need to return to today's date.

Is there a macro that would allow us to click on an icon in Developer which would bring our cursor back to today's date? This would save us manually scrolling back which is quite tedious as we take numerous bookings each day.

I use Win 7, 64 bit with MS office 2010.

Thanks guys

MalR
 
Hi Mal ,


I am sure someone will post the macro you need , but in the meantime you can try a formula based workaround.


I assume your dates are in row 4 , going from B4 across.


Create a named range TODAY , and in the Refers To box , put in the formula :


=OFFSET(Sheet1!$B$4,,MATCH(TODAY(),Sheet1!$4:$4,0)-2)


Now , when ever you wish to go to today's column , press F5 ( GoTo ) or CTRL G , and in the Reference box , enter today
. When you click OK , the cursor will go to the 4th row in that column.


Narayan
 
Hi, MalR!


Following NARAYANK991's sureness this is the code for the macro, place it in any module and then assign a Ctrl-<letter> shortcut from the Alt-F8 macro menu, Options, so as to call it immediately from anywhere:

-----

[pre]
Code:
Option Explicit

Sub GoToToday()
' constants
Const kiDateRow = 4
' declarations
' start
On Error Resume Next
' process
With ActiveSheet.Rows(kiDateRow)
.Cells(2, .Find(Int(Now())).Column).Select
End With
' end
On Error GoTo 0
End Sub
[/pre]
-----


Regards!
 
Narayan thanks for the input and the quick reply. I was using Cntrl F to find the word "Today". Problem is I have to type the word everytime I want to go back. OK for me but for my wife it is another story!

SirJB7 this is a brilliant bit of code. It did the job! I had previously used a similar one but it required a calendar control which is missing in 2010.

Your code seems to work without this.

Thanks again guys. This code saves me a lot of time!

Mal
 
Hi,MalR!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
Back
Top