Announcement

Collapse
No announcement yet.

£25 - VBA Module Help and Tidy Up

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • £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

    Attached Files
    Last edited by Janarthenan; November 20th, 2018, 21:22.

  • #2
    I can look at this for you
    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.

    Comment


    • #3
      Originally posted by KjBox View Post
      I can look at this for you
      Yes Please. Thanks!

      Comment


      • #4
        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?

        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.

        Comment


        • #5
          Hi KjBox

          Sorry I wasnt very clear.

          Please see attached, I have amended some dates to fir what I am trying to explain.

          Thanks
          J
          Attached Files

          Comment


          • #6
            Thanks, that makes it clearer.
            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.

            Comment


            • #7
              Sorry to bump the post, but is anyone able to assist?

              I have increased the pay to £25 if that helps.

              J

              Comment


              • #8
                Hello,

                You should not worry ...

                KjBox will very soon provide you with your solution ...
                If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                Comment


                • #9
                  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.

                  Comment


                  • #10
                    Payment received, Many thanks. File attached
                    Attached Files
                    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.

                    Comment


                    • #11
                      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

                      Comment


                      • #12
                        Hi,
                        1. Sorry, my misunderstanding, I thought the UDF would always refer to Unqualified. I will look into that and amend the UDF accordingly.
                        2. 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.

                        Comment


                        • #13
                          Originally posted by KjBox View Post
                          Hi,
                          1. Sorry, my misunderstanding, I thought the UDF would always refer to Unqualified. I will look into that and amend the UDF accordingly.
                          2. 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

                          Comment


                          • #14
                            Change the UDF to
                            Code:
                            Function TrainingPercentage(sTr As String, sPrTr As String, sDesig As String, dt As Date) As Double
                                Dim x, Desig, i As Long, i1 As Integer, i2 As Integer
                                Dim lcnt As Long, lTot As Long, oTbl As ListObject
                                
                                Set oTbl = ActiveSheet.ListObjects("tblDesignation") '//Change if the Designation table is on another sheet
                                If [SearchDate] = "" Or Not IsDate([SearchDate]) Then Exit Function
                                Application.Volatile
                                Desig = oTbl.DataBodyRange.Columns(Application.Match(sDesig, oTbl.HeaderRowRange, 0))
                                dt = CDate([SearchDate])
                                
                                With Sheet4.ListObjects("Tier1")
                                    x = .DataBodyRange
                                    i1 = Application.Match(sTr, .HeaderRowRange, 0)
                                    i2 = Application.Match(sPrTr, .HeaderRowRange, 0)
                                    For i = 1 To UBound(x, 1)
                                        If Len(x(i, i1)) And CDate(x(i, i1)) >= DateAdd("yyyy", -1, Date) Then
                                            If Not IsError(Application.Match(x(i, 3), Desig, 0)) _
                                            Or x(i, 9) <> "Leaver" _
                                            Or CDate(x(i, 2)) >= dt Then
                                                lTot = lTot + 1
                                                If Len(x(i, i2)) And CDate(x(i, i2)) >= DateAdd("yyyy", -1, Date) Then
                                                    lcnt = lcnt + 1
                                                End If
                                            End If
                                        End If
                                    Next
                                End With
                                
                                TrainingPercentage = lcnt / lTot
                                
                            End Function
                            Required entries into the formula are now
                            1. Column header for required training
                            2. Column header for the "Previous" of required training
                            3. Column header for required designation
                            4. 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.

                            Comment


                            • #15


                              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.

                              Comment

                              Working...
                              X