I have a sheet that calculates 5 most likely correct scores for a football market, based on certain odds conditions.
I am using VBA to copy 'live' odds from one part of the sheet (linked to Betangel excel) to another - that then performs the calc. This is triggered from the Betangel excel countdown clock - so the clock is linked on my sheets & when it hits a certain time to kick off - a formula triggers - changing a cell value - which the VBA code is looking for .
I have other code that does something similar to lay at HT and at 75.
BUT using the calculate event is problematic as I have a lot of formulas working on the sheet - which when they resolve triggers the event.
I cannot use the change event as that doesn't work with formulas - only if I manually change a cell - which I am not doing as the object of the exercise is to automate.
I am using application.enableevents = false in my subs so my own changes don't trigger it.
Anyone know a way of avoiding this? I have seen lots of posts about using VBA but nothing around this particular problem.
Been driving me mad for weeks now - so any help appreciated.
Code: Select all
Private Sub Worksheet_Calculate()
Dim row_number As Integer
Dim end_row As Integer
end_row = 30
For row_number = 11 To end_row
If Range("FT" & row_number).Value = 1 And IsEmpty(Range("B" & row_number)) Then
Copy_Starting_Odds (row_number)
End If
If Range("FX" & row_number).Value = 1 And IsEmpty(Range("RD" & row_number)) Then
InitialBet (row_number)
End If
If Range("GN" & row_number).Value = 1 And Range("GO" & row_number) = 0 Then
Call Place_Lay(row_number, "GO")
End If
If Range("HF" & row_number).Value = 1 And Range("HG" & row_number) = 0 Then
Call Place_Lay(row_number, "HG")
End If
Next row_number