Add timestamp to cell with VBA

[vrupper]

Hi all,

I'm trying to automate my back bets for the horses if the price stays below a certain percentage of BSP for n seconds.

I have setup my spreadsheet using the "Reload quick pick list at midnight" sheet as my starting point. Columns AB:AW have criteria in them that the Trigger column Q references. This works fine and has been losing me money just fine!!

Due to downward spikes the back bet threshold is being reached for a split second and firing the BACK command. I've tried putting in criteria if it's close to the lay price but this still hasn't worked.

So now I want to place the BACK if the threshold is reached and held for a certain time period, say 3 seconds for example. I have changed my spreadsheet to have range AA5:AA44 as the range that has the IF commands in to say whether to BACK that row.

I would like column Z to have a timestamp (just time) to be entered by VBA if the corresponding AA cell has BACK in it. My Trigger (Q column) cell will then have a formula comparing the Z cell and $C$2 for the time now.

This is my VBA at the moment, but it is not entering the time in the Z column. I've tried loops, Ifs, Intersects and none seem to work! I have no experience of VBA so I've been trying to customise code I see on the internet for adding timestamps, but i think my problem is that these are static spreadsheets that aren't refreshing.

As a second query, I then want column Z to be cleared when the market changes to the next race.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False



Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 26
xTimeColumn = 25
xRow = Target.row
xCol = Target.Column
If Target.Text = "BACK" Then
    If xCol = xCellColumn Then
       Cells(xRow, xTimeColumn) = Time()
    Else
        On Error Resume Next
        Set xDPRg = Target.Dependents
        For Each xRg In xDPRg
            If xRg.Column = xCellColumn Then
                Cells(xRg.row, xTimeColumn) = Time()
            End If
        Next
    End If
End If



      If triggerQuickPickListReload Then
            triggerQuickPickListReload = False
           Range("Q2").Value = -3
         triggerFirstMarketSelect = True
    Else
        If triggerFirstMarketSelect Then
           triggerFirstMarketSelect = False
              Range("Q2").Value = -5

         End If
    End If

      Application.EnableEvents = True





End Sub


[Captain Sensible]

Not sure exactley what you're after but the following code will enter a timestamp once BACK is entered in column AA, it'll be a static time that won't change and also won't go if BACK is no longer in the column AA. Once the market changes in cell A1 column Z will be cleared.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count <> 16 Then Exit Sub
Static MyMarket As String
Dim lastRowTimes As Long, lastRowRunners As Long, i As Long
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With Target.Parent


If .Range("A1").Value <> MyMarket Then

    MyMarket = .Range("A1").Value
    lastRowTimes = .Range("Z" & Rows.Count).End(xlUp).Row
    If lastRowTimes > 4 Then .Range("Z5:Z" & lastRowTimes).Value = ""

End If

lastRowRunners = .Range("A" & Rows.Count).End(xlUp).Row

For i = 5 To lastRowRunners

    If .Range("AA" & i).Value = "BACK" And .Range("Z" & i).Value = "" Then
        .Range("Z" & i).Value = Time()
    End If
Next i







      If triggerQuickPickListReload Then
            triggerQuickPickListReload = False
           .Range("Q2").Value = -3
         triggerFirstMarketSelect = True
    Else
        If triggerFirstMarketSelect Then
           triggerFirstMarketSelect = False
              .Range("Q2").Value = -5

         End If
    End If


End With


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True





End Sub

[vrupper]

Thanks for the reply and code, it's exactly what I need, I want the time input in column Z to be static.

I've tried the code in my spreadsheet but it didn't place the time in column Z, same problem I have been having!!?!?

Column AA formula is:

=IF(AND($E$2="In play",COUNT($T5<1),AN5=TRUE,F5<=(Y5*$AC$2),G5>=$AC$3,($F5*1.2)>=$H5)=FALSE,"","BACK")

and places the word "BACK" in the back column fine when the parameters are met, but no time in column Z. I've also tried placing a simple IF formula in column AB and the time appears, but obviously changes, which I don't want.

I may try afresh with a new downloaded template and then paste your code in to see if it works then.

[vrupper]

I've started afresh and paste the code into the new sheet and it works before I put all my parameters in. I.e. I type "BACK" into cell AA5 for instance and the Z5 shows the static time, perfect.

Columns AE to AL have formulas in that result in TRUE/FALSE, these then feed back to AA5 resulting in "BACK" or "". It seems the

Code: Select all

Application.Calculation = xlCalculationManual

line is stopping all my other formulas working and results in the debugger stopping on this line as column AA has the #Value! error.

Code: Select all

  If .Range("AA" & i).Value = "BACK" And .Range("Z" & i).Value = "" Then

But now even when not connected to Betting Assistant, the Z column has reverted to not working again!?!?

I'm going to start afresh again and go through and see at what point the column Z stops working.

[Captain Sensible]

You can remove the calculation lines as they simply speed things up on the sheet a little and it should still pick up the BACK even if it's on the next refresh.

Sometimes if VBA hits an error the code never moves onto the line Application.EnableEvents = True which means the VBA won't run until the error in the code is sorted or your turn back on events.

[vrupper]

I got it working I think, testing now.

The workbook was opening and then causing an error which then meant the timestamp wasn't working, even in the worksheet after I disconnected it from BA.

The #VALUE! in my AA column was linked to my formulas elsewhere in the worksheet on opening.

I added IFERROR at the start of all my formulas and I think this has fixed it.

Thanks for your help Captain!

[Captain Sensible]

No problems, it can be a bit of a pain mixing and matching formulas and VBA but there is lots of error trapping you can use to get round the problems. Generally you try to anticipate them within your code but sometimes that's not always possible so you need to deal with them as they crop up especially as the API can send out rogue data.

It'd be worth you adding in some reset code within amodule just to reset the events once the errors occur though as when you're tweaking code they'll be so many times errors will stop the VBA running to completion. Something like this should be sufficient to start with

Code: Select all

Sub Reset()
  Application.EnableEvents = True
  'etc
End Sub