gotoToday macro not working, please help

sumhungl0

Board Regular
Joined
Jan 1, 2014
Messages
119
good afternoon,
i have a xlsm file that has a button to find the cell with todays date and scroll to it. the sheet is a rolling calendar with dates across the top/columns (1:1). the button has worked for 5+ years and is still working. now i am recreating the sheet for something else but the same code does not work. it gives me run-time error '91': object variable or with block variable not set, and i have no clue why this wont work on a freshly created workbook but still works on the original. i am using excel 2013. i threw a few msgbox lines in to test to see if the code would find the date but i dont think it is finding the date at all. i went back and made sure the cells are formated to be a date and still nothing. am i missing a reference or something? i cant figure this out. can someone please help? thank you and have a great day.

here is the code i have:
Code:
Sub GoToToday() 'scroll to today'Cells.Find(Date, , xlValues, xlWhole).Select
Columns(1).Find(Date, , xlValues).Select
ActiveWindow.ScrollColumn = ActiveCell.Column
End Sub

i have also tried the following codes i found during my search for a fix:
Code:
Sub scrollToday()
With Range("1:1")
Set c = .Find(DateValue(Now()), LookIn:=xlValues)
If Not c Is Nothing Then
MsgBox .Address
.Select
ActiveWindow.ScrollColumn = c.Column
End If
End With
End Sub
Sub Find_Todays_Date()
Dim FindString As Date
Dim rng As Range
FindString = Date
With Sheets("Sheet1").Range("1:1")
Set rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumn, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox "Nothing found"
End If
End With
End Sub
Sub tryme()
LastCol = Cells(Columns.Count, "1").End(xlPrevious).Column
For j = 1 To LastCol
 If Cells(j, "1").Value = Date Then
   Cells(j, "1").Select
   Exit For
 End If
Next j
End Sub
Sub findToday()
    Dim sht As Worksheet
    Dim rng As Range
    Dim r As Range


    Set sht = ThisWorkbook.Worksheets("MOF")
    Set rng = sht.Range("1:1")
    
    Set r = rng.Find(Date)
    MsgBox r.Address
    If Not r Is Nothing Then r.Select
End Sub
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Try using CLng(Date) in your find.
thanks for the responce, still same thing. its acting like the find is not working, i put another msgbox in and it stops on the line before.
Code:
Sub GoToToday() 'scroll to today'Cells.Find(CLng(Date), , xlValues, xlWhole).Select
Range("1:1").Find(CLng(Date), , xlValues).Select
MsgBox Selection
ActiveWindow.ScrollColumn = ActiveCell.Column
End Sub
 
Upvote 0
Try this:
Code:
Sub GoToToday()
'Modified 3-21-18 4:00 PM EDT
Dim SearchString As String
Dim SearchRange As Range
SearchString = Date
Dim LastColumn As Long
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Set SearchRange = Range(Cells(1, 1), Cells(1, LastColumn)).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox "Not Found": Exit Sub
SearchRange.Select
ActiveWindow.ScrollColumn = ActiveCell.Column
End Sub
 
Upvote 0
Try this:
Code:
Sub GoToToday()
'Modified 3-21-18 4:00 PM EDT
Dim SearchString As String
Dim SearchRange As Range
SearchString = Date
Dim LastColumn As Long
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Set SearchRange = Range(Cells(1, 1), Cells(1, LastColumn)).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox "Not Found": Exit Sub
SearchRange.Select
ActiveWindow.ScrollColumn = ActiveCell.Column
End Sub
no error but i get the msgbox with "Not Found".
 
Upvote 0
In one of your post it says search column(1)
Then in another post you say:
Range("1:1")

Range("1:1") is row (1)
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

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