Repeating an equations operations on result

[FranklyPineapple]

Hi,

I have a series of IF functions, and if they all 4 of them return negatively I want it to run again. The first IF is based of a RANDBETWEEN function so it would give a different result, not resulting in a loop.

I have tried code such as

Sub Worksheet_Activate()
    If (Worksheets("Sheet1").Cells(2, 23)) = 1 Then
         ActiveSheet.EnableCalculation = False
         ActiveSheet.EnableCalculation = True
       Else
    End If
End Sub

and

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If (Worksheets("Sheet1").Cells(2, 23)) = 1 Then
         ActiveSheet.EnableCalculation = False
         ActiveSheet.EnableCalculation = True
       Else
    End If
End Sub

The first of which works, but only if I switch sheets then come back to the original sheet, it does not run automatically as I would like it to.


Any help would be welcome, thanks.

 

[shg]

Welcome to the board.

The first of which works, but only if I switch sheets then come back to the original sheet, it does not run automatically as I would like it to.

It runs when the procedure name suggests: when the worksheet is activated. When would you otherwise want it to run?

The Change event will run in response to manual changes or cell changes via code, not changes as a result of calculation.

If the worksheet recalculates as a result of volatile formulas, then perhaps you should check after enabling calculation to see if it needs to be calculated again.

 

[FranklyPineapple]

If the worksheet recalculates as a result of volatile formulas, then perhaps you should check after enabling calculation to see if it needs to be calculated again.

Could you please explain what you mean by this?

 

[shg]

Could you please explain what you mean by this?

What is the point of the worksheet activate code? Why does it test W23? Does enabling calculation cause W23 to recalculate? If so, is it certain that it will W2's value will be acceptable? If not, what could you do?