2

I made a fairly easy function to count the number of employees scheduled during a certain hour on a certain day. The formula works, but it doesn't always calculate it. If I go back into the schedule, and re-enter the value in the cell, it then does re-calculate it. Is this a glitch or bad code on my part.

the function:

 Function CountStaff(StartR As Range, StartTime As Range)
 J = 0
 Dim C As Object
     For Each C In StartR
         CurrentLine = CurrentLine + 1
         If StartTime >= C And StartTime < C.Offset(0, 1) Then
             If C.Interior.ColorIndex <> 43 Then
                 J = J + 1
             End If
         End If
     Next
 CountStaff = J
 End Function

The Formula: =CountStaff(B$3:B$21,$R3)

The Range is the start times for all the employees on the schedule, $R3 holds the time being tested (say 9:00 am). The color check is if a cell is highlighted because of vacation time or another such event.

Community
  • 1
  • 1
Steve
  • 63
  • 6
  • HI. What do you mean by "it doesn't always calculate it"? Do you use a macro to run the function? Or do you only use it after updating the schedule manually? – David912 Feb 28 '16 at 14:42
  • Do you have Calculation Options set to Automatic in the Formulas section of the ribbon? – Porcupine911 Feb 28 '16 at 15:17
  • 5
    Add `Application.Volatile` as the first line in your function. – Scott Craner Feb 28 '16 at 15:56
  • 1
    Have you already had a look at this Q&A: http://stackoverflow.com/questions/11045/refresh-excel-vba-function-results ? This might be also of interest to you: http://stackoverflow.com/questions/25145698/alternative-to-application-volatile-to-auto-update-udf This is also good: https://fastexcel.wordpress.com/2012/02/02/writing-efficient-vba-udfs-part-10-volatile-functions-and-function-arguments/ – Ralph Feb 28 '16 at 15:57
  • Thank you! That did the trick! – Steve Feb 28 '16 at 17:13

0 Answers0