gotoToday macro not working, please help
gotoToday macro not working, please help
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: gotoToday macro not working, please help

  1. #1
    Board Regular
    Join Date
    Jan 2014
    Posts
    118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default gotoToday macro not working, please help

    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

  2. #2
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    9,248
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: gotoToday macro not working, please help

    Try using CLng(Date) in your find.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, Excel jeanie, RoryA addin (Win & Mac) or Borders-Copy-Paste

  3. #3
    Board Regular
    Join Date
    Jan 2014
    Posts
    118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: gotoToday macro not working, please help

    Quote Originally Posted by MARK858 View Post
    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

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    11,698
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    3 Thread(s)

    Default Re: gotoToday macro not working, please help

    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  5. #5
    Board Regular
    Join Date
    Jan 2014
    Posts
    118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: gotoToday macro not working, please help

    Quote Originally Posted by My Aswer Is This View Post
    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".

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    11,698
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    3 Thread(s)

    Default Re: gotoToday macro not working, please help

    So you are searching for todays date in row(1) is that correct?
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  7. #7
    Board Regular
    Join Date
    Jan 2014
    Posts
    118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: gotoToday macro not working, please help

    Quote Originally Posted by My Aswer Is This View Post
    So you are searching for todays date in row(1) is that correct?
    yes, correct. this is crazy. thanks for helping me.

  8. #8
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    11,698
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    3 Thread(s)

    Default Re: gotoToday macro not working, please help

    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)
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  9. #9
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    11,698
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    3 Thread(s)

    Default Re: gotoToday macro not working, please help

    Quote Originally Posted by sumhungl0 View Post
    yes, correct. this is crazy. thanks for helping me.
    Show me exactly how your entering the date and where you are entering the date.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  10. #10
    Board Regular
    Join Date
    Jan 2014
    Posts
    118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: gotoToday macro not working, please help

    ††
    lets try this, here is the file i just made up. all i did was type "1mar" in to cell a1. after hitting enter excel formatted it as a date. i then dragged the cell across row 1 to auto fill the rest.

    hope this link works. lemme know. thanks again.

    https://drive.google.com/file/d/0B6m...ew?usp=sharing

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com