Gruss Forum • View topic

by **Shaun** » Tue Sep 21, 2010 1:28 pm

The below code works fine when the market changes but i also need it to run that same macro if the time hits 30 minutes before start, in AA2 i have it linked to D2

Code: Select all: Private Sub Worksheet_Calculate() Static MyMarket As Variant Application.EnableEvents = False Application.Calculation = xlCalculationManual If [A1].Value = MyMarket Then GoTo Xit Else MyMarket = [A1].Value If Sheets("Venue").Range("AA2").Value = "0:30:00" Then Call Copy Else Application.OnTime Now + TimeValue("00:00:10"), "Copy" Xit: Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End If End If End Sub

by **Shaun** » Wed Sep 22, 2010 3:07 pm

Any help with this please

by **osknows** » Thu Sep 23, 2010 1:10 pm

It looks like when the time is reached nothing is calculating so the worksheet_calcualte() event won't fire

Place your code below in the worksheet_change event instead

If Sheets("Venue").Range("AA2").Value = "0:30:00" Then
Call Copy

end if

by **Shaun** » Thu Sep 23, 2010 1:33 pm

I need the calculate event so the market change part will fire or will that work under the worksheet_change event as well

by **osknows** » Thu Sep 23, 2010 1:47 pm

That's fine, you can have both events. The market change can also be run from the change event too.

by **Shaun** » Thu Sep 23, 2010 1:51 pm

This is my current code, works exactly like i need for the market change.

Code: Select all: Private Sub Worksheet_Calculate() Static MyMarket As Variant Application.EnableEvents = False Application.Calculation = xlCalculationManual If [A1].Value = MyMarket Then GoTo Xit Else MyMarket = [A1].Value Sheets("Venue").Range("Z5:Z44").Value = "" Application.OnTime Now + TimeValue("00:00:10"), "Copy" Xit: Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End If End Sub

this is what i would like it to do but it does not work or if the code is in wrong place it crashes excel

Code: Select all: Private Sub Worksheet_Calculate() Static MyMarket As Variant Application.EnableEvents = False Application.Calculation = xlCalculationManual If Sheets("Venue").Range("AB2").Value = 1 Then Sheets("Venue").Range("Z5:Z44").Value = "" Application.OnTime Now + TimeValue("00:00:10"), "Copy" Else If [A1].Value = MyMarket Then GoTo Xit Else MyMarket = [A1].Value Sheets("Venue").Range("Z5:Z44").Value = "" Application.OnTime Now + TimeValue("00:00:10"), "Copy" Xit: Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End If End If End Sub

I need it to check 2 things, i have a time setup on the sheets that populates AB2 with a 1 at the 30 minute mark, so i need it to check that, other wise it needs to check for new market

by **osknows** » Thu Sep 23, 2010 2:12 pm

Your turning off events and calcs at the start of the code but only turn them back on if the conditions are met in the IF statement

Code: Select all: Private Sub Worksheet_Calculate() Static MyMarket As Variant Application.EnableEvents = False Application.Calculation = xlCalculationManual If Sheets("Venue").Range("AB2").Value = 1 Then Sheets("Venue").Range("Z5:Z44").Value = "" Application.OnTime Now + TimeValue("00:00:10"), "Copy" Else If [A1].Value = MyMarket Then GoTo Xit Else MyMarket = [A1].Value Sheets("Venue").Range("Z5:Z44").Value = "" Application.OnTime Now + TimeValue("00:00:10"), "Copy" End If End If Xit: Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub

by **Shaun** » Thu Sep 23, 2010 2:59 pm

I tried to test it but when it ran the first part of the macro excel crashed

by **osknows** » Thu Sep 23, 2010 3:06 pm

The code I posted works ok here. Do you have a sheet named "Venue"?

Temporarily change the sub named 'Copy' to the following to test if this code above works

Sub copy()
MsgBox ("COPY OK!")
End Sub

by **Shaun** » Thu Sep 23, 2010 3:29 pm

When i change the market the copy code works, but when cell AB2 is populated with 1 it crashed, would it matter if i had 3 different work books running this same code.

by **osknows** » Thu Sep 23, 2010 3:46 pm

Thought you might be

Your code needs to state which workbook, sheets and ranges to use if mutliple are being used. Eg, if you just use range("A1") it take A1 from the current active sheet only.

Code: Select all: Private Sub Worksheet_Calculate() Static MyMarket As Variant Application.EnableEvents = False Application.Calculation = xlCalculationManual With ThisWorkbook If .Sheets("Venue").Range("AB2").Value = 1 Then .Sheets("Venue").Range("Z5:Z44").Value = "" Application.OnTime Now + TimeValue("00:00:10"), "Copy" Else If [A1].Value = MyMarket Then GoTo Xit Else MyMarket = [A1].Value .Sheets("Venue").Range("Z5:Z44").Value = "" Application.OnTime Now + TimeValue("00:00:10"), "Copy" End If End If End With Xit: Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub

You will need to ensure you update the macro 'Copy' and any other subs to do the same

by **Shaun** » Thu Sep 23, 2010 4:26 pm

Damn, you only just told me that the other day and i missed it again, gees i am thick at times.

by **Shaun** » Thu Sep 23, 2010 6:10 pm

I used your above code and change the copy code but still wants to crash when cell AB2 populates, i know it is not the copy code as that works fine when the sheet changes.

not sure what the problem is

by **osknows** » Fri Sep 24, 2010 10:37 am

What do you mean by crash? Excel vanishes, or the code breaks on a certain line?

What formula do you have in AB2, possibly if it creates an error such as #N/A or #VALUE! then it may cause an error with the code

by **Shaun** » Fri Sep 24, 2010 12:09 pm

I have made a few changes to try and fix this so will try to explain, but excel comes up with big error screen and has to close.

In AB2 i had a code that compared the count down clock in BA with a predefined time i wanted to update the prices, 30 minutes before the start so i could load it all up in the morning and leave it.

As you know i am using 1 workbook for each venue, originaly i had a formula to populate AB2 with a 1 when 30 minutes was hit, i have since changed this and put that in the macro i have also stopped calling another macro and made the code simpler.

current code minus the section that causes errors

Code: Select all: Private Sub Worksheet_Calculate() Static MyMarket As Variant Application.EnableEvents = False Application.Calculation = xlCalculationManual With ThisWorkbook If [A1].Value = MyMarket Then GoTo Xit Else MyMarket = [A1].Value .Sheets("Venue").Range("Z5:Z44").Value = "" .Sheets("Venue").Range("Z5:Z44").Value = .Sheets("Venue").Range("AA5:AA44").Value End If End With Xit: Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub

As soon as i add anything like the below code it crashes, it is always the same thing the code works good until the 30 minute then crash, do i need to use the Worksheet_Calculate or is there another option that will update my prices when the race changes and at the 30 minute mark

Code: Select all: Private Sub Worksheet_Calculate() Static MyMarket As Variant Application.EnableEvents = False Application.Calculation = xlCalculationManual With ThisWorkbook IF .Sheets("Venue").Range("Z5:Z44").Value = "" .Sheets("Venue").Range("Z5:Z44").Value = .Sheets("Venue").Range("AA5:AA44").Value Else If [A1].Value = MyMarket Then GoTo Xit Else MyMarket = [A1].Value .Sheets("Venue").Range("Z5:Z44").Value = "" .Sheets("Venue").Range("Z5:Z44").Value = .Sheets("Venue").Range("AA5:AA44").Value End If End If End With Xit: Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub

Maxro Help

Maxro Help

Who is online

Sports betting software from Gruss Software