Posts by Smudge.Smith

    I have two functions that operate 'live' on a worksheet:

    1, Fill cell ("A",i) with a pre- determined colour based on the number keyed in at that location.

    2. For a date that is keyed in at ("L",x), do one of three things:

    If the date is less than two years from todays date, colour the cell next to it green and insert text "OK"

    If the date is more than two years from todays date, colour the cell next to red and insert text "Overdue"

    The idea is that as dates are added to rows in column L, it is compared to today's date and as the date approaches a two year anniversary, it will flag up as yellow and being due, after that, if the date isn't updated, and goes over a two year point then will flag up as red and being overdue.

    This seems to work fine sometimes but as I insert dates further down the rows, the blue spinning circle starts to appear and takes longer and longer to think about updating.

    Clearing a cell doesn't always seem to clear the cell next to it either.

    Could someone take a look at my code and offer words of wisdom to optimise it?

    Additionally I've been trying to add an extra condition to function 2 that does this:

    2. If the date in ("L",x) is within two months of its two year anniversary and becoming overdue, colour the cell next to it yellow and insert text "Due"

    I can't seem to get to grips with the logic

    Many thanks


    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?

    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?

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

    Many thanks and best wishes


    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


    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?



    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



    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


    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


    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


    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