Hi I need some help with some code please. I am trying to record a list of races I have automated trades on. I want to record the RACE, NUMBER of RUNNERS, TOTAL MATCHED.
I have linked these three cells to sheet2 in cells A1 B1 C1.
I have also linked “In-play” (G9) to D1 and P&L (C9) to E1. These are to act as triggers to record the information. It will record when the market turns in play and only if I have an active trade.
GCst (AUS) 29th Jan - 11:25 R2 900m 2yo 11 48564 In-play 2.2
Race Runners Total Matched
KemG (AUS) 29th Jan - 11:15 R3 2400m Hcap 8 54524
GCst (AUS) 29th Jan - 11:25 R2 900m 2yo 12 48564
Okay so here is the dodgy code I have put together which obviously won’t work. I recorded the copy paste side of the macro and then tried to add a trigger. Any help please?
Sub Macro1()
'
' Macro1 Macro
'
'
If Range("D1").Value = "In-play" And Range("E1").Value <> "0" Then
Range("A1:C1").Select
Selection.Copy
Range("A1").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range("A6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub
Trying to trigger a copy in excel.
Hi Shamrock,
I'm no expert when it comes to excel, but I have been working on my own variation of a data capture spreadsheet this last few days and when I want to copy cells, I use the following syntax:
I personally don't like using the copy/paste functions as I have found that they use, and therefore screw up other applications use of, your clipboard.
Hope this helps.
SB
I'm no expert when it comes to excel, but I have been working on my own variation of a data capture spreadsheet this last few days and when I want to copy cells, I use the following syntax:
This would effectively copy the current content of Cell C3 in the Bet Angel sheet into cell A2 (row 2, column 1) of Sheet2.Sheets("Sheet2").Cells(2, 1) = Sheets("Bet Angel").Range("C3")
I personally don't like using the copy/paste functions as I have found that they use, and therefore screw up other applications use of, your clipboard.
Hope this helps.
SB
Hi Spikey,
Thanks for the reply.
I got this back from Mr Excel.
Private Sub Worksheet_Calculate()
If Range("D1").Value = 1 Then
Dim NextRow&
NextRow = _
Range("A:C").Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
Range(Cells(NextRow, 1), Cells(NextRow, 3)).Value = _
Range("A1:C1").Value
End If
End Sub
This works great.
I copy the counter to E1 and set F1 at -0:00:01 then in D1 put an if function =if(E1=F1,1,0). This copies the info 1 second prior to the advertised off time.
Good way to capture the data.
Cheers.
Thanks for the reply.
I got this back from Mr Excel.
Private Sub Worksheet_Calculate()
If Range("D1").Value = 1 Then
Dim NextRow&
NextRow = _
Range("A:C").Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
Range(Cells(NextRow, 1), Cells(NextRow, 3)).Value = _
Range("A1:C1").Value
End If
End Sub
This works great.
I copy the counter to E1 and set F1 at -0:00:01 then in D1 put an if function =if(E1=F1,1,0). This copies the info 1 second prior to the advertised off time.
Good way to capture the data.
Cheers.
Hi Shamrock
Another nice solution is to record the data to a log file in csv format which can then be opened directly into excel for easy analysis. This has the advantage of being able to record data for multiple races. You can use the following code:
Private Sub Worksheet_Calculate()
If Range("D1").Value = 1 Then Call LOG_TRIGGER
End Sub
This code goes in Module 1:
Sub LOG_TRIGGER()
Sheets("Bet Angel").Activate
LogInformation Range("B1").Text & ", " & Range("C4").Text& ", " &Range("C2").Text
End Sub
Sub LogInformation(LogMessage As String)
Const LogFileName As String = "C:\Bet_Angel_Data\Excel_log.csv"
Dim FileNum As Integer
FileNum = FreeFile ' next file number
Open LogFileName For Append As #FileNum ' creates the file if it doesn't exist
Print #FileNum, LogMessage ' write information at the end of the text file
Close #FileNum ' close the file
End Sub
You would need to create the folder "C:\Bet_Angel_Data" or similar
Another nice solution is to record the data to a log file in csv format which can then be opened directly into excel for easy analysis. This has the advantage of being able to record data for multiple races. You can use the following code:
Private Sub Worksheet_Calculate()
If Range("D1").Value = 1 Then Call LOG_TRIGGER
End Sub
This code goes in Module 1:
Sub LOG_TRIGGER()
Sheets("Bet Angel").Activate
LogInformation Range("B1").Text & ", " & Range("C4").Text& ", " &Range("C2").Text
End Sub
Sub LogInformation(LogMessage As String)
Const LogFileName As String = "C:\Bet_Angel_Data\Excel_log.csv"
Dim FileNum As Integer
FileNum = FreeFile ' next file number
Open LogFileName For Append As #FileNum ' creates the file if it doesn't exist
Print #FileNum, LogMessage ' write information at the end of the text file
Close #FileNum ' close the file
End Sub
You would need to create the folder "C:\Bet_Angel_Data" or similar