Announcement

Collapse
No announcement yet.

25 - VBA Module Help and Tidy Up

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

  • #31
    Hi KjBox


    Percentages are fine.

    Once I copied the code into the main spreadsheet (which contains other etc and more training) it extremely slows down and calculating after every change.
    Also I found two of the columns are giving me a #VALUE error.

    J
    Attached Files

    Comment


    • #32
      A UDF is always going to be slower than an Excel built-in Function, so if many Training columns are involved then calculation time will increase considerably.

      To speed things up you need to keep the number of cells using the UDF to a minimum.

      I suggest you have only the dates for the current month in the table using the UDF, that will mean just 4 rows with the UDF. Dates prior to the current month can either be deleted or, if percentages still needed, select and copy all cells for prior month(s) and paste back to the same location (or to another sheet if required) as Paste Special Values. That will remove the formula retaining only the result of the formula that was there. Then for the following month extend the table down to include the weeks for the new month, the UDF formulas will fill down automatically

      Without seeing the data for the Training Columns that produce #VALUE very difficult to say what is causing it. The one thing that you can check is that all values in the concerned columns are correctly entered as dates and the Cells are formatted as Date.
      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


      • #33
        Ok Thanks

        I attached my file in the previous post.
        I have checked and the values are all dates, what could be the problem?

        J

        Comment


        • #34
          I missed seeing the attachment! However that too is showing the "Catastrophic Failure"

          Can you attach it without the UDF and clear the formula from the cells that have the UDF
          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


          • #35
            Hi

            See attached

            J
            Attached Files

            Comment


            • #36


              OK, I tracked down the reason for the #VALUE, I knew it had to be something to do with the Training and/or Previous dates, it turned out that you had a few cells that were not truly blank because they contained a space.

              I cleaned up all date columns by using ReplaceAll, replacing " " with "".

              The UDF now works on all columns.

              I deleted the Stats table rows for December onward but did not copy and paste back as value months April to October because you may need to change the Designation parameter in the formulas first. I used [All} for all formulas except for "Mental Health Act - Qualified" and "Mental Health Act - Unqualified" for which I used [Qualified] and [Unqualified]

              Once you change any formulas that need changing then copy/paste values you should find that calculation time is not slow.
              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

              Working...
              X