Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Apr 2016
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Looking for help searching for a date in excel VBA

    Hi all,

    I've just joined the forum and already throwing out some questions.. but before I do, a little about me.

    I'm just trying to learn VBA for Excel (very slowly so far) and I appologise if I ask a stupid question, but here goes..

    I'm trying to create a year planner with the dates along row 1 formatted to "dd mmm", each sheet is a separate month with a 13th sheet being the full year (thats the easy bit).
    within this workbook I have writen a Macro to select the tab with this months date and then select the cell containing todays date within that sheet, (with me so far? ), my problem.. the 13th sheet is Auto created by the previous 12 sheets with the dates being transfered using formula "=Apr!T1" and so on.. now.. how does Excel actually store that cells content so I can have this 13th sheet also jump to todays date?

    here is what I have tried and I'm thinking all I need to do is change the search string but I have no idea what to..

    Private Sub Worksheet_Activate()
    Dim FindString As Date
    Dim Rng As Range


    FindString = CLng(Date)
    With Rows("1:1")
    Set Rng = .Find(What:=FindString, _
    After:=.Cells(.Cells.Count), _
    LookIn:=xlValues, _
    LookAt:=xlWhole, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False)
    If Not Rng Is Nothing Then
    Application.Goto Rng, True
    Else
    'Give a message that todays date was not found
    MsgBox "Nothing found"
    End If
    End With
    End Sub


    if I do a manual search (Ctrl-F) and search for the text "29 Apr" everything works fine.. but how do I get the Date to be converted to this format before the search so I can do it from within VBA Automatically every day?

    any help is greatly appreciated..

    kind regards

    Rob
    Last edited by ShuggyShug; 2016-04-29 at 18:46.

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,853
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Try
    Code:
      Dim FindString As String
      Dim Rng As Range
      
      'Searching as text
      FindString = Format(Date, "dd mmm")
      With ActiveSheet.Rows("1:1")
        Set Rng = .Find(What:=FindString, After:=.Cells(1), _
              LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
              SearchDirection:=xlNext, MatchCase:=False)
        If Not Rng Is Nothing Then
          Application.Goto Rng, True
        Else
          'Give a message that todays date was not found
          MsgBox "Nothing found"
        End If
      End With
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    ShuggyShug (2016-05-01)

  4. #3
    New Lounger
    Join Date
    Apr 2016
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you for your reply Andrew, though sadly it still results in the message box saying "nothing Found"

    is there another way to copy the date from one sheet to another that might make it easier?


    Edit:

    Got it!! Thank you Andrew for pointing me in the right direction.. I edited my formula in the sheet to "=TEXT(Apr!AE1,"d mmm")" and now with your code it works a treat!! thanks again !! now I can maybe go to bed and stop trying to figure this out lol

    many thanks

    Rob
    Last edited by ShuggyShug; 2016-04-29 at 20:50.

Posting Permissions

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