Select todays cell
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Select todays cell

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    My column A is a list of dates for the whole year, i need to write a macro that will automatically move the cursor to the current date, any ideas please.

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This one, though recorded will work. In Sheet2!A1, I wrote =Today()

    This macro goes to Sheet2!A1, copies the date, then finds it in column A of Sheet1.

    Sub Macro3()
    Application.ScreenUpdate = False
    Sheets("Sheet2").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Cells.Find(What:="02/24/2002", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    MatchCase:=False).Activate
    Application.ScreenUpdating = True
    End Sub


    I recorded it then added screen updating. Might be an easier way.
    ~Anne Troy

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    If your date is a value (ie not =today() or now()) then you can use this
    Cells.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    MatchCase:=False).Activate
    End Sub

    Derek



    [ This Message was edited by: Derek on 2002-02-24 17:25 ]

    [ This Message was edited by: Derek on 2002-02-24 17:29 ]

  4. #4
    New Member
    Join Date
    Feb 2002
    Location
    Bloomington, MN
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could try this as well.

    Sub GoToNow()
    Range("A1").Select
    While ActiveCell.Value <> Int(Now)
    ActiveCell.Offset(1, 0).Select
    Wend
    End Sub

    Jay

  5. #5

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Or more simply :-

    Columns(1).Find(What:=Date, After:=[A65536]).Select

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Autolycus
    That a neat bit of code. Is there any way you can select today's date regardless of whether it is a value or the result of a formula, eg =today(). I can only seem to do one or the other but not both.
    regards
    Derek

  7. #7

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-24 20:23, Derek wrote:
    Autolycus
    That a neat bit of code. Is there any way you can select today's date regardless of whether it is a value or the result of a formula, eg =today(). I can only seem to do one or the other but not both.
    regards
    Derek

    Columns(1).Find(What:=Format(Date, "d-mmm-yy"), After:=[A65536]).Select

    Or whatever date format in in column 1.

  8. #8

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Correction. Should read :-

    Columns(1).Find(What:=Format(Date, "d-mmm-yy"), After:=[A65536], LookIn:=xlValues).Select

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Autolycus
    Works great! Many thanks
    Derek

User Tag List

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