Posts by Smudge.Smith

    Hi RoyUK

    Thanks very much for your prompt assistance. I hadn't heard of EOMONTH before but looks as though it should work based on what I've read.

    I added three extra rows with December dates, unfortunately the code produce a message that it was unable to find the required rows when 12 (December)

    I think there is something missing from your amended code that doesn't take into account the start date of Dec being in the previous month?


    Code
    1. lFrom = CLng(DateValue(Format("1/" & lMth & "/" & Year(Now()), "dd/mm/yyyy")))

    Wouldn't this assume that the variable lFrom is in 2022 (as I set the clock forward to test?)based on the Year(Now) function?



    Code
    1. lFrom = CLng(DateValue(Format("1/" & lMth & "/" & Year(Now()), "dd/mm/yyyy")))
    2. If lMth < 12 Then
    3. lTo = CLng(DateValue(Format("1/" & lMth + 1 & "/" & Year(Now()), "dd/mm/yyyy")))
    4. Else
    5. 'lTo = CLng(DateValue(Format("1/01/" & Year(Now()) + 1, "dd/mm/yyyy")))
    6. lTo = CLng(Application.WorksheetFunction.EoMonth(Date, -1))
    7. End If


    Many thanks and best wishes


    Chris

    Apologies for revisiting a thread which I had marked as resolved - but I have found an issue which I think I need some help getting my head around with regards to selecting the month to be exported.

    Currently the VBA appears to rely on the selected month being in the current year, so each of the 12 months in this year are available to choose if the report is run in the current year.

    However, in practice, the selected month should always be a month behind when the report is run.

    Eg the month end report for Jan will be run in Feb, June's report will be run in July etc..

    This process falls down when Decembers data is required, as the report will be run in Jan and therefore previous December will no longer be in the same year as the report is run.

    I've been fiddling around with IF statements trying to make it work but unfortunately I can't seem to find the right terminology, especially as we're not actually at a point to test it (ie it's not January yet and I'm trying to get the previous months data).


    Many thanks


    Smudge

    Hello people


    As a continuation of my previous road to enlightenment project ie learn some useful VBA, I requested help to select rows of data that contained the current month and export those rows and selected columns into a CSV ready to be exported into another program. Thanks to everyone who provided advice or updated my code.


    My issue now is how to select any month within a calendar year rather than just the current month. I was thinking of a drop down menu where you can select a month but unfortunately I have no idea how to implement this into my current code. ( I saw something similar elsewhere but unfortunately I couldn't make it work for me so binned it).


    Anyone out there who can set me in the right direction?


    Many thanks once again.


    Also, on another note (apologies to admin if 2nd question is not allowed under the same heading), I export the headers separately as I believe that they are not copied over due to to not being recognised as a date in the error capture routine.

    When the relevant date rows are then copied to the new sheet, I end up with a blank rows between the headers and the data.

    I know I could just delete the blank rows but was wondering if there was a more elegant way to move the data up so it sits below the the headers?


    TVM


    Smudge


    Hello again


    Further to my recent post about exporting selected Excel columns to CSV which was very kindly resolved by gijsmo , I decided I wanted to build on that and export only those rows where the date contains the current month.

    The idea is that as a monthly report, I don't have to mess about selecting dates etc... the code just looks for the current month in column C and selects those rows for export.


    So I used lr and r to find how many rows of data there are and then count through them to see if the row contains an actual date and if so, if the date contains the current month. The idea is then to copy these rows and selected columns into a CSV file for importing to another program.

    (original VBA columns selection which worked previously is remmed out).


    Unfortunately, when I get to the save as CSV screen, nothing is copied over and I don't know why. I would expect to see rows 3,4,5,6 and 7 appear in the new CSV as their dates in column C fall in May (which as of today is the current month).


    Anyone tell me where I've gone wrong... and point me in the right direction please?


    Many thanks


    Smudge



    Hi


    I've got a relatively simple task of exporting selected columns into a CSV ready to be used in another program.

    The macro seems to work as advertised with the exception that the dates do not keep their UK format but are resolved into USA format.

    The locales are set correctly on both my PC and the PC where the original xls file originates.


    Anyone know what tweak I need to make to the macro to make it export the date in UK format?


    I've attached a simplified version of the excel spreadsheet along with the VBA coding.



    Many thanks


    Smudge

    Could I ask for help writing a formula please?


    I want to see if a value in column E (Ticket Number) matches one in column A (Ref Number). If so count the ID number for each Class where the Ticket Number has been validated against a Ref Number.


    ie in the table the first Ticket Number 538672055899436 also matches in Ref Number (column A), which is assigned to Class A and has ID 0 (Highlighted Yellow)

    Ticket Numbers 186218746914657 and 270138205991737 (Highlighted green) are both matches in the Ref Number column, are both Class B and both have ID =4 ( so the count in the table is 2)


    Ticket Number 162669418927643 (highlighted blue is also a match and so 3 A is counted


    And so on...


    I have a feeling this is a combination of Index /Match and CountIFS but I just can't get my head around the logic in writing the formula ?(


    Could someone more knowledgeable than me help me out please?


    Many thanks


    Smudge

    Files

    • Book1.xlsx

      (11.86 kB, downloaded 59 times, last: )

    Hi folks


    My aim is to show the totals as a percent, how long it takes people from applying for a course to attending .


    I have a list of two dates:

    An Application Date (the date contact is made to attend a training course) and the Course Date (the date of the course).


    From a list of Course Dates in a given month, (in this example March 2019) I want to calculate the difference between the Application Date and the Course date, for each candidate in months and then group those totals by months and shown as a percentage.


    At the moments I'm typing this manually and I'm looking for a formula that can work these numbers out automatically.

    Instead of keep typing manually I was trying to use the CHOOSE formula to give the months in Column F but don't know how to select just one month consecutively from the list so maybe I'm going the wrong way with that ?


    Any formula guru's out there able to assist?


    1. Find the month of course dates from Column B Displayed in F8 - I used =TEXT(B2,"MMM") function.

    2. Find how many course places there were for that month - =SUM(B:B) varies depending on data loaded into columns A and B

    3. Group Column A by month and find totals for each month displayed in F, G - (I just count manually)

    4. Calculate as a percentage of all course dates, each total for each month H - simple calc based on above)

    5. Show totals for each month and as a percentage - as per sheet


    Many thanks


    Smudge

    Almost there...but not quite.


    Found that when the VBA autofilter is applied, it converts the date to American format which then seems to halt the process.

    I've added Cstr (key) to the code at line 22 which converts the date to a string, which then allows it to be copied back to the relevant cell in the template.



    All of the dated templates are produced but the placing of the data gets more and more offset as the VBA runs as the files are produced...

    Hi Mumps


    Thank you so much for the time and effort you have put into this. I really appreciate it - but half way through, the VBA being run I get the dreaded VBA run-time error 1004 : Application-defined or object-defined error message.


    Stepping thru the code I can see the array being formed; the code applies a filter to the headers in Book1 with nothing selected. It then opens the template and fills in the date at D9


    Something then happens as D13 shows 0 where I would expect it show 1 (as there is one row that contains the date 08/12/2020...)

    It then fills in B20, B21 and F20 with the header title instead of the row data... and then bombs out with the run time error 1004...


    Any ideas?


    Many thanks once again


    Hi Mumps


    Thank you for the reply. The number 35 isn't a 'calculated' value as such. Its just a numerical figure that is a fixed value that needs to be in place in column G for each populated row for when the file is saved and then imported into another computer program. Because of this the structure of the template is very specific.


    Also the files will exist in the same folder as Book1


    Many thanks

    Hi Folks


    I have a laborious task of copying data from a list (Book1) into date based templates.

    What I am after is some VBA to loop through the data in Book1 and create a new file for each distinct date in the file from the master template.


    This is how I see the process:


    Find the beginning of the date range in Book1

    Get number of rows with that date

    Open template


    Insert date @ D9 in


    Insert count of array @ D13 ‘to give a count of rows


    Insert Time array @ B20


    Insert REF array @ F20


    Insert value of ‘35’ @ G20 ‘for length of array


    Save file named as date


    Close template


    Next date...ie repeat process until last date.



    I have provided attachments for a visual representation

    You will see that the first date in the list (colour coded in yellow) has found one row associated with the date 08/12/20 and copied the data over to the relevant cells and saved as a file named as the date ( 08DEC020)


    The next date found is 17/12/2020 and so all relevant rows associated with this date (highlighted in green) are copied over to a new template which is then saved as that date (17DEC2020)... and so on for each distinct date occurrence.


    The list is variable in length.

    The list is sorted in date order for ease of use but may not be presented that way initially.


    This seems to be a situation that Excel is crying out for to be automated via VBA (and save my typing fingers and eyesight).


    Any knowledgeable people out there can help me with this?


    Many thanks


    Smudge

    Files

    • Book1.xlsx

      (18.49 kB, downloaded 61 times, last: )
    • 08DEC2020.xlsx

      (10.86 kB, downloaded 57 times, last: )
    • 17DEC2020.xlsx

      (11.14 kB, downloaded 60 times, last: )