VBA is searching under US Date range and not UK.

  • [xpost][/xpost]

    I am trying to run this macro i have found, however when i input the date range 06/04/20 - 05/04/21, it also returns the value 08/04/21.

    This is because I believe the macro is seeing my input as 04/06/20 - 04/05/21. If i input the range in american format then it returns the value as expected, however I am in the UK and that's not ideal.

    Any idea on how I can rectify this, as i have looked all over the net and can't seem to find anything to help.copy and archive.xlsm

  • Hello,


    You have to be coherent with your own regional date format ...


    You could test following instruction

    Code
    Call CreateSubsetWorkbook(Format(strStart, "dd/mm/yyyy"), Format(strEnd, "dd/mm/yyyy"))


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Ive run the StartDate and EndDate both ways and it returns the correct data. I am located in the United States.


    ????

    By default ... VBA uses the US format mm/dd/yyyy ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • It works here running both methods of entering the dates. That is why I don't understand .. something is amiss.


    I can enter 08/04/20 or 04/08/20 and it still returns the correct data.


    ???

  • That didn't work either Carim so I sorted the dates in order, and now when i use 06/04/20 as a start date, and 05/04/21 as the end date it puts row 03/04/20 on a new workbook. I'm after the date range I ask for and anything in-between, nothing outside of the requested range.

    If it is me, i might have to try a different macro, but not sure why it's not working right?

  • .

    Here is a similar macro that can be edited to fit your needs. At present it does not create a new workbook and of course it does not

    search Col A.


  • Hello,


    Just checked your file and found out you are using UK format but Locale has a regional setting Azerbaijani - Cyrillic ....


    Is that right ?


    so, in terms of Number Format on your computer you should test : NumberFormat = "dd/mm/yy;@"


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello again,


    You are using an Input Box to populate your local dates as strings ... so you are adding a step of potential mistakes ...


    You could test the following call instruction

    Code
    Call CreateSubsetWorkbook(Format(DateValue(strStart), "dd mm yy"), Format(DateValue(strEnd), "dd mm yy"))

    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Regarding your initial Input Box you are using for allowing User Input, you could ensure the input is checked with following test :


    Code
    Public Sub TestUserInputDate()
    Dim strDate As String
      strDate = InputBox("Insert Your Date using Format dd/mm/yy", "User Input Date", Format(Now(), "dd/mm/yy"))
      If IsDate(strDate) Then
        strDate = Format(CDate(strDate), "dd/mm/yy;@")
        MsgBox strDate & " has a correct format "
      Else
        MsgBox "Wrong date format"
      End If
    End Sub

    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Having dealt with the many issues relating to dates when not using the American date format, this is a very common issue.


    For auto-filtering, best to convert your input date strings into "proper" dates and then into longints as underneath the date format, Excel stores dates as a number.


    Obviously, adding whatever validation routine/s you like to confirm the input dates entered are valid is a good idea - and yes, you would have to add some error checking as a result. However, assuming that dates are entered correctly as dd/mm/yy (or dd/mm/yyyy) strings then the below changes work fine on my version of Excel (I'm in Australia, so dd/mm/yyyy is the default format here).


    Note that I've also tinkered with the filter range in the code snippet below because the code was not using the header row (row 2) in filtering. This meant the row 3 was pretty much copied every time irrespective of the start/end date entered.


  • One way to maybe simplify the hassle of validating the date input could to be use named ranges on the sheet containing the "Run" button. This would provide the opportunity to add Excel cell level validation and formatting which might overcome some of the hurdles when using an InputBox.


    An example of how this could work is attached (again, my default date format is dd/mm/yyyy).


    copy and archive with cell input.xlsm

  • Gijsmo that works, I’ve noticed the dates must be sorted first for it to be effective otherwise it only copies data over up to a point.

    As the new workbook being created will be a reoccurring monthly data archive, is there any way rather than copy the data over to a new workbook cut and paste, then sort the source file to clear the blanks, or cut paste and delete the rows on the original source?

  • I'm not quite sure what you mean by "I’ve noticed the dates must be sorted first for it to be effective otherwise it only copies data over up to a point."

    Auto-filtering should not need data to be sorted first. I think you'll need to provide more information about what you're seeing or perhaps another example workbook as the sample one only has a very limited number of rows.


    And, yes, you can of course cut & paste to the new workbook and remove the "empty" rows with more code.

  • I've just tried using the original code with Carim's tweaks, on the actually spreadsheet and I realised that I only want the macro to work on sheet1 only. The macro is trying to run on all available sheets and I am getting a runtime.

    I have tried to modify it to only work with sheet1 but then get a runtime on my modification.

    Any advice?

Participate now!

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