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]

How have your variables suddenly got date components?

 

[hsandeep]

How have your variables suddenly got date components?

@RoryA
It is not sudden. I have been using the values in the above 3 cell addresses using above format only (However, I don't know whether choosing this formatting is the cause of Code not filling the formula). Other actions are performed by the code. Only the last action (where the code needs to set the formula in the range at a certain time period) is not working. I am really stuck here.

 

[RoryA]

No, you haven't. Your original posted code is:

AA4Value = TimeSerial(14, 0, 0): AD4Value = TimeSerial(15, 22, 30): AG4Value = TimeSerial(15, 55, 0)

That will not include the current date in the variables (they will effectively be treated as times on 31/12/1899)

 

[hsandeep]

No, you haven't. Your original posted code is:

AA4Value = TimeSerial(14, 0, 0): AD4Value = TimeSerial(15, 22, 30): AG4Value = TimeSerial(15, 55, 0)

That will not include the current date in the variables (they will effectively be treated as times on 31/12/1899)

@RoryA
First of all, I will admit that I made an unintentional deviation from using the accurate words, despite knowing that I am talking to a MVP. Sir, it was an explanatory error from my side in post#10.
In the code’s body, it is certainly what you have said

AA4Value = TimeSerial(14, 0, 0): AD4Value = TimeSerial(15, 22, 30): AG4Value = TimeSerial(15, 55, 0)

But in the Worksheet tab named A23 (in which this code is pasted),
It is
AA4=07/17/2023 14:30:00 PM
AD4=07/17/2023 15:52:30 PM
AG4=07/17/2023 15:55:00 PM
The above 3 cell addresses contains value in date & time format.

Instead of the typo error (wrongly written as in post# 10)
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

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

WHAT MODIFICATION IN THE CODE IS SUGGESTED?

 

[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

 

[hsandeep]

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

@Alex Blakenburg
I want to let you know that I think you are a good and intelligent person, and people like you are needed on this earth. Keep being amazing!

if AA4 AD4 & AG4 have date and time and those times are what you are using why hard code the times in the code. I hard coded AA4 AD4 & AG4 to provide values directly in the code, rather than referencing them from cells or variables. The code should perform only during 1 time period, the starting time of this time period will start every day at 2:00:00 PM (every day here means: the day when I keep the Workbook opened at 2:00:00 PM). I also needed to increase the code’s speed. So I kept AA4Value = TimeSerial(14, 0, 0) since it will always have a fixed value that will not change.

AA4 says:14:30 the code is using 14:00 Here I think it is a blunder made by me. The code line

AA4Value = TimeSerial(14, 0, 0): AD4Value = TimeSerial(15, 22, 30): AG4Value = TimeSerial(15, 55, 0)

is CORRECT. However, I think the cell AA4 should be =07/17/2023 14:00:00 PM instead of =07/17/2023 14:30:00 PM. Am I correct here?

"ON THAT DATE" - does that mean that the system date has to be the same date as in row 4 ? Yes

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. Yes, your idea is truly impressive and shows your remarkable creativity and ingenuity.

PLEASE PROVIDE ME A SPEEDY, NON HANGING CODE.

 

[Alex Blakenburg]

Firstly did you replace the sections of your code with my suggestions in post #15 and if so did it then do what you wanted it to do ?

is CORRECT. However, I think the cell AA4 should be =07/17/2023 14:00:00 PM instead of =07/17/2023 14:30:00 PM. Am I correct here?

Yes to avoid confusion but at this point you have the time hard coded and I am only using the date portion in AA4

A SPEEDY, NON HANGING CODE.

You are using a worksheet_calculate event, so the code will be running almost all the time.
What cell is being manually changed on the sheet that can tell the code that it needs to run ?

 

[hsandeep]

Firstly did you replace the sections of your code with my suggestions in post #15 and if so did it then do what you wanted it to do ?

Yes to avoid confusion but at this point you have the time hard coded and I am only using the date portion in AA4


You are using a worksheet_calculate event, so the code will be running almost all the time.
What cell is being manually changed on the sheet that can tell the code that it needs to run ?

@Alex Blakenburg
Firstly did you replace the sections of your code with my suggestions in post #15 and if so did it then do what you wanted it to do? No, not yet. I will implement the changes to the code on Monday, as the data is only live during weekdays. Then only I would be able accurately assess the code’s performance.

You are using a Worksheet_Calculate event, so the code will be running almost all the time.
What cell is being manually changed on the sheet that can tell the code that it needs to run? The code is designed to run automatically without any manual intervention. The cell AB4 updates its values from 9:00:00 AM to 3:30:00 PM, and its dependent cells AB5:AB65 (each cell of this range containing a formula "=$AB$4") also update their values accordingly. This triggers the code to run.

Any further change/(s) suggested, Sir?

 

[hsandeep]

You are using a worksheet_calculate event, so the code will be running almost all the time.

The code line written in row 1

If Time < TimeSerial(14, 0, 0) Then Exit Sub

which should check if the current time is before 14:00 (2:00 PM) and if it is, the code must exit the subroutine; is stopping the code to run when current time<14:00 (2:00 PM) or not?