Simple VBA code required to record odds periodically

[yiangos_shiarlis]

Hi,
Help required to record "Last price matched" odds in column "O" and copied into empty column eg. "AN", after each refresh the odds are again recorded and copied into the next available column eg."AO" and so on........

[yiangos_shiarlis]

Hi All,
still no ideas?

I do have from an old spread sheet, this bit of code, but it recorded the odds by row rather column

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Columns.Count = 16 Then
Application.EnableEvents = False

With ThisWorkbook.Sheets(Target.Worksheet.Name)

.Range("J2").Value = "U" 'updates balance and exposure

'copy data to recording sheet
If .Range("U1").Value = "Y" Then
read_range = .Range("A58:AEB58").Value

ThisWorkbook.Sheets("Recorded Odds").Range(ThisWorkbook.Sheets("Recorded Odds").Range("A65536").End(xlUp).Offset(1, 0), _
ThisWorkbook.Sheets("Recorded Odds").Range("A65536").End(xlUp).Offset(1, UBound(read_range, 2) - 1)).Value = read_range
End If

End With
Application.EnableEvents = True
End If

End Sub

[Captain Sensible]

Something as simple as this would aacheive what you want just by setting MyCount as a variable and adding 1 on each refresh. I'd imagine it will fill up your sheet pretty quickly though and you'd want to clear the data and reset MyCount hen changing markets too.

Code: Select all

Range(Cells(5, 40 + MyCount), Cells(50, 40 + MyCount)).Value = Range(Cells(5, 15), Cells(50, 15)).Value
MyCount = MyCount + 1

There are quite a few examples of various ways of copying data littered over the forum that may be more relevant to what you're actually trying to acheive though.

Code: Select all



Private Sub Worksheet_Change(ByVal Target As Range)
    Static MyCount As Integer
   
If Target.Columns.Count <> 16 Then Exit Sub
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual



Range(Cells(5, 40 + MyCount), Cells(50, 40 + MyCount)).Value = Range(Cells(5, 15), Cells(50, 15)).Value
MyCount = MyCount + 1



    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Exit Sub
   
End Sub



[yiangos_shiarlis]

Hi Captain Sensible,

I just tried it and it works perfectly, thanks again for taking time to look at this for me, I will adjust the refresh rate so not to over fill. MAGIC!!!

regards