Excel formulas stop working - stumped !

[mtrend]

Hi,

Im using excel to trigger my bets off & all has been fine using a simple forumla to trigger my bets automatically throughout the day using Guardian.

However now I have updated the spreadsheet which now contains a more complicated formula for triggering the bets, 10 or so conditions are in the formula now.

Again this works fine but only for 2 or 3 races, its seems randomly after usually 2 or 3 races the formulas in the sheet just stop firing. I go through the whole sheet manually trying to trigger the formula on each row and its dead, just like its just stopped processing the whole column of formulas.

If i close the workbook & reopen it again, they work again for 2 or 3 races & then randomly just stop all together again.

Im using VB code which has been posted on here that auto clears the status column which works fine, the status column is clear but still the formulas stop working.

Really not sure how to troubleshoot this anymore, hoping someone has come across something similar to this before ??

Any ideas ?


TIA

[nigelk]

If you are using multiple 'If' statements in a formula, the maximum number allowed is 7.

If you are using code, maybe you have an 'Application.EnableEvents=False' that you haven't reset?

Nigel.

[mtrend]

Thanks for the reponse Nigel.

Im only using one IF, the formula is:

=IF(AND(G9<=C71,G9>=C70,AND(G11>=C72,G13>=C72,G15>=C72,G17>=C72 and so on.......


The only code in the sheet is:

Private Sub Worksheet_Calculate()
Dim i As Integer
If Range("H1").Value = "Suspended" Then
For i = 9 To 67 Step 2
If Range("O" & i).Value <> "" Then _
Range("O" & i).ClearContents
Next i
End If
End Sub


So none of those ideas by the looks of it.....It really is bizzare !

[nigelk]

What cell is the formula in?
Next time it happens, check to see if the calculation is on automatic or manual.

[mtrend]

Its in all the 'Bet Rule' cells.

Il check next time it happens.

Thanks.

[dogform]

mtrend,

As an alternative to using long formulae I sometimes break the formula down into smaller units by using a table of zero's and 1's then sum the values.

For example, if the condition is met 1, if not met 0 then have a column for each condition with the rows corresponding to the "Status" rows

If the sum of the zeros and 1's equals or is greater than a certain value then the trigger fires.

[mtrend]

Hi Dogform,

I did as you suggested & broke down my formulas into seperate ones using 1's & 0's & now all runs smoothly without having to close & open the sheet to get the formulas to fire again.

Thanks for the info.