I have an automated trading sheet for football which works fairly well apart from suspensions. I would like the sheet to not fire bets for a couple of minutes after each suspension to allow the market to reform.
I need to use formula as my vb is rubbish, I can see I need to capture the current time and record this when the suspended cell is filled but then I can't figure out how to time 2 minutes and then restart betting.
Any suggestions?
Delaying bets after suspensions
You should be able to do this with only a little vba to make a marker when it is suspended & then capture the time when it is first unsuspended. I am a bit of a google-taught noobie when it comes to vba but it sounds like you could do it the same way as I time the clearing of status cells.
Allocate a cell that you will copy the time to and one for the marker (eg:Y1 & Y2 respectively on the betangel sheet) and then write something like this in the code for that sheet (I think it would have to be under the worksheet change event):
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("H1").Value = "Suspended" And Range("Y2").Value <> "Suspended" Then
Range("Y1").ClearContents
Range("Y2").Value = Range("H1").Value
End If
If Range("Y2").Value = "Suspended" And Range("H1").Value <> "Suspended" Then
Range("Y1").Value = Range("F4").Value
Range("Y2").ClearContents
End If
End Sub
That should lay a marker in Y2 when it is suspended & then capture the time when it is unsuspended again. It should clear the values to re-arm itself when it is suspended again.
That should be enough to give you something you can reference with a formula. I haven't tested it so PM me if there is a problem or you could send me the sheet & I'll fix it.
There are plenty of others on here who could write this a great deal better I think as I have only learned 5 or 6 things in vba but excel is so flexible that you can know a little & do a lot.
(Edited to correct code)
Allocate a cell that you will copy the time to and one for the marker (eg:Y1 & Y2 respectively on the betangel sheet) and then write something like this in the code for that sheet (I think it would have to be under the worksheet change event):
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("H1").Value = "Suspended" And Range("Y2").Value <> "Suspended" Then
Range("Y1").ClearContents
Range("Y2").Value = Range("H1").Value
End If
If Range("Y2").Value = "Suspended" And Range("H1").Value <> "Suspended" Then
Range("Y1").Value = Range("F4").Value
Range("Y2").ClearContents
End If
End Sub
That should lay a marker in Y2 when it is suspended & then capture the time when it is unsuspended again. It should clear the values to re-arm itself when it is suspended again.
That should be enough to give you something you can reference with a formula. I haven't tested it so PM me if there is a problem or you could send me the sheet & I'll fix it.
There are plenty of others on here who could write this a great deal better I think as I have only learned 5 or 6 things in vba but excel is so flexible that you can know a little & do a lot.
(Edited to correct code)
Last edited by leesnose on Mon Jul 21, 2014 9:45 pm, edited 1 time in total.
-
willkirkland
- Posts: 6
- Joined: Thu Jul 10, 2014 9:24 pm
Oh nice one,thanks so much for that. I will try it once I get home. Very much appreciated sir.