Find and replace error using VBA

  • Hi,


    I have a data set of the following form:


    [Blocked Image: http://i.imgur.com/FxA7wIK.jpg]


    The code I used is:


    Code
    Range("C:D").Select
        Selection.Replace What:=".", Replacement:=":", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Selection.End(xlUp).Select


    Any suggestions on What modification I might need to do? [I'm using Excel 2013]


    Have asked the same question at Mr.Excel forums at http://www.mrexcel.com/forum/e…l-basic-applications.html

  • Re: Find and replace error using VBA


    Don't replace... use Custom formatting to display the dates as you require.


    The 'wrong' dates are because Excel uses American date format as standard. Any date where the day is less than 12 will be assumed to be American format (mm/dd/yy) and so switches the day/month figures.

  • Re: Find and replace error using VBA


    Quote from cytop;727743

    Don't replace... use Custom formatting to display the dates as you require.


    The 'wrong' dates are because Excel uses American date format as standard. Any date where the day is less than 12 will be assumed to be American format (mm/dd/yy) and so switches the day/month figures.


    i tried it, but didn't succeed. With the sample data shown in image, could you please tell me how I can do that.


    Setting Custom format as "dd-mm-yyyy hh:mm AM/PM" or "dd-mm-yyyy hh:mm" leaves the data as it is with no changes.

Participate now!

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