none
.Find RRS feed

  • Question

  • Good Morning!

    I've written:

    Sub formmacro()
    Dim I As Date
    Range("A:A").Select
    I = "1/1/2015"
    Do While Selection.Find(I) Is Nothing
    I = I + 1
    Loop
    Selection.Find(I).Select
    End Sub

    When it runs the find it recognises 01/11/2015 as I instead of 01/01/2015 which i have set it as.

    Any Idea's why its doing this?

    Soliddrew!


    • Edited by Soliddrew Thursday, February 25, 2016 11:12 AM
    Thursday, February 25, 2016 10:07 AM

Answers

  • >>>Im not sure why the macro isnt working because when i ctrl + f to find, it doesnt select november dates when i look for "1/1/2015", just january dates.

    According to your description, I have reproduced this issue, most common problem I see is matching dates. It's usually due to date format differences between the What:= and LookIn:= args. and not understanding that it's a text comparison. So I sugget that you could refer to below:

    Sub TestDate()
        Dim I As Date
        Range("A:A").Select
        I = "1/1/2015"
        
        last = Day(dhLastDayInMonth(CDate(I))) - 1
        Do While Selection.Find(What:=Format(I, "dd-mmm-yy"), LookIn:=xlValues) Is Nothing
           If (Day(CDate(I)) <> last) Then
              I = I + 1
           Else
             Exit Do
           End If
        Loop
        If Not Selection.Find(What:=Format(I, "dd-mmm-yy"), LookIn:=xlValues) Is Nothing Then
           Selection.Find(What:=Format(I, "dd-mmm-yy"), LookIn:=xlValues).Select
        End If
    
    End Sub
    
    Function dhLastDayInMonth(Optional dtmDate As Date = 0) As Date
        ' Return the last day in the specified month.
        If dtmDate = 0 Then
            ' Did the caller pass in a date? If not, use
            ' the current date.
            dtmDate = Date
        End If
        dhLastDayInMonth = DateSerial(Year(dtmDate), _
         Month(dtmDate) + 1, 0)
    End Function
    

    • Marked as answer by Soliddrew Monday, February 29, 2016 1:07 PM
    Monday, February 29, 2016 9:10 AM

All replies

  • I'd like to know value of column [A].
    Can you share your file via cloud storage such as OneDrive, Dropbox, etc?
    Thursday, February 25, 2016 11:44 AM
  • Column A contains a variation of date's from 02/04/2013 to 24/02/2016 (in this format). I Have made it loop because the date I look for is extracted from a form in the format of MMM/YY so excel just defults to the first of that month. I.e if i chose Feb-15 it will read it as 01/02/2015 so i've looped it so it finds the closest date after 01/02/2015.
    Thursday, February 25, 2016 11:56 AM
  • >>>Column A contains a variation of date's from 02/04/2013 to 24/02/2016 (in this format). I Have made it loop because the date I look for is extracted from a form in the format of MMM/YY so excel just defults to the first of that month. I.e if i chose Feb-15 it will read it as 01/02/2015 so i've looped it so it finds the closest date after 01/02/2015.<<<

    According to your description, please correct me if I have any misunderstandings on your question, you could use CDate function to convert String to Date, refer to codes below:

    Sub DemoDate()
    
        Dim I As Date
        Range("A:A").Select
        I = "1/1/2015"
        Do While Selection.Find(CDate(I)) Is Nothing
        I = I + 1
        Loop
        Selection.Find(CDate(I)).Select
    
    End Sub

    In addition could you provide more information about your issue, for example sample file, screenshot etc., that will help us reproduce and resolve it.

    Thanks for your understanding.


    Friday, February 26, 2016 1:49 AM
  • Hi Soliddrew,

    Have your issue been solved by David_JunFeng's advice/code?
    If not, I ask you again to share your file via cloud storage such as OneDrive, Dropbox, etc.
    Sorry, I cannot understand how the value in column [A] is.

    Regards.
    Friday, February 26, 2016 4:36 AM
  • This is my column A:

    24-Mar-15
    17-Jun-15
    07-Jul-15
    14-Jul-15
    14-Aug-15
    17-Aug-15
    19-Aug-15
    08-Sep-15
    16-Sep-15
    16-Sep-15
    22-Sep-15
    22-Sep-15
    22-Sep-15
    13-Oct-15
    13-Oct-15
    03-Nov-15
    03-Nov-15
    03-Nov-15
    03-Nov-15
    03-Nov-15
    03-Nov-15
    11-Nov-15
    11-Nov-15
    11-Nov-15
    11-Nov-15
    02-Dec-15
    05-Feb-16
    05-Feb-16
    10-Feb-16
    10-Feb-16
    10-Feb-16
    19-Feb-16
    19-Feb-16
    19-Feb-16
    19-Feb-16
    19-Feb-16
    19-Feb-16
    23-Feb-16
    23-Feb-16
    23-Feb-16
    23-Feb-16
    23-Feb-16
    23-Feb-16
    Friday, February 26, 2016 8:59 AM
  • Thankyou for your suggestion David, Unfortunatly the find still selects a November date instead of a january date.

    Maybe you could think of a better way to locate the information?

    Im not sure why the macro isnt working because when i ctrl + f to find, it doesnt select november dates when i look for "1/1/2015", just january dates.

    Soliddrew! =)

    Friday, February 26, 2016 9:02 AM
  • Hi Soliddrew,

    Thank you for reply.

    I'm afraid the data you provided is displayed data, instead of original value.
    As you know, Excel displays something other than its real value.
    i.e. when you input "2015/5/24" in cell A1, it can be seen like "May-24-2015",  "5/24/2015", "2015-5-24", etc. But the real value in cell A1 is "42148".
    So, I want you to provide your file, instead of copied text.

    Regard.

    • Edited by Ashidacchi Friday, February 26, 2016 10:19 AM
    Friday, February 26, 2016 9:32 AM
  • >>>Im not sure why the macro isnt working because when i ctrl + f to find, it doesnt select november dates when i look for "1/1/2015", just january dates.

    According to your description, I have reproduced this issue, most common problem I see is matching dates. It's usually due to date format differences between the What:= and LookIn:= args. and not understanding that it's a text comparison. So I sugget that you could refer to below:

    Sub TestDate()
        Dim I As Date
        Range("A:A").Select
        I = "1/1/2015"
        
        last = Day(dhLastDayInMonth(CDate(I))) - 1
        Do While Selection.Find(What:=Format(I, "dd-mmm-yy"), LookIn:=xlValues) Is Nothing
           If (Day(CDate(I)) <> last) Then
              I = I + 1
           Else
             Exit Do
           End If
        Loop
        If Not Selection.Find(What:=Format(I, "dd-mmm-yy"), LookIn:=xlValues) Is Nothing Then
           Selection.Find(What:=Format(I, "dd-mmm-yy"), LookIn:=xlValues).Select
        End If
    
    End Sub
    
    Function dhLastDayInMonth(Optional dtmDate As Date = 0) As Date
        ' Return the last day in the specified month.
        If dtmDate = 0 Then
            ' Did the caller pass in a date? If not, use
            ' the current date.
            dtmDate = Date
        End If
        dhLastDayInMonth = DateSerial(Year(dtmDate), _
         Month(dtmDate) + 1, 0)
    End Function
    

    • Marked as answer by Soliddrew Monday, February 29, 2016 1:07 PM
    Monday, February 29, 2016 9:10 AM
  • Thank you all for your replys! Im glad this is finally sorted! Thanks David =)
    Monday, February 29, 2016 1:08 PM