£25 - VBA Module Help and Tidy Up

  • Hi


    I am hoping someone will be able to help.


    Attached is a training performance file.


    There is already a VBA code, which I managed to make with some support from forums and my little knowledge.


    We started a new training topic which is only applicable to some staff and i am finding it difficult to achieve this.


    So the VBA formula that currently runs is looking at: The range where the dates are inputted and makes sure that it is within the last 12 months, the date i need the percentage for and the column where leavers are marked.


    I need on top of this to have another option where I can select the "Designation" either as a range or as text (range is preferred), to only look at staff that started work after the date I am looking at.


    In summary:


    The formula should look at a range of two columns and count the biggest date in a single row, and only count if it is within the last 12 months, then to see if they are marked as leavers, then to see if the designation is correct (Based on what I select), also that the staff start date is less than the date i want the percentage for and give me a percentage for the date.
    I would also like a formula without the range to look at dates so that I can use it as my denominator.


    Thank You for all your help.
    J

  • I am rather confused about just what it is you require.


    Where do you want the UDF result to show?
    You say you need a percentage, but what as a percentage of what?


    Quote

    I would also like a formula without the range to look at dates so that I can use it as my denominator.


    No idea what you mean here!


    Can you manually enter your desired result, show how that result was obtained and re-attach the file.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hello,


    You should not worry ...


    KjBox will very soon provide you with your solution ...:smile:

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

  • Sorry for the delay, been very busy here.


    Your file is ready, I will PM you with my PayPal details and attach the file here on receipt of payment.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi KjBox


    Thank You.


    Just one issue, I am not able to select the designations? I would need to dictate to the formula which group of designation for which training.


    Also, am I able to select columns that are not next to each other, I.e if the “previous” was say 10 columns away?


    J

  • Hi,

    • Sorry, my misunderstanding, I thought the UDF would always refer to Unqualified. I will look into that and amend the UDF accordingly.
    • Yes, any 2 columns can be selected irrespective of their location in the table. Just make sure "Previous" is the second column header in the formula.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi,

    • Sorry, my misunderstanding, I thought the UDF would always refer to Unqualified. I will look into that and amend the UDF accordingly.
    • Yes, any 2 columns can be selected irrespective of their location in the table. Just make sure "Previous" is the second column header in the formula.


    Thanks


    Are you able to make it so that I can select ann header and all dsignations in that header are included? We may have more groups in the future, Clinical, maintenanence etc..


    J

  • Change the UDF to


    Required entries into the formula are now

    • Column header for required training
    • Column header for the "Previous" of required training
    • Column header for required designation
    • Search Date

    The Designation Table can be modified whenever needed and the code will work. The only thing now is that if the Designation Table is moved to another sheet then the code will need modifying where indicated to reflect the new sheet location for the Designation Table.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • I just edited the code in last post, make sure you use the edited code!


    Also make sure the cell(s) where you use the formula are formatted as Percentage

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi KjBox


    Thank you very much.
    Just have one more request.


    Is it possible to have the “previous” as an optional requirement? Just that we have new trainings without any previous dates... So can I put ,, and will it still work?


    J

  • When a Function (or Sub) parameter is optional it must go at the end of all non-optional parameters.


    Therefore I have had to change the order in which column headers are entered into the Formula. I also added 3 new parameters (for Start Date column, Designation column and Leaver column), this means that if any of those columns move within the table or have their header name changed then the code will still find the correct column. I also made the code allow for any value in the Leaver column, just in case "Yes" gets entered instead of "Leaver"


    I think that should make the UDF as possible.


    The new order of entry for the formula parameters are:

    • Start Date column header
    • Designation column header
    • Leaver column header
    • Designation Type column header (in the Designation Table)
    • Training column header
    • "Previous" column header (this parameter is optional)

    If there is no "Previous" column for the required training the just omit the last parameter.


    I am attaching an updated file, with a new training (Test Training) which has no "Previous" column. I also inserted the new training between Moving and Moving Previous to show that columns do not have to be adjacent, also replaced one "Leaver" with "Yes"


    I modified the code a bit to, hopefully, make it easier for you to see how it works.


  • Also, wanted to make this clear, as I don't think it was in my original post.


    A training date is considered valid (should only be counted) if it is within 1 year from the search date. For e.g If the training was completed on 2/4/17 and the search date was 1/4/18 - then its valid, if the training date is 1/5/18 and the search date is 1/4/18, then the previous date should be used and if there is no previous, this should NOT be counted.


    Sorry for causing any inconvenience


    Thank You Very Much.
    J