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 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.

 

[RoryA]

currentTime = Time

rather than

currentTime = Now

 

[bob33]

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

try that.

 

[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.

 

[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
As suggested, I had replaced the code line

currentTime=Now

Replaced with

currentTime=Time

Let me inform you that I had kept

AA4Value as 07/13/2023 14:30:00 PM
AD4Value as 07/13/2023 15:52:30 PM
AG4Value as 07/13/2023 15:55:00 PM

i.e. AA4Value; AD4Value & AG4Value are kept as date & time value.

I noticed at system’s time>15:30:00 PM that the code had already inserted formula "=$AB$4" in the ws.Range("AB5:AB65"). This is WRONG ACTION.

My requirement is: that THE CODE SHOULD INSERT FORMULA WHEN SYSTEM’S TIME>AD4VALUE I.E. WHEN SYSTEM’S TIME IS > 15:52:30. That’s all.

WHAT MODIFICATION IN THE CODE IS SUGGESTED?