Keep getting error in status bar please help.

[Willygubbins]

I Have used =IFERROR(VALUE(MID(TEXT(F4,"hh:mm:ss"),4,2)),"-"
excell came back saying there was a spelling mistake and rectified it and now it works

[Willygubbins]

Another question is it possible to recorded the largest figure that appears in the Green up cell ie D10 or if not the actual value an incremental indicator that it acheived a certain value?

[ShaunWhite]

Another question is it possible to recorded the largest figure that appears in the Green up cell ie D10 or if not the actual value an incremental indicator that it acheived a certain value?

It's time for a little bit of VBA.....

1. Open the Visual Basic Editor
2. Double click on a sheet in the project explorer.

3. Paste this....
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "F4" Then
If Range.Value("F999") < Range.Value("F4") Then
Range.Value("F999") = Range.Value("F4")
End If
End If

End Sub


Obviously change the cell references to something relevent to you.

You will need to clear the cell when the market changes...but I hope if you understand how that little bit of code works above, you'll know how to spot other changes (eg market name changes) and preform a relevent action... You can stick more and more code between the Sub and End Sub

It will now look something like this

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "F4" Then
If Range.Value("F999") < Range.Value("F4") Then
Range.Value("F999") = Range.Value("F4")
End If
End If

If Target.Address = MarketNamePlace Then
If Range.Value(SavedMarketNamePlace) <> Range.Value(MarketNamePlace ) Then
Range.Value(SavedMarketNamePlace) = Range.Value(MarketNamePlace )
Range.Value("F999") = 0

End If
End If
End Sub

There's no promise any of that will work 'as is' as I just threw that together... but I hope I've steered you in the right dirrection.

( The forum software has lost all my lovely neat indenting on those IF's)

[Willygubbins]

Another question is it possible to recorded the largest figure that appears in the Green up cell ie D10 or if not the actual value an incremental indicator that it acheived a certain value?

It's time for a little bit of VBA.....

1. Open the Visual Basic Editor
2. Double click on a sheet in the project explorer.

3. Paste this....
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "F4" Then
If Range.Value("F999") < Range.Value("F4") Then
Range.Value("F999") = Range.Value("F4")
End If
End If

End Sub


Obviously change the cell references to something relevent to you.
And I'm right in assuming i can just layer this in for all the diffrent cells with an End If / If inbetween the cell details?



You will need to clear the cell when the market changes...but I hope if you understand how that little bit of code works above, you'll know how to spot other changes (eg market name changes) and preform a relevent action... You can stick more and more code between the Sub and End Sub

It will now look something like this

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "F4" Then
If Range.Value("F999") < Range.Value("F4") Then
Range.Value("F999") = Range.Value("F4")
End If
End If

If Target.Address = MarketNamePlace Then
If Range.Value(SavedMarketNamePlace) <> Range.Value(MarketNamePlace ) Then
Range.Value(SavedMarketNamePlace) = Range.Value(MarketNamePlace )
Range.Value("F999") = 0

End If
End If
End Sub

There's no promise any of that will work 'as is' as I just threw that together... but I hope I've steered you in the right dirrection.

( The forum software has lost all my lovely neat indenting on those IF's)

I found this on google which seems to work fine,


Private Sub Worksheet_Calculate()
If [A1] > [A2] Then
[A2] = [A1]
End If
End Sub

Do the square brackets have some relevence? as that is fare simpler than your formula, I have referenced the cells i wish to use on to a seperate sheet then used the above formula.

[nigelk]

The square brackets are just "shorthand", see here for a quick reference:

http://www.quepublishing.com/articles/a ... 8&seqNum=4

I have referenced the cells i wish to use on to a seperate sheet then used the above formula.

Excel always assumes that the activesheet (the one you are looking at), is the one you are referring to. So if you are on sheet 1 and Range("A1") is empty, that's what you'll get back. If the value you are after is on sheet 2 you'll have to tell it to look specifically at that sheet eg. Sheets("Sheet2").Range("A1")

[Willygubbins]

Thanks Nigel,

I have done the follows,
on sheet 2

A1="SHEET1"!D1O

I have had to do this for all possible runners because if i picked one and this had a back order on it it would show the total pontential profit from the order as the highest value once the race has ended, I realise this is quite possibly the long way of doing it.
I have then used the follow VBA to record to record the highest / lowest achieved figure for that row/runner,

Private Sub Worksheet_Calculate()
If [A1] > [B1] Then
[B1] = [A1]
End If
If [A1] < [C1] Then
[C1] = [A1]
End If

Is there away of duplicating the VBA formula for different groups of cells so that it changes the cell references in a logical manner, for example by column or row?

[ShaunWhite]

I found this on google which seems to work fine,

Private Sub Worksheet_Calculate()
If [A1] > [A2] Then
[A2] = [A1]
End If
End Sub

Do the square brackets have some relevence? as that is fare simpler than your formula, I have referenced the cells i wish to use on to a seperate sheet then used the above formula.

It's far simpler but is not good practice.. My version my appear long winded but was still a simpler version of what one would actually code. For instance it uses hard coded cell references, excludes the sheet names, doesn't include any error trapping. I'm also not sure why you would choose the .calculate event rather than the . change event. The problem with VBA is it allows you to be sloppy, I'm from the 'do it once, do it right' school but that ethos is a result of being in development for 30years.

Is there away of duplicating the VBA formula for different groups of cells so that it changes the cell references in a logical manner, for example by column or row?

Yes, there's nothing you can think of that can't be done. You can either duplicate the formula, move the formula to a separate SUB and call it several times with parameters, use a SELECT CASE/END SELECT construct, or if the cell references for a logical sequect use a FOR/NEXT loop.

I don't mean to try and confue you willy, I'm just trying to set you off with best practice in mind. It's like golf, a comfortable grip wil get you going but if you want to be any good you need to get used to using an uncomfortable grip.

[Willygubbins]

Thanks Shaun, I understand what you are saying, its is all a bit over whelming, I have odered some books so hopwfully that will help.

Using said formula, to recordlowest odds achieved, it doesnt work because the relevent cell is considered to be 0.00? i am assumingso it will never register a value where the reference cell is lower than that, how do i over come this?

[ShaunWhite]

Thanks Shaun, I understand what you are saying, its is all a bit over whelming, I have odered some books so hopwfully that will help.

Using said formula, to recordlowest odds achieved, it doesnt work because the relevent cell is considered to be 0.00? i am assumingso it will never register a value where the reference cell is lower than that, how do i over come this?

That's fair enough willy, it is overwhelming to start with. But I promise you that one day you'll have a lightbulb moment and then you'll be off and running. You'll probably only have to learn half a dozen commands/statements and be able to do whatever you like. It's like any foreign language, you don't need to learn the whole dictionary if you can do what you want by knowing the words for 'beer' 'bill' and 'taxi'. Please excuse me if I come across as impatient, it's not meant that way.

I can only guess that the 'relevent cell' is looking at the wrong place on your spreadsheet. By relevent cell do you mean the current BA price cell, or the cell that is supposed to store the highest or lowest value ?

But....If you need more help I'd be more than happy to have a look at your spreadsheet for you, if you attach it or send me a PM. It's difficult helping otherwise and I could easily tell you a load of things that send you down the wrong path when it might only be a typo.

[Willygubbins]

Shaun,

I'm still using the cheating easy formula at the moment....... I for some reason have been unable to get the runners names to reference onto my sheet.......it is a rough sheet, but it will serve me for what i need at the moment.
So issues with,

Record lowest achieved odds and cant get runner names to reference across.

Thanks

[nigelk]

Would a simple max formula do what you require?

On sheet 2 you can see the maximum amount is on row 11, along with the name of the runner

[Willygubbins]

Would a simple max formula do what you require?

On sheet 2 you can see the maximum amount is on row 11, along with the name of the runner

the problem with the maximum recorded value of all the cells is that ultimately it will be the figure shown in the induvidual profit and loss at the end of the race, ie the potential winnings of the horse you backed, this is why I have listed for all runners.
The problem i am currently having is recording the lowest back odds achieved by each runner.

[ShaunWhite]

I'll have a look for you later tonight.

[ShaunWhite]

OK...here goes.

I've attached your xls with a few tweaks You had made a decent effort to do that coding, just getting into that bit is an acheivement.

What I've sent you as far from complete...it takes a long time to do a nice job. For instance, for the time being, don't move anything on the 'GreenUp' sheet unless you change the code as well. I didn't have time to name ranges etc. I've commented the code quite a lot but it's not 100%

The biggest change I made was to put a 'Start' and 'Stop' button on the sheet to switch on/off the updates. I set it to 1 second but search for 'PauseTime' in the code and you can change it.

I changed it all to run on a timer because the problem with using "worksheet.change" or "worksheet.calculate" is that when the code changes the worksheet... it spots that it's changed, and re-runs the damn thing again...it sort of disappears up it's own arse.

OK, my fingers are sore...I spent a couple of hours on it. I hope it's a decent start. DO get back to me if you need too... I'm slightly addicted to coding so won't mind. I saved it as Excel97 cos I don't know what version you run.

oh....I haven't had chance to give it a good test...if it crashes choose 'Debug' from the popup and it should put you at the offending line of code. I think you stand a good chance of working out the problem.

When you're looking at the code. Press F9 on any line to toggle a breakpoint, it will stop there when it runs... you can then use F8 to step through the code line by line to see what it does. F5 will make it run full speed until it reached the next breakpoint. .. have fun.

[Willygubbins]

OK...here goes.

I've attached your xls with a few tweaks You had made a decent effort to do that coding, just getting into that bit is an acheivement.

What I've sent you as far from complete...it takes a long time to do a nice job. For instance, for the time being, don't move anything on the 'GreenUp' sheet unless you change the code as well. I didn't have time to name ranges etc. I've commented the code quite a lot but it's not 100%

The biggest change I made was to put a 'Start' and 'Stop' button on the sheet to switch on/off the updates. I set it to 1 second but search for 'PauseTime' in the code and you can change it.

I changed it all to run on a timer because the problem with using "worksheet.change" or "worksheet.calculate" is that when the code changes the worksheet... it spots that it's changed, and re-runs the damn thing again...it sort of disappears up it's own arse.

OK, my fingers are sore...I spent a couple of hours on it. I hope it's a decent start. DO get back to me if you need too... I'm slightly addicted to coding so won't mind. I saved it as Excel97 cos I don't know what version you run.

oh....I haven't had chance to give it a good test...if it crashes choose 'Debug' from the popup and it should put you at the offending line of code. I think you stand a good chance of working out the problem.

When you're looking at the code. Press F9 on any line to toggle a breakpoint, it will stop there when it runs... you can then use F8 to step through the code line by line to see what it does. F5 will make it run full speed until it reached the next breakpoint. .. have fun.

Wow,

Thanks shaun,
I will test this but proberly only on one market as i have to go out, I have 2 Questions........
My plan is to set this up in the morning to run all day with a sperate bet angel tab for each market and a subsequent green up data tab for each of those,
Can you or instruct me how to change the start stop buttons to a timed start and stop, start say 5 Mins before the off and and stop when the it suspends at the end of the race.
I need to duplicate both the Bet Angel tab and the Green Up tab say 50 times to allow for a tab a market so that all the data can be retained for assessment after and so all bets can be placed at the same time, timed (as was my original question), also the minutes timer has been removed will reinstating this up set any thing?
I am happy to try and do these things myself under your guidence, But you would need to instruct me very much step by step as I really am inexperienced with this, but i plan on learning can you recommend any good guides/books obvs there is the dummies series which is appropriate at the moments.