Action #2 needs to be modified in code

[hsandeep]

Below code (self-explanatory) has to perform 3 actions. Action #3 is being performed by the code. But Action #2 is not being performed by the code even if it meets the 2 conditions specified in the If block.
Request someone to look the code & modify it suitably so that Action #2 is performed by the code when it satisfies the 2 conditions in the If block of Action #2. The code must fill ws.Range("AB5:AB65") with the Formula "=$AB$4" BUT IT IS NOT FILLING WITH FORMULA. IT LEAVES THE ws.Range("AB5:AB65") as .Value=.Value i.e. THE RANGE HAS ONLY VALUES IN EACH CELL

Thanks in advance.

Private Sub Worksheet_Calculate()
    ' 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 current time and set it to the current time
    Dim CurrentTime As Date
    CurrentTime = Now

    ' Action #1:
    ' Check if either condition is met and exit the subroutine early if so
    If ws.Range("AA4").Value > CurrentTime Or ws.Range("AG4").Value < CurrentTime Then
        Exit Sub
    End If

    ' Action #2:
    ' Check if the second condition is met and set the formula for the range if so
    If ws.Range("AD4").Value <= CurrentTime And Not ws.Range("AB5").HasFormula Then
        ws.Range("AB5:AB65").Formula = "=$AB$4"
        Exit Sub
    End If

    'Action #3:
    ' 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.Cells(cell.Row, "AA").Value <= CurrentTime Then
            cell.Value = cell.Value
        End If
    Next cell

    ' Declare a variable to track if all formulas have been removed and set it to True initially
    Dim allFormulasRemoved As Boolean
    allFormulasRemoved = True
   
    ' 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")

    ' Loop until all formulas have been removed
    Do
        ' Set allFormulasRemoved to True at the start of each iteration of the loop
        allFormulasRemoved = True

        ' Loop through each cell in the range starting from startCell
        For Each cell In ws.Range(startCell, "AB65")
            ' Check if the cell has a formula and meets the condition, and set its value if so
            If cell.HasFormula And ws.Cells(cell.Row, "AA").Value <= CurrentTime Then
                cell.Value = cell.Value
               
                ' Set allFormulasRemoved to False since a formula was found and removed
                allFormulasRemoved = False
               
            ' Check if the cell does not have a formula and update startCell if so
            ElseIf Not cell.HasFormula Then
                Set startCell = cell.Offset(1, 0)
            End If
        Next cell
       
    ' Continue looping until all formulas have been removed (allFormulasRemoved is True)
    Loop Until allFormulasRemoved

    ' Turn screen updating back on after completing the loop
    Application.ScreenUpdating = True
   
End Sub

 

[Joe4]

But Action #2 is not being performed by the code even if it meets the 2 conditions specified in the If block.

Are you sure?
How have you verfied this?

Have you tried placing a break point at the beginning of your code, and then firing the code to run, and using the F8 key to step through the code one line at a time to see what path it takes in the code?

 

[Joe4]

Actually, I have an idea of what may be going on.

You need to be REALLY careful with working with automated procedures like Worksheet_Change, Worksheet_SelectionChange. and Worksheet_Calculate, because if in your code you are updating values or moving around, your code can essentially call itself again (and you can get caught in a loop - sometimes an infinite one if you are not careful!).

As such, it is usually a good idea to disable events while the code is running, so the changes the code is making doesn't cause it to call itself again. You just need to be careful to be sure to turn them back on again at the end, or else the automated code won't fire anymore.

Instead of the points of your code which tell it to "Exit Sub", let's put in a GoTo that sends it to the bottom of the code, and tells it to turn events back on before exiting.
So the updated parts of your code are in red type:

Private Sub Worksheet_Calculate()
    ' Temporary disable events to not get caught in a loop
    Application.EnableEvents = 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 current time and set it to the current time
    Dim CurrentTime As Date
    CurrentTime = Now

    ' Action #1:
    ' Check if either condition is met and exit the subroutine early if so
    If ws.Range("AA4").Value > CurrentTime Or ws.Range("AG4").Value < CurrentTime Then
        GoTo Exit_Sub
    End If

    ' Action #2:
    ' Check if the second condition is met and set the formula for the range if so
    If ws.Range("AD4").Value <= CurrentTime And Not ws.Range("AB5").HasFormula Then
        ws.Range("AB5:AB65").Formula = "=$AB$4"
        GoTo Exit_Sub
    End If

    'Action #3:
    ' 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.Cells(cell.Row, "AA").Value <= CurrentTime Then
            cell.Value = cell.Value
        End If
    Next cell

    ' Declare a variable to track if all formulas have been removed and set it to True initially
    Dim allFormulasRemoved As Boolean
    allFormulasRemoved = True
 
    ' 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")

    ' Loop until all formulas have been removed
    Do
        ' Set allFormulasRemoved to True at the start of each iteration of the loop
        allFormulasRemoved = True

        ' Loop through each cell in the range starting from startCell
        For Each cell In ws.Range(startCell, "AB65")
            ' Check if the cell has a formula and meets the condition, and set its value if so
            If cell.HasFormula And ws.Cells(cell.Row, "AA").Value <= CurrentTime Then
                cell.Value = cell.Value
             
                ' Set allFormulasRemoved to False since a formula was found and removed
                allFormulasRemoved = False
             
            ' Check if the cell does not have a formula and update startCell if so
            ElseIf Not cell.HasFormula Then
                Set startCell = cell.Offset(1, 0)
            End If
        Next cell
     
    ' Continue looping until all formulas have been removed (allFormulasRemoved is True)
    Loop Until allFormulasRemoved

    ' Turn screen updating back on after completing the loop
    Application.ScreenUpdating = True
 
 
Exit_Sub:
    ' Turn events back on
    Application.EnableEvents = True
  
End Sub

Does that work any better?
If that does not fix it, use the Step through code method I described in my previous post to watch what is happening when the code runs.

 

[hsandeep]

You need to be REALLY careful with working with automated procedures like Worksheet_Change, Worksheet_SelectionChange. and Worksheet_Calculate, because if in your code you are updating values or moving around, your code can essentially call itself again (and you can get caught in a loop - sometimes an infinite one if you are not careful!).

As such, it is usually a good idea to disable events while the code is running, so the changes the code is making doesn't cause it to call itself again. You just need to be careful to be sure to turn them back on again at the end, or else the automated code won't fire anymore.

Instead of the points of your code which tell it to "Exit Sub", let's put in a GoTo that sends it to the bottom of the code, and tells it to turn events back on before exiting.
So the updated parts of your code are in red type:

@Joe4 Feel comfortable when persons like you are on the other side for help.

Yes, you are correct to be very careful while handling automated procedures like Worksheet_Change, Worksheet_SelectionChange. and Worksheet_Calculate, because I am moving around my Workbook & the code can essentially call itself again to make me caught in a loop - sometimes an infinite one!.

Your remedial action did solved the problem, Thanks Joe4.

 

[Joe4]

You are welcome.
Glad I was able to help!