Excel Function or Macro

[AndrewP]

Hi All,

Can anyone help me with an Excel function or macro?

What I want to do is determine the prices at which I will back or lay selections from their last traded prices. It's easy enough to put formulae in Excel to do this but I don't want them to be dynamic and alter as the last traded price updates. I would like the last traded prices to be read once upon (auto) selection and my back and lay prices claculted from this reading with no further updating. How can I do this?

Regards,

Andrew

[phrenetic]

Andrew

Try this:

Code: Select all

Public sRaceDets As String

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Integer
Dim iCol As Integer
Dim cell As Object

For Each cell In Target
  iRow = cell.Row
  iCol = cell.Column

  ' Check for new race being loaded
  If iCol = 1 And iRow = 1 Then
    If LCase(cell.Value) <> sRaceDets Then
      sRaceDets = LCase(cell.Value)

      ' Copy last matched prices into column AA
      Application.EnableEvents = False
      For theRow = 5 To 44
        Range("AA" & theRow).Value = Range("O" & theRow).Value
      Next theRow
      Application.EnableEvents = True
    End If
  End If
Next cell
End Sub


It will copy the last price matched values into column AA whenever a race is selected.

Alistair

[AndrewP]

Alistair,

Thank you for this.

Forgive my ignorance but can you explain blow by blow what I do with the code? I'm afraid I'm a novice when it comes to VB although I understand basic Excel spreadsheets well.

Andrew

[phrenetic]

Andrew

Just right click on the tab for the worksheet that you are linking to BA and select the View Code option. A macro coding sheet will be opened.

Cut and paste the function from my previous message into the coding sheet. Save the macro and close the macro coding sheet.

Now whenever a new race is selected by BA, the spreadsheet will be updated and the last matched price values will be copied into column AA automatically.

Alistair

[AndrewP]

Thanks again Alistair, it works perfectly.

Regards,

Andrew

[AndrewP]

Alistair,

Can I be really cheeky and ask if you could modify the code to capture last matched prices to column AH when cell W1 reads "In Play" rather than upon loading the race? I have another strategy I would like to try out.

Thanks,

Andrew

[phrenetic]

Sure - I'll post something later this afternoon.

Alistair

[phrenetic]

Andrew

*** Note - due to a complete Internet outage this evening, I have not been able to test this ****

Try this:

Code: Select all

Public bOddsCopied As Boolean
Public sRaceDetails As String

Private Sub Worksheet_Calculate()
   ' Reset the flags if a new race is loaded
  If LCase(Range("A1").Value) <> sRaceDets Then
    sRaceDets = LCase(Range("A1").Value)
    bOddsCopied = False
  End If
   
  ' Check to see if race has just gone In-Play (status is held in cell W1)
  If LCase(Range("W1").Value) = "in play" And bOddsCopied = False Then
    ' Copy last matched prices into column AH
    Application.EnableEvents = False
    For theRow = 5 To 44
      Range("AH" & theRow).Value = Range("O" & theRow).Value
    Next theRow
    Application.EnableEvents = True
    bOddsCopied = True
  End If
End Sub     


Alistair

[AndrewP]

Alistair,

I tested the code by auto selecting the US market and simulating cell W1 turning 'in play' and it seems to work just fine. I'll let you know how it fares in the UK market when turning 'in play' for real.

I really am most grateful to you for this, it would take me ages to work it out for myself.

Best regards,

Andrew

[AndrewP]

Alistair,

In testing the code on 'in play' markets I see that as my 'in play' flag in cell W1 refreshes so do the prices in column AH. It is effectively therefore giving me the same last traded prices as colunm O. My intention was to capture the last traded prices just as the market turns in play (BA seems to allow for the market to settle down) and not keep refreshing them. Is there a way of doing this?

Andrew

[Captain Sensible]

Andrew try amending the line

Public sRaceDetails As String


to

Public sRaceDetls As String

and it should hopefully work

[Captain Sensible]

Public sRaceDets As String

[phrenetic]

Andrew

Sorry - the dangers of posting something without being able to check it first.

As Captain Sensible says, just change the line to read:

Public sRaceDets As String

Alistair

[AndrewP]

Thanks Guys, I'll give it a whirl tomorrow.

Andrew