Calculate Formula Only Once

[andybason]

Hello

I have the following formula in a sheet. It works fine but I would like it to run only once, the first time the criteria are met. Is this possible?

=COUNTIF($F$5:$F$25,"<2")>=2

Thanks

 

[maabadi]

If I understand correct, you want to find second value smaller than 2 at your range. because this formula only give you result true or false only?
If I say correct? what exact you want to do? find second match row or second match value or .....

 

[andybason]

Hello maabadi, the range F5:F25 is dynamic and the values change every few seconds. So the formula returns TRUE when the criteria is first met but then returns FALSE if the values change so that the criteria is no longer met.

Once the criteria is first met I want TRUE to be returned and to stay even if the values later change and the criteria is not met.

Thanks

 

[maabadi]

With this situation, I think you should use worksheet change event VBA. Are you familiar with VBA?
Where is your formula? (Formula cell address)

 

[andybason]

Hello maabadi, thanks. I have a basic knowledge of VBA. The reference is AA5 in a sheet called Market.

 

[maabadi]

One other thing.
Are you have formula at range F5:F25? If yes what is source column & how it is changed each few seconds?
I need it to know how to write worksheet event macro.

 

[maabadi]

This method works but you have one Problem after formula get results true, the sheet calculation doesnot automatic and it change to manual. But if you have formula at F5:F25 we can use another method:

Private Sub Worksheet_Calculate()
Dim Target As Range
Set Target = Range("AA5")
 If Not Intersect(Target, Range("AA5")) Is Nothing Then
 If Target.Value = True Then Application.Calculation = xlManual
 End If
End Sub

 

[andybason]

Hello maabadi, thanks for this. F5:25 do not contain formulas they are populated by third party software. However there are other formulas that will needs to calculate after the AA5 formula. Thanks again

 

[maabadi]

You're Welcome. without changing Calculate option to manual. I delete formula from Cell AA5 and added it to VBA formula. After result get true then don't change result.
Right click on sheet name at excel window and add this code:
Try this:

Private Sub Worksheet_Calculate()
Dim Target As Range, Rs As Long
Set Target = Range("AA5")
If Target.Value = "True" Then Exit Sub
  Rs = Application.WorksheetFunction.CountIf(Range("F5:F25"), "<2")
  If Rs >= 2 Then
   Target.Value = "True"
  Else
   Target.Value = "False"
End If
End Sub

 

[andybason]

Thank you so much maabadi. This works perfectly and will save me lots of time. I've also learnt a lot about VBA.