q2: -1 to next race

[hahaha]

Hi guys,

I would like to set up q2 to cell to go to the next race when criteria is met. If i write a formula in q2 cell, it would jump to next race, however, when the next race is presenting in excel q2 cell is empty again. Can anyone advice me how to fix this? thanks.

[Captain Sensible]

You pretty much need to use VBA to use the Q2 cell to it's full capabilities, plenty of examples of VBA to move markets on the site. Without knowing what your criteria is it's hard to advise any VBA code tbh.

[knot]

You pretty much need to use VBA to use the Q2 cell to it's full capabilities, plenty of examples of VBA to move markets on the site. Without knowing what your criteria is it's hard to advise any VBA code tbh.

Would i be right in saying that this can only be achieved by using vba and worksheet_calculate ?

worksheet_change would need a manual cell change whereas worksheer_calculate would only need a formula change?

For example:

Code: Select all

Private Sub Worksheet_Calculate()
    If Range("AA1").Value = "Next Event" Then
       'Range("Q2").Value = -1
             End If
End Sub


[knot]

You pretty much need to use VBA to use the Q2 cell to it's full capabilities, plenty of examples of VBA to move markets on the site. Without knowing what your criteria is it's hard to advise any VBA code tbh.

Would i be right in saying that this can only be achieved by using vba and worksheet_calculate ?

worksheet_change would need a manual cell change whereas worksheer_calculate would only need a formula change?

For example:

Code: Select all

Private Sub Worksheet_Calculate()
    If Range("AA1").Value = "Next Event" Then
       'Range("Q2").Value = -1
             End If
End Sub


hahaha DO NOT use my above code as i have just tried it on the Kempton 19:40 race and for some reason it jumped onto Kempton 20:40 missing out the 20:10 race.

I'll get me coat, lol.

[Daywalker]

The problem with that is BA is reading -1 twice. You need to add code to only enter -1 for one refresh.
Also if you look at the example worksheets, you'll notice some code relating to worksheet change and 16 column count and enable events false/true. These are all needed to keep things running smoothly.
Once you start playing about you'll probably get it wrong and stop vba half way through a program, then think it's frozen up. Whenever you use application events false, you need to use application events true to get vba to run again, even if you've stopped the program. You can do this by putting the application events true code in the immediate window and pressing enter.
Good luck, it's fun once you get going.
D

[knot]

The problem with that is BA is reading -1 twice. You need to add code to only enter -1 for one refresh.
Also if you look at the example worksheets, you'll notice some code relating to worksheet change and 16 column count and enable events false/true. These are all needed to keep things running smoothly.
Once you start playing about you'll probably get it wrong and stop vba half way through a program, then think it's frozen up. Whenever you use application events false, you need to use application events true to get vba to run again, even if you've stopped the program. You can do this by putting the application events true code in the immediate window and pressing enter.
Good luck, it's fun once you get going.
D

Why would this be? If you enter -1 by yourself it does not read it twice. What do you mean?

[Daywalker]

Your code is run a second time before your "next event" value has been removed. Therefore ba reads it again and you've missed an event. Using the example sheets as a guide, you can prevent this. The column count of 16 ensures your code is only run once every price refresh and not when other data is refreshed. Also the enable events false prevents changes your code makes from causing another run of the code. You will also find code to only insert -1 once per event by reference of event name.

Some of the things you have to code for aren't obvious but most can be found on here, most have been tackled before. My code is mostly hacked from samples and suggestions on here and forums like mrexcel and ozgrid.

[knot]

Ok i have found this thread but it gives no examples of using Private Sub Worksheet_Calculate(). Only Private Sub Worksheet_Change.

http://www.gruss-software.co.uk/forum/viewtopic.php?f=3&t=5132

Code: Select all

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

If Range("AA1").Value = "Next Event" Then
       Range("Q2").Value = -1
             End If

Application.EnableEvents = True
End Sub

But this code does not work either.

From Google it appears that "Private Sub Worksheet_Change" only works when you change a cell yourself then press the enter key. "Private Sub Worksheet_Calculate()" works when a cell changes is changed by a formula.

If Target.Columns.Count <> 16 Then Exit Sub comes up as an error when using with Private Sub Worksheet_Calculate().

Baffling to me. I guess i will have to join a Excel forum.

[Daywalker]

That code is correct, so far as the worksheet change, column count and enable events is concerned.
Your if statement should run but you don't have any code to prevent a second run. I can't get to my computer at mo but it works something like this-

If a = b and thisEvent <> savedName then
Cell = -1
savedName = thisEvent
End if

So your code is a=b. When the code runs for the first event, savedName will be empty. When a=b, thisEvent will not equal savedEvent so cell = -1 and savedEvent=thisEvent. If the code tries to run again whilst still on same event, it won't get past the thisEvent <> savedEvent.
Once onto the next event savedEvent contains last event name so is ready to run once more when a=b.

Hope that makes sense.

[Daywalker]

also worksheet change needs to be in the sheet code, not a module or workbook.

[Daywalker]

I'm thinking this through and realised you need to be populating q2 with something each time. A positive number is the refresh rate. So in addition to my previous code suggestion add

Else cell=0.2

before the "end if". This way, every refresh will put a value in cell and only once per event can it be -1.
I think I'll wait till I'm in front of computer before jumping in again.

[knot]

Is Gary Russell dead or no longer posting on his own forum?

[GaryRussell]

Sorry I have been quiet lately, but I am still alive and developing.

[knot]

Glad to hear it.

[hahaha]

O.K. I know how to play with index, if, match etc. formulas in excel, but for VBA I am such an expert as for women. Not really good.

Is anyone using VBA q2 trigger and would you be so nice and share a code with me. (step by step instruction would be aa+). Thank you.