Suggestions Please

[Shaun]

I am in need of some help, i know what i want my excel sheet to do but not sure how to get it to do it, i will explain.

I am betting mulltiple runners in a race, normally 3 but sometimes 4 or 2 i am running an allup series over 4 races if the selection losses i stay on 1 unit if the selection wins i go to 2 units and so on.

Each venue has it's own sheet, if i stayed on the same number of bets i would not have a problem i could judt check for RESULT_WON
or RESULT_LOST in F2 to F4 of result sheet, but as the number of bets changes i need to know how many bets to check for on the result.

I was thinking that maybe when the results are known the profit loss could be worked out and recorded, and just before the next bets are due to be placed the results sheet could be cleared ready for the new bets.

A macro could record the info in a cell that would only change when the macro ran again and another macro could clear the shett at a particular time before the next bet is placed.

[Shaun]

I think i found a solution to my problem but i need a code change in this VBA and as i am clueless to VBA i wonder if you could help

I have made a small change myself where the code is looking in cell K1 but i don't want to clear cells when the market changes i want it to clear cells when it see the word "clear"

Code: Select all

Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If [K1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [K1].Value
Range("A2:F10").Value = ""
End If
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub



[Shaun]

I may have found solution to problem

Code: Select all

Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If [K1].Value = MyMarket Then
GoTo Xit
Else
[I1] = [I1] + [Sum(I2:I6)]
MyMarket = [K1].Value
Range("A2:F6").Value = ""
End If
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub


[Shaun]

I got the above code to do what i want but now i have another problem.

If i back 3 runners in the first race and then have the results cleared before putting on the next bets when it changes to the next race all past results are imported to the Results sheet even thought i have cleared it.

Does anyone know how i can stop this happening so just the last set of results are listed.

[dgs2001]

Shaun

A "-7" placed in cell Q2 will stop the results list being repopulated, you will still have to clear it.

Duncan

[Shaun]

I have made changes to the selections so only 3 can ever be bet on or no race, the main problem i have with this macro it is activated by a timer when it sees the time it will record the info, problem is it records it twice as it passes by the time how can i solve this and just record it once

Code: Select all

Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If [M1].Value = MyMarket Then
GoTo Xit
Else
[K1] = [K1] + [Sum(J1)]
MyMarket = [M1].Value
End If
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub



[Shaun]

Problem solved , i just changed the way i do things.