"GO TO" TODAY()----select today out of full year dates in one column.

GFelts

New Member
Joined
Feb 9, 2019
Messages
20
I have a rather large spreadsheet that has twenty col across and 430 rows down, 365 of these rows have information and a date starting with 1/1/2019 in D36 thru 12/31/2019 in D401. Top 5 rows are froze so they can view averages in Row 5 and other information. Also a place to put the buttons for navigation, go to top, go to bottom, and go to chart macros. Each work day information is added to the corresponding date row. Which in turn drives a chart located at the top of the sheet for the BRASS to look at each morning. Not much of a problem now simple page up/page down and your there. But come around Mach and April on, the BRASS that look at this information will have to press a button for a macro to go to the top of the page so they can see the averages and another button the view the chart these macros are already working. (God forbid they have to scroll or page up to do that). But if they want to look at the data that is driving the chart---back down the page they go.....this is where they have problems. I want to add a button which will take them to todays date. If by chance they press "SAVE" while at the chart located at the top of the sheet then the next morning someone at 03:30am (probably me) half asleep, groggy eyed, grumpy, and sucking on coffee will have to go to the last date and enter the required information for today. Just FYI we are closed sometimes on the weekend so there is no data entered and this creates a break between the dates. Or I would use the End button in a macro on a column that I enter data into-- no work--no data which stops me from using End in a macro. It would stop at every Friday that we did not work that weekend. So far we have worked all but three weekends this year. So I have three breaks in the dates that stop the END function dead.

Assume Friday was 3/1/2019 the last date info was entered which is located in Cell D95, Closed Sat 3/2 and Sun 3/3 no data entered. Monday morning I press the magical button while at the top of the sheet and it goes to D98 which is 3/4/2019 Monday which is based on the date in B2 where I have TODAY() located at. Is there a Formula or VBA code that will use the TODAY() in B2 and then GO TO Cell D98? Actually I would like for it to go to "A98" the first cell to receive data. I'm at a loss but my Boss "WOULD LIKE TO SEE" this happen so he looks good to his boss's each morning while they play arm chair quarterback with the previous days data. VBA is not my forte But my Boss WOULD LIKE TO SEE this happen and he signs my paychecks. So I would like to see this happen as well and would love to figure out how the heck to do it myself. Any help would be appreciated ALOT!

Thanks in advance
Greg
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
col Ctoday is09/02/2019index number9add 514
row 7101/02/2019macro
202/02/2019
303/02/2019cells(cells(1,15),3).select
404/02/2019
505/02/2019
606/02/2019
707/02/2019
808/02/2019
909/02/20199 is found by using match H1 in C7 to C26
1010/02/2019
1111/02/2019
1212/02/2019
1313/02/2019
1414/02/2019
1515/02/2019
1616/02/2019
1717/02/2019
1818/02/2019
1919/02/2019
2020/02/2019

<colgroup><col span="2"><col><col span="4"><col><col span="8"></colgroup><tbody>
</tbody>
 
Upvote 0
I do appreciate the help, maybe I don't understand what you trying to show/tell me, but I can FIND the date, using match. I need the cursor to GO TO the date. Using your dates If I run the Macro on 9/2/2019 I need the cursor to GO from where ever it is TO the date 9/2/2019. Also I don't understand what your telling me with the Cells(Cells(1,15),3).Select Can you explain it to me. Remember I am very limited with VBA and not much better at formulas. Excel is fairly new to me. I came from mechanical background, been using Excel for all of about 3-4 months now. Due to an accident I am now doing office work and Excel is my new challenge. with help like Mr Excel and you folks I will master it, it will not beat me.
 
Upvote 0
I do appreciate the help, maybe I don't understand what you trying to show/tell me, but I can FIND the date, using match. I need the cursor to GO TO the date. Using your dates If I run the Macro on 9/2/2019 I need the cursor to GO from where ever it is TO the date 9/2/2019. Also I don't understand what your telling me with the Cells(Cells(1,15),3).Select Can you explain it to me. Remember I am very limited with VBA and not much better at formulas. Excel is fairly new to me. I came from mechanical background, been using Excel for all of about 3-4 months now. Due to an accident I am now doing office work and Excel is my new challenge. with help like Mr Excel and you folks I will master it, it will not beat me.

UPDATE .........UPDATE.........I found it! =HYPERLINK("#"&CELL("address",INDEX(Dates,MATCH(B2,Dates,0))),B2)

I named column D with all the dates as "dates" and the above formula gets todays date from B2 and makes a hyperlink to the cell containing that date. Click the hperlink and you end up where you are suppose to be. Thanks for the help, I knew if I chewed on this long enough I would come up with an answer. Thanks again.
 
Upvote 0
Instead of using B2 you could use Today()

Like this:
=HYPERLINK("#"&CELL("address",INDEX(Dates,MATCH(TODAY(),Dates,0))),TODAY())

Assuming you planned to always enter todays date in B2
 
Last edited:
Upvote 0
Instead of using B2 you could use Today()

Like this:
=HYPERLINK("#"&CELL("address",INDEX(Dates,MATCH(TODAY(),Dates,0))),TODAY())

Assuming you planned to always enter todays date in B2

TRUE, But B2 is already set with Today() that's why I referenced it.

One snag :( I didn't know or realize is hyperlinks will not work on a locked or protected page. So Actually Im back to square one. I thought I had it, and I did until I locked the page all the macros work but the Hyperlink doesn't. Darn it. Any ideas? The page is protected or locked so only the cells requirinig data input can be accessed. Cells with formulas are locked. Several people view this spreadsheet throughout the day and I got tired of putting the formulas back in when they wiped them out everyday. So I tried to make it idiot proof, problem is the company hires smarter idiots. Like me!
 
Upvote 0
Try using this script.

It looks down column D of active sheet for Todays Date

Code:
Sub Goto_Today()
'Modified  2/10/2019  4:42:46 AM  EST
Application.ScreenUpdating = True
Dim SearchString As String
Dim SearchRange As Range
SearchString = Date
Dim lastrow As Long
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
Set SearchRange = Range("D1:D" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
SearchRange.Select
End Sub
 
Upvote 0
my one line macro goes to the cell containing today's date - I thought that was what you wanted.............
 
Upvote 0
my one line macro goes to the cell containing today's date - I thought that was what you wanted.............

That is what I want but If I enter what you have posted as a macro **=Cells(cells(1,15),3.Select** I get errors saying There's a problem with this formula. But doesn't tell me what it is or offer any help, Says If I'm entering TEXT to …..

I can use the match formula and it tells me what row the date is located on but doesn't move the cursor to that location. I don't understand what you have or I'm not entering it correct. I have looked at it and tried several ways to enter it and not having any luck.

GF
 
Upvote 0
You're missing a closing bracket in that VBA line.

Code:
Cells(cells(1,15),3[B][COLOR="#FF0000"])[/COLOR][/B].Select
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top