Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select Cell that equals Today (2K/2K3)

    I have =today() in cell A1 and column A from A12 down, has every day of the year in the format dd/mmm/yy, 16-May-06, 17-May-06,etc, ad infinitum. Is there a formulae to select the date that corresponds to the date in A1 on opening the worksheet, or is it a VBA solution? I have looked at Index and Match, but have had no joy.

    Thank you.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select Cell that equals Today (2K/2K3)

    Hi there

    This is a VBA solution for your. Right hand mouse click the worksheet tab and select View Code and paste this into the editor.

    Private Sub Worksheet_Activate()
    Dim strDate As Date


    Range("A1").Select

    strDate = ActiveCell.Value



    Cells.Find(What:=strDate, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=True).Activate
    End Sub
    Jerry

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select Cell that equals Today (2K/2K3)

    A formula cannot select another cell, it can only return a value in the cell that contains it. To select a cell, you need VBA code. For example:
    - Activate the Visual Basic Editor (Alt+F11).
    - Double click the ThisWorkbook node belonging to your workbook.
    - Paste the following code into the module that appears:

    Private Sub Workbook_Open()
    Dim wsh As Worksheet
    Dim rng As Range
    On Error GoTo ErrHandler
    Application.EnableEvents = False
    ' Substitute sheet name below
    Set wsh = Worksheets("Sheet1")
    wsh.Activate
    Set rng = wsh.Range("A12:A377").Find(What:=wsh.Range("A1"), _
    LookIn:=xlValues, LookAt:=xlWhole)
    If Not rng Is Nothing Then
    rng.Select
    End If
    ExitHandler:
    Application.EnableEvents = True
    Set rng = Nothing
    Exit Sub
    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    - Substitute the correct sheet name.
    - Switch back to Excel, and save the workbook.

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Cell that equals Today (2K/2K3)

    Thank you both for your swift replies. Visitors have arrived at 2100 (local) so I will test tomorrow.

    Why is it wife's won't let you play on computers when you have visitors?

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Cell that equals Today (2K/2K3)

    Hans,

    Thank you for that. Works as advertised.

    Jerry,

    I get a "Compile error: Syntax Error. and the line of code starting: Cells.find.. is in red. Am not able to troubleshoot as I haven't a clue what to do!

    Problem now solved, so thanks to both of you.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select Cell that equals Today (2K/2K3)

    Thanks worked on mine <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> I was thinking afterwards that I should have put an error trap into it like Hans's.
    Jerry

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select Cell that equals Today (2K/2K3)

    If farside tested on Excel 2000, the SearchFormat argument of the Find method was not valid - it was introduced in Excel 2002.

    BTW, the Worksheet_Activate event will run when the user switches to the worksheet from another worksheet within the same workbook. It won't run when the workbook is opened. That's why I used the Workbook_Open event.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select Cell that equals Today (2K/2K3)

    <img src=/S/cool.gif border=0 alt=cool width=15 height=15> thanks

    I was taking the assumption of when farside opened the worksheet as he suggested but I see your point <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select Cell that equals Today (2K/2K3)

    My code only runs when you open the workbook (the .xls fdile). Jerry's code only runs when you switch from another worksheet within the workbook to the one with the dates. If you want the cell to be selected in both cass, you must add both pieces of code: the Workbook_Open procedure to the ThisWorkbook module, and the Worksheet_Activate procedure to the worksheet module, Here is a version of Jerry's code with error handling, and without the argument that wouldn't work in Excel 2000:

    Private Sub Worksheet_Activate()
    Dim rng As Range
    On Error GoTo ErrHandler
    Application.EnableEvents = False
    Set rng = Range("A12:A377").Find(What:=Range("A1"), _
    LookIn:=xlValues, LookAt:=xlWhole)
    If Not rng Is Nothing Then
    rng.Select
    End If
    ExitHandler:
    Application.EnableEvents = True
    Set rng = Nothing
    Exit Sub
    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  10. #10
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Cell that equals Today (2K/2K3)

    Hans (and Jerry!)

    Thank you for your comprehensive reply on this issue. The workbook and worksheets perform as you have briefed.

    Big thank you:-)

Posting Permissions

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