Recording Price Movements in Excel

[MartinP_uk]

I'm sure i have read/seen some code on here to record price movements. Could somebody please post a link as i cannot seem to find it. What i would like to do is record the price movements in F5.

[MartinP_uk]

Must of been on another forum

[2020vision]

Hi MartinP_uk

would any of these posts be of use to you?

http://www.gruss-software.co.uk/forum/v ... ght=record
http://www.gruss-software.co.uk/forum/v ... ght=record
http://www.gruss-software.co.uk/forum/v ... ght=record
http://www.gruss-software.co.uk/forum/v ... ght=record

Can not say for sure if any of the downloads are still available but you could
ask their makers to upload again if not?

Ttfn - Michael

[MartinP_uk]

You must need 2020vision to find those threads

Cheers m8

[MartinP_uk]

I'm still having trouble with getting this to work. If somebody can knock up a sheet that records the price movements of all the horses i'd be happy to pay by paypal. Pls pm me.

[MartinP_uk]

I have managed to scrape together this piece of code that copies the contents of Y5:Y50 into the next column. How would it be possible to then past Y5:Y50 into the next available column every time Betting Assistant refreshes? i.e every 2 secs.

Code: Select all

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NextCol As Long

If Target.Columns.Count <> 16 Then Exit Sub
NextCol = Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 1
Application.EnableEvents = False
Range("Y5:Y50").Copy
Cells(5, NextCol).PasteSpecial Paste:=xlPasteValues

End Sub


[MartinP_uk]

Also could somebody please explain what the following means please

Code: Select all

 If Target.Columns.Count <> 16 Then Exit Sub

[osknows]

The code you have is placed in a worksheet change event i.e. - Private Sub Worksheet_Change(ByVal Target As Range)

So when the worksheet changes the event fires and the changed cells are picked up as a range object in 'Target'

BA updates excel with 2 different refreshes, one for prices and one for balance updates etc

For price updates 16 columns are updated; therefore 'If Target.Columns.Count <> 16 Then Exit Sub' essentially checks if BA has refreshed prices rather than you just changing the odd cell. If BA has refreshed then Target.Columns.Count =16 and the code below runs otherwise it exits the VBA Subroutine

[osknows]

I have managed to scrape together this piece of code that copies the contents of Y5:Y50 into the next column. How would it be possible to then past Y5:Y50 into the next available column every time Betting Assistant refreshes? i.e every 2 secs.

Code: Select all

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NextCol As Long

If Target.Columns.Count <> 16 Then Exit Sub
NextCol = Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 1
Application.EnableEvents = False
Range("Y5:Y50").Copy
Cells(5, NextCol).PasteSpecial Paste:=xlPasteValues

End Sub


Your code works fine I think, the only problem is you have disabled events using 'Application.EnableEvents = False' and not enabled them again

try this

Code: Select all

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NextCol As Long
Application.EnableEvents = False

NextCol = Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 1

Range("Y5:Y50").Copy
Cells(5, NextCol).PasteSpecial Paste:=xlPasteValues
Application.EnableEvents = True
End Sub

[MartinP_uk]

thanks osknows that kinds makes sense

[MartinP_uk]

I have managed to scrape together this piece of code that copies the contents of Y5:Y50 into the next column. How would it be possible to then past Y5:Y50 into the next available column every time Betting Assistant refreshes? i.e every 2 secs.

Code: Select all

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NextCol As Long

If Target.Columns.Count <> 16 Then Exit Sub
NextCol = Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 1
Application.EnableEvents = False
Range("Y5:Y50").Copy
Cells(5, NextCol).PasteSpecial Paste:=xlPasteValues

End Sub


Your code works fine I think, the only problem is you have disabled events using 'Application.EnableEvents = False' and not enabled them again

try this

Code: Select all

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NextCol As Long
Application.EnableEvents = False

NextCol = Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 1

Range("Y5:Y50").Copy
Cells(5, NextCol).PasteSpecial Paste:=xlPasteValues
Application.EnableEvents = True
End Sub

osknows thank you very much your code works perfectly. thank you

[osknows]

Sorry made a mistake in the last code....

use this

Code: Select all

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NextCol As Long

If Target.Columns.Count <> 16 Then Exit Sub
NextCol = Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 1
Application.EnableEvents = False
Range("Y5:Y50").Copy
Cells(5, NextCol).PasteSpecial Paste:=xlPasteValues
Application.EnableEvents = True
End Sub