Add timestamp to cell with VBA

Please post any questions regarding the program here.

Moderator: 2020vision

Add timestamp to cell with VBA

Postby vrupper » Mon Sep 27, 2021 9:35 am

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!! :lol:

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
vrupper
 
Posts: 6
Joined: Mon Sep 27, 2021 9:16 am

Re: Add timestamp to cell with VBA

Postby Captain Sensible » Tue Sep 28, 2021 12:47 pm

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
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Add timestamp to cell with VBA

Postby vrupper » Tue Sep 28, 2021 1:27 pm

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
 
Posts: 6
Joined: Mon Sep 27, 2021 9:16 am

Re: Add timestamp to cell with VBA

Postby vrupper » Tue Sep 28, 2021 8:05 pm

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.
vrupper
 
Posts: 6
Joined: Mon Sep 27, 2021 9:16 am

Re: Add timestamp to cell with VBA

Postby Captain Sensible » Tue Sep 28, 2021 11:02 pm

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.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Add timestamp to cell with VBA

Postby vrupper » Wed Sep 29, 2021 1:39 pm

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!
vrupper
 
Posts: 6
Joined: Mon Sep 27, 2021 9:16 am

Re: Add timestamp to cell with VBA

Postby Captain Sensible » Wed Sep 29, 2021 6:49 pm

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
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm


Return to Help

Who is online

Users browsing this forum: No registered users and 36 guests

Sports betting software from Gruss Software


The strength of Gruss Software is that it’s been designed by one of you, a frustrated sports punter, and then developed by listening to dozens of like-minded enthusiasts.

Gruss is owned and run by brothers Gary and Mark Russell. Gary discovered Betfair in 2004 and soon realised that using bespoke software to place bets was much more efficient than merely placing them through the website.

Gary built his own software and then enhanced its features after trialling it through other Betfair users and reacting to their improvement ideas, something that still happens today.

He started making a small monthly charge so he could work on it full-time and then recruited Mark to help develop the products and Gruss Software was born.

We think it’s the best of its kind and so do a lot of our customers. But you can never stand still in this game and we’ll continue to improve the software if any more great ideas emerge.