VBA code to copy data to another cell

[dealbanker]

Hello

I have searched the internet and tried several VBA codes offered but none have done the job.

I currently have data in AA5:AA25 that is duplicating the odds data received in F5:F25.

My aim is to have the data copy into the adjacent blank cells (AB5:AB25) as/or just before the new data arrives in the original AA cells - every 2 seconds.

Does anyone know of a very simple VBA code for this scenario it would be greatly appreciated...

cheers

[Captain Sensible]

Plenty of example bits of code in the forum, are you trying to create an audit trail of prices? i.e. on the next refresh data gets copied to AC5:AC55 and so on, fixing that price once or continually copying at each refresh?

Have a look at http://gruss-software.co.uk/forum/viewtopic.php?t=1885 it might be overkill for what you need but easily tweaked to cover most things

[dealbanker]

Thanks Captain

Thank you for the link but may be way beyond what I need. Not being that bright about VBA I am not sure what to pick out of all that text.

I am not trying to create an audit trail of prices but only to continually copy at each refresh. It is simply to compare the difference between odds over 2 second intervals, using a number of spreadsheet formulas.

The difficulty is in finding a code that will copy/save the old data into a separate column when the new data appears every 2 seconds. The current price and previous price are then available together in real time (for 2 seconds) in order to make calculations with the other formulas.

Thanks

[Captain Sensible]

Not sure why you need to copy data in F to AA, seems an but unneccessary step but something like this might be sufficient

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

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


Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Range("AB5:AB25").Value = Range("AA5:AA25").Value
Range("AA5:AA25").Value = Range("F5:F25").Value


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


[dealbanker]

Thanks Captain

That worked perfectly!
A simple but far more reliable and effective code than I had found previously

cheers

[Shaun]

I have always found this the best method and does not suffer manual change of sheets when it is running.

[dealbanker]

Hello Captain

The code you gave me previously works a treat and providing excellent returns. I need to create an additional indicator to my method that uses this same idea but I can not seem to make it work. I have tried to use the same code with adjustments, unsuccessfully.

The aim was to make a copy of all the data in column F5:F25 paste into the AD column (AD5:AD35), and it copies the data only once when the race goes into play.

I have a a flag cell that displays the number `1' when the race goes into play.

I need to make the data copy from the F column into the AD column at the time the race goes into play, when the flag cell (V2) shows `1'.

Do you have any suggestions?

Thanks in advance...

[Captain Sensible]

You just need to stick the code within an if statement, i.e. if V2 equals 1 then do that code and also add another flag to say V3 so it only copies once.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

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

If Cells(2, 22) = 1 And Cells(2, 23) <> 1 Then
   
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Range("AD5:AD25").Value = Range("F5:F25").Value
Cells(2, 23) = 1

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End If

End Sub
 

Then just amend your usual code to clear the V2 and AD cells when you switch markets

[dealbanker]

Thanks for the code Captain but the comment about amending the usual code to clear the cells when switching mkts?

I have been through the GRUSS manual but find nothing.
Is this done with a cell formula or another VB code?
Would appreciate being pointed in the right direction.

BTW the code worked perfectly ta

cheers

[Captain Sensible]

Gary stuck up some code years ago that'd allow you to track market changes, I still use it to clear trigger cells/do other things when markets change etc

Something like this would clear ranges AD:5:AD25 and cell V3 when the market switches if you were automating things.

Code: Select all

Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If [A1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [A1].Value
Range("AD5:AD25").Value = ""
Range("V3").Value = ""


'insert any additional coding here




End If
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

[Shaun]

If you so choose you can add both those codes together.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Static MyMarket As Variant
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
With ThisWorkbook
If .Sheets("Data").Range("T2").Value <> 1 Then
   .Sheets("Data").Range("T3").Value = 0
GoTo Mrkt
End If
If .Sheets("Data").Range("T2").Value = 1 And Sheets("Data").Range("T3").Value <> 1 Then
    Range("AD5:AD25").Value = Range("F5:F25").Value
   .Sheets("Data").Range("T3").Value = 1
End If

Mrkt:
    If .Sheets("Data").Range("A1").Value = MyMarket Then
        GoTo Xit
    Else
    MyMarket = .Sheets("Data").Range("A1").Value
    If .Sheets("Control").Range("X9").Value = 1 Then
    Range("AD5:AD25").Value = ""
    Range("V3").Value = ""
    End If
End With
Xit:
Application.EnableEvents = True
End Sub

[dealbanker]

Thank you Captain and Shaun

Apologies for late reply (hospital stay).
I could get neither codes to work but found that the idea was not needed in the end. In fact if I had followed through my idea would probably have been detrimental to the plan.

I believe what I actually need is an extra column in the grid interface, probably 4 or 5 each side. Do you know if it is possible to do this with GRUSS?

Thanks

[Captain Sensible]

Thank you Captain and Shaun

Apologies for late reply (hospital stay).
I could get neither codes to work but found that the idea was not needed in the end. In fact if I had followed through my idea would probably have been detrimental to the plan.

I believe what I actually need is an extra column in the grid interface, probably 4 or 5 each side. Do you know if it is possible to do this with GRUSS?

Thanks

Not sure what you mean by an extra column in the grid interface???

Do you mean in the excel interface? If so you can just export the data to start at cell F1 rather than A1, not sure exactly what you're looking or asking for.

[dealbanker]

Thanks Captain

Sorry I did mean Excel interface.
We have 3 Lay Odds columns and 3 Back odds column in Excel.
Can I create 4 columns of each or even 5 columns of each in the sheet or is it limited to 3?

[Captain Sensible]

Using the normal grid excel interface you're limited to the three prices for lay and back. To get more prices exported you'd have to use the COM interface and code it up yourself.

Have a look at

http://gruss-software.co.uk/forum/viewtopic.php?t=3825

So far I've had no need to utilize or need the COM side of things so wouldn't have a clue how to code it up, probably a few examples around the site though, just do a search6 and you'd probably find something you could cannibalise.