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

[GFelts]

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

 

[My Aswer Is This]

Not sure how this one simple line of code will select the cell with Todays Date. When I tried it I received a error code. Not sure what you mean about brackets being fixed will solve the issue.
Show me please the proper one line code. The orginal poster did not comment about my code. It works for me even if it is not just one of code.

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

Cells(cells(1,15),3[B][COLOR=#ff0000])[/COLOR][/B].Select

 

[MARK858]

Did you put the formulas that oldbrewer stated for the cells? He is using a combination of the code and formula to get the result.

 

[My Aswer Is This]

I'm not able to read his code. It's all scrambled around on the screen at least for me it is.

Did you put the formulas that oldbrewer stated for the cells? He is using a combination of the code and formula to get the result.

 

[MARK858]

I'm not able to read his code. It's all scrambled around on the screen at least for me it is.

It is not just you, it is a typical way he creates his posts so you end up doing a bit of scrolling to find the info

 

[My Aswer Is This]

Thanks. I had seen this in previous postings also always wondered if it was just me. Not sure how using a bunch of formulas would be better. But again there are always 10 different ways to do most anything with Exceel.

It is not just you, it is a typical way he creates his posts so you end up doing a bit of scrolling to find the info

 

[MARK858]

Not sure how using a bunch of formulas would be better. But again there are always 10 different ways to do most anything with Exceel.

Similar to what you have stated I wouldn't say better just different, anyway just so that you know I needed to make a couple of adjustments to your code to work for my UK date setup. Can you test to see if it still works for you?

Sub Goto_Today()
    'Modified  2/10/2019  4:42:46 AM  EST
    Application.ScreenUpdating = True
    Dim SearchString [COLOR="#FF0000"]As Long[/COLOR]
    Dim SearchRange As Range
    SearchString = [COLOR="#FF0000"]CLng(Date)[/COLOR]
    Dim lastrow As Long
    lastrow = Cells(Rows.Count, "D").End(xlUp).Row
    Set SearchRange = Range("D1:D" & lastrow).Find([COLOR="#FF0000"]CDate(SearchString)[/COLOR], LookIn:=xlValues, lookat:=xlWhole)
    If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
    SearchRange.Select
End Sub

 

[My Aswer Is This]

Yes this worked for me. So why would mine work for me and not for you.

Similar to what you have stated I wouldn't say better just different, anyway just so that you know I needed to make a couple of adjustments to your code to work for my UK date setup. Can you test to see if it still works for you?

Sub Goto_Today()
    'Modified  2/10/2019  4:42:46 AM  EST
    Application.ScreenUpdating = True
    Dim SearchString [COLOR=#ff0000]As Long[/COLOR]
    Dim SearchRange As Range
    SearchString = [COLOR=#ff0000]CLng(Date)[/COLOR]
    Dim lastrow As Long
    lastrow = Cells(Rows.Count, "D").End(xlUp).Row
    Set SearchRange = Range("D1:D" & lastrow).Find([COLOR=#ff0000]CDate(SearchString)[/COLOR], LookIn:=xlValues, lookat:=xlWhole)
    If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
    SearchRange.Select
End Sub

 

[MARK858]

Yes this worked for me. So why would mine work for me and not for you.

Different date formats in different regions, nearly always causes issues.
VBA is always in mm/dd/yyyy and so it becomes an issue an issue in regions where the format is dd/mm/yyyy so you end up looking at the underlying value then converting it.

 

[GFelts]

I'm not able to read his code. It's all scrambled around on the screen at least for me it is.

How do you think I feel? I know little to nothing about VBA or code and I am new to excel and learning formulas. I couldn't make heads or tails out of it. I put it everywhere I knew or could find to put it and got nothing but errors. I thought it was me, and afraid to show my stupidity by asking where or how to run it. Glad to hear it wasn't just me.
But I am learning alot about errors!

 

[oldbrewer]

thanks mark it is tyhe whole macro lol