Code is not filing the formula in a range

[hsandeep]

I am using below code which is self-explanatory.

Private Sub Worksheet_Calculate()

    ' Exit the subroutine if the current time is before 2:00:00 PM
    If Time < TimeSerial(14, 0, 0) Then Exit Sub
   
    ' Temporary disable Events and ScreenUpdating not to get caught in a loop
    Application.EnableEvents = False
    Application.ScreenUpdating = False
   
    ' Declare a Worksheet variable and set it to the sheet named "A23"
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("A23")
   
    ' Declare a variable to hold the starting cell for the loop and set it to the first cell in the range
    Dim startCell As Range
    Set startCell = ws.Range("AB5")
   
    ' Declare variables to hold the values of the cells used in the conditions
    Dim AA4Value As Date, AG4Value As Date, AD4Value As Date
   
    ' Assign hard-coded values to the variables
    AA4Value = TimeSerial(14, 0, 0): AD4Value = TimeSerial(15, 22, 30): AG4Value = TimeSerial(15, 55, 0)
   
    ' Declare a variable to hold the current time and set it to NOW()
    Dim currentTime As Date
    currentTime = Now

    ' Declare an array to hold the hard-coded values for column AA
    Dim AAVals(1 To 61) As Variant
   
    ' Declare a variable to hold the start time and set it to 2:00:00 PM
    Dim startTime As Date
    startTime = TimeSerial(14, 0, 0)
   
    ' Loop through each element of the array
    Dim k As Long
    For k = 1 To 61
        ' Set the element to the hard-coded value
        AAVals(k) = startTime + TimeSerial(0, 0, (k - 1) * 30)
    Next k

    ' Loop through each cell in the range and set its value if it has a formula and meets the condition
    Dim cell As Range
    For Each cell In ws.Range("AB5", "AB65")
        If cell.HasFormula And ws.Range("AA" & cell.Row).Value <= currentTime Then
            cell.Value = cell.Value
        End If
    Next cell

     If AA4Value < currentTime And AD4Value <= currentTime And currentTime < AG4Value Then
        Application.EnableEvents = False
            ws.Range("AB5:AB65").Formula = "=$AB$4"  
        Application.EnableEvents = True       
     End If
     Application.EnableEvents = True
     Application.ScreenUpdating = True
End Sub

The code lines are not working

If AA4Value < currentTime And AD4Value <= currentTime And currentTime < AG4Value Then
ws.Range("AB5:AB65").Formula = "=$AB$4"

The If statement checks if the current time is between the values of AA4Value and AG4Value, and greater than or equal to the value of AD4Value. If this condition is met, the code ‘should’ set the formula for the range of cells from “AB5” to “AB65” to “=$AB$4”. But the code is not performing this action & leaves the range of cells from “AB5” to “AB65” as .Value

Request someone to help & rectify the code.

Thanks in advance

 

[RoryA]

currentTime = Time

rather than

currentTime = Now

 

[bob33]

I believe the line
currentTime = Now
should be
currentTime = Time

try that.

 

[Alex Blakenburg]

Sadly your request is still quite unclear.
1) if AA4 AD4 & AG4 have date and time and those times are what you are using why hard code the times in the code.
2) AA4 says:14:30 the code is using 14:00
3) "ON THAT DATE" - does that mean that the system date has to be the same date as in row 4 ?
ie do we need to check the date is the same before doing a time check
4) If yes to 3 is the date in AA AD & AG always going to be the same so we only need to check the systems date against one of the 3.

Since I don't have the answers to the above this might be overkill but try these changes:

    ' Declare a variable to hold the current time and set it to NOW()
    Dim currentDateTime As Date
    Dim currentDate As Date
    Dim currentTime As Date
    currentDateTime = Now
    currentDate = Int(currentDateTime)
    currentTime = currentDateTime - currentDate             ' if you don't need the date could just use = Time

     ' if the date is the same in AA4 & AD4 & AG4, only need to check one.
    If currentDate = Int(ws.Range("AA4")) Then
        If AA4Value < currentTime And AD4Value <= currentTime And currentTime < AG4Value Then
           Application.EnableEvents = False
               ws.Range("AB5:AB65").Formula = "=$AB$4"
           Application.EnableEvents = True
        End If
     End If

 

[RoryA]

currentTime includes today's date, since you used Now() rather than Time(). Your other values are just times, so they will always be smaller than the currentTime value.

 

[hsandeep]

currentTime includes today's date, since you used Now() rather than Time(). Your other values are just times, so they will always be smaller than the currentTime value.

@RoryA Many thanks for your time & efforts. What should be the 'modified' code lines? It would help me immensely.

 

[bob33]

Rory beat me by a few seconds

 

[hsandeep]

currentTime = Time

rather than

currentTime = Now

@RoryA
I replaced the below code line (from the vba)

If AA4Value < currentTime And AD4Value <= currentTime And currentTime < AG4Value Then
ws.Range("AB5:AB65").Formula = "=$AB$4"

Replaced with

If AA4Value < Time() And AD4Value <= Time() And Time() < AG4Value Then
ws.Range("AB5:AB65").Formula = "=$AB$4"

As soon as AA4Value became<system’s time (at this time, AD4Value was >system’s time AND system’s time was < AG4Value); the WORKBOOK GOT HUNG ( cursor turned into a blue spinning ball) and moreover the formula "=$AB$4" got inserted into ws.Range("AB5:AB65")!!! This is ANOTHER WRONG ACTION. Code should set the formula in the range when AA4Value < Time() And AD4Value <= Time() And Time() < AG4Value only. WHAT MODIFICATION IN THE CODE IS REQUIRED? Why the code is setting the formula early?

As soon as the system’s time became >AG4Value, (at this time, AA4Value was <system’s time AND AD4Value was <system’s time); the hung problem was over.

Please note:
AA4Value < AD4Value < AG4Value ALWAYS.

 

[RoryA]

First, why didn't you just change the one line I suggested?
Second, where did the code to disable events go? Changing formulas in a Calculate event without disabling events will cause recursion.

 

[hsandeep]

First, why didn't you just change the one line I suggested?
Second, where did the code to disable events go? Changing formulas in a Calculate event without disabling events will cause recursion.

@RoryA I'll do that & report back the outcome tomorrow when the data goes live.