Wrong format in FIND box

  • Hello.
    With the following code:

    Sub Tides()
    Application.ScreenUpdating = False
        Dim fnd As Range
        On Error Resume Next
        Set fnd = Range("B:B").Find(Range("D199").Value)
        fnd.Resize(14, 3).Copy [I2]
    Application.ScreenUpdating = True
    End Sub

    The format for all my dates are d-mm-yy, but in the FIND box it comes up as mm/dd/yyyy. Because of this the value D199 is not found. I have checked all my regional settings and they are correct.
    If I change all dates to GERNERAL format, the code works, but I want the date format.
    Any help would be much appreciated. Thanks.

  • Re: Wrong format in FIND box

    The format of a cell does not affect the underlying data, simply how it is presented to the user.

    If your cell contains a valid date, then it should be found regardless of the displayed format.

    You need to explain "... in the FIND box it comes up as mm/dd/yyyy". What Find box? If you're using INPUTBOX() or a textbox to accept input and placing in cell D199 then you should use CDATE() ro convert to a true date when adding to the cell.

  • Re: Wrong format in FIND box

    What I mean by the FIND box, is the dialogue box that comes up when you select the find and search function on the far right of the HOME tab in the ribbon. The macro is using this to search for the value in cell D199. I know this as when I have run the macro, I can see the value when I open the 'find and search' dialogue box, only the format is different. E.g. if the value of D199 is 28-12-15, the value in the dialogue box is shown as 12/28/2015.
    I really believe this is causing the problem as when I change the format of all dates to general number, E.g. 28-12-15 = 42625 as a general number, the code works as the value is the same and is able to be found.
    Another weird thing is that this problem only occurs on occasion. Most of the time the macro works.

  • Re: Wrong format in FIND box

    As cytop says, nothing in the code you have posted displays the Find dialogue box; I suspect the problem is in some part of the code that you have not posted.

    However, you will find that there are all sorts of problems when using dates in UK format in VBA. Almost invariably, VBA will try to treat dates in US format. It is quite likely that, when you think it is working on occasions, the problem is still there, but because the UK day number can be interpreted as a month number (i.e. <=12), the program will work but will give results based on an incorrect date.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!