VBA code, change to next race keeping refresh rate even

[volcom64]

Hi everyone,

I need some help with VAB code to change to the next market when a race finishes.
I got a code at the forum but I need a simple change:
-I need the cell [Q2] after changing market looks like this: [Q2]="=SE(E(HORA(D2)=0;MINUTO(D2)<=1);0,5;10)"


Option Explicit

Dim currentMarket As String
Dim inPlay As Boolean
Dim inPlayTime As Date
Dim marketChanged As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
If [A1] <> currentMarket Then
currentMarket = [A1]
inPlay = False
marketChanged = False
End If
If [E2] = "In Play" Then
If Not inPlay Then
inPlay = True
inPlayTime = Now
End If
If [F2] = "Suspended" And Not marketChanged And DateDiff("s", inPlayTime, Now) > 20 Then
marketChanged = True
Application.EnableEvents = False
[Q2] = "-1"
Application.EnableEvents = True
End If
Else
inPlay = False
End If
End If
End Sub

I know this is very simple but I could not do it.
I hope you can help.
thanks to all

[osknows]

how about this...

Code: Select all

Option Explicit

Dim currentMarket As String
Dim inPlay As Boolean
Dim inPlayTime As Date
Dim marketChanged As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Columns.Count = 16 Then
   
    Application.EnableEvents = False
   
        If [A1] <> currentMarket Then
            currentMarket = [A1]
            inPlay = False
            marketChanged = False
        End If
       
        If [E2] = "In Play" Then
       
            If Not inPlay Then
                inPlay = True
                inPlayTime = Now
            End If
           
            If [F2] = "Suspended" And Not marketChanged And DateDiff("s", inPlayTime, Now) > 20 Then
                marketChanged = True
                [Q2] = "-1"
            End If
        Else
            inPlay = False
        End If
       
        If [Q2] = "" Then [Q2].Formula = "=SE(E(HORA(D2)=0;MINUTO(D2)<=1);0,5;10)"
       
        Application.EnableEvents = True
    End If
End Sub

[volcom64]

Thanks for so fast reply.

The code doesn't work.

I tried to change to If [Q2] = "" Then [Q2] = "=SE(E(HORA(D2)=0;MINUTO(D2)<=1);0,5;10)"

don't give error but does not change the market too.

Today there are no more races to try a code but if you can help I would greatly appreciate it

[osknows]

Does the formula work on it's own in Excel? It's a different layout in the UK so I can't test it here but If [Q2] = "" Then [Q2].formula = "=IF(AND(HOUR(D2)=0,MINUTE(D2)<=1),0.5,10) was working fine for me.

Assuming your formula is correct this will also work

If range("Q2").value = "" or isempty(range("Q2").value) Then range("Q2").formula = "=SE(E(HORA(D2)=0;MINUTO(D2)<=1);0,5;10)"

or in English...

If range("Q2").value = "" or isempty(range("Q2").value) Then range("Q2").formula = "=IF(AND(HOUR(D2)=0,MINUTE(D2)<=1),0.5,10)"

[volcom64]

Ok, tomorrow I will test this.

Unfortunately my excel functions are in portuguese, I need to reinstall it because I prefer english functions too.

Thank you very much for your help, I will try to learn a little more VBA now.

Useful to comment on another issue: In the morning when I tested the code (the original I posted), to pass from the first to the second race I had to change manually, then to all the other races worked well. Can be any variable that is not initially with the correct value (like "true" or "false"...)? Can you confirm this?

I apologize for the bad english and very little knowledge on VBA, tomorrow or Tuesday I will test this with more time.

Thanks again

[osknows]

I can't see anything obvious wrong. If a market doesn't forward as expected, put a breakpoint on the start of the event and step through it line by line. That should identify which term is causing the problem.

There's a method to forward markets in this thread. See the example Record & Playback.zip in sheet M1. [/url]

[volcom64]

Got It!

Damn, my excel functions in spreadsheet are in portuguese, but in VBA I need to write: "=IF(AND(HOUR(D2)=0,MINUTE(D2)<=1),0.5,10)" and commas and points is not equal too.

So the correct code:

Code: Select all

Option Explicit

Dim currentMarket As String
Dim inPlay As Boolean
Dim inPlayTime As Date
Dim marketChanged As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Columns.Count = 16 Then
   
    Application.EnableEvents = False
   
        If [A1] <> currentMarket Then
            currentMarket = [A1]
            inPlay = False
            marketChanged = False
        End If
       
        If [E2] = "In Play" Then
       
            If Not inPlay Then
                inPlay = True
                inPlayTime = Now
            End If
           
            If [F2] = "Suspended" And Not marketChanged And DateDiff("s", inPlayTime, Now) > 20 Then
                marketChanged = True
                [Q2] = "-1"
            End If
        Else
            inPlay = False
        End If
       
        If Range("Q2").Value = "" Or IsEmpty(Range("Q2").Value) Then Range("Q2").Formula = "=IF(AND(HOUR(D2)=0,MINUTE(D2)<=1),0.5,10)"
       
        Application.EnableEvents = True
    End If
End Sub

just misses confirm what I said in the previous post (about variables)

[volcom64]

I found my problem, my excel. tried to run other files from the forum and happened to me exactly the same thing.
already installed the English version and the errors persist even

when I do this, everything goes the way I want:

http://imageshack.us/photo/my-images/855/k4ts.jpg/

when I change to this:

http://imageshack.us/photo/my-images/834/ll2q.jpg/

http://imageshack.us/photo/my-images/713/7rb5.jpg/

the problem is in [Q2] = "= ..." because [Q2] = "123" does not exist error

tried [Q2]. formula = "= .." and the error persists.

Any idea what it might be?

Thank you all, yesterday was testing step by step and I seemed to be okay, however, today nothing worked.

[osknows]

on the first image you have

[B1]= "[A2]" which means you are setting cell B1 to a string type with value [A2]

In the second image you have

[B1]= "=[A2]" which means you are setting cell B1 formula to =[A2] but this is not a valid Excel formula and so gives an error.

You probably want

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
[B1] = "=A1"
Application.EnableEvents = True
End Sub

[volcom64]

Thanks for the help, probably everything is working now, but I will test tomorrow.

Look what happens when I use your program "Record Playback v1.0":
I Link to BA on MA1 sheet and i get the same-error:

http://imageshack.us/photo/my-images/835/plr5.jpg/

Any ideas? Sorry for all questions.

Thanks

[alrodopial]

Thanks for the help, probably everything is working now, but I will test tomorrow.

Look what happens when I use your program "Record Playback v1.0":
I Link to BA on MA1 sheet and i get the same-error:

http://imageshack.us/photo/my-images/835/plr5.jpg/

Any ideas? Sorry for all questions.

Thanks

Replace the comma "," with ";" in the code - yellow line
It's the Portuguese version you are using that uses ";" in the formulas instead of ","

[volcom64]

I will install permanently the english version.

Thank you all for the help

[osknows]

If installing the English version doesn't help, have a look at this http://akbaraji.wordpress.com/2012/05/2 ... -csv-file/

[volcom64]

If installing the English version doesn't help, have a look at this http://akbaraji.wordpress.com/2012/05/2 ... -csv-file/

I cant believe that was so simple, everything work now at the portuguese version too. Thanks