Code executing Twice each time it's called...

I have designed a workbook to record live data market movements in sports betting markets, Please see the attached TESTING file.

Sheet 1 is the live feed from the API I am sourcing the data from.Race 1 is where this processed data is stored.

There are 2 ways I have set up sheet 'race1 to log the live feed'...

1.) From a press of the green command button _2 ('update') button around cell C19 to execute check1 macro (situated in module1). This works absolutely fine each time it's clicked, the data is logged starting at A61. There is no problem with this.

2.) From matching the schedule times (Cells D1...L1) with cell A21(the live time on sheet1) to give a 1 value when matched and this 1 value to trigger the same check1 macro that the Command button_ 2 press executes in method 1 above.

When using method 2 above, the macro will fire but then seems to fire about a second later for a 2nd time (I have a feeling this is the next refresh of the live data feed as this refresh rate was set to 1 second at the time).

This 2nd method is using the private sub worksheet_calculate and this is housed in the 'race1' sheet (sheet3).

If I cut the live feed and manually enter in cell C2 the time to match one of the schedule times, the macro(private sub worksheet calculate) will execute once as desired.

On this morning’s example the first schedule time 07:27:00 was reached and you can see the double fire evidenced from cell A61, the next time in the schedule 07:58:00 and the double fire again from A97.

I really would appreciate any help on solving this problem.

Many Thanks.
[ATTACH]n1218106[/ATTACH]

*Not that it changes much

First schedule time in this mornings example was 07:57:00 not 07:27:00

I should have pointed out that the formula giving the 1 value used to trigger the sub worksheet calculate code( method 2) is housed in 'race1' Cell A20.

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    If Range("a20").Value > 0 Then Call check1
    Application.EnableEvents = True
End Sub

Hi there and thanks for your reply. The above is the code which I thought was doing this?

Apologies Karim I was just replying to the post above yours when typing.

I will try your proposal and get back to you shortly. Many thanks for your efforts.

Carim, firstly my express apologies at mis spelling your name earlier!

Unfortunately this revision of code ...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$20" Then Exit Sub
  Application.EnableEvents = False
    If Target.Value > 0 Then Call check1
  Application.EnableEvents = True
End Sub

no longer executes the code at all.

Cell A20 is giving the correct value 1 when the times are matched up but it's just not then executing, whereas in the previous code it actually executed twice.

Carim,

Cell A21 in 'race1' is the live time as referenced in 'sheet1' C2 from the API live feed. Cell A20 is a formula that looks to see if this A21 time is matched anywhere in the love schedule on that sheet (Cells D1 to L1). If there is no match Cell A20=0, if a match then A20=1 and the macro should be triggered (sub worksheet calculate).

Obviously in a live feed the time will tick over the matched time on the next 2nd and this value will switch back to zero until another time match occurs. I appreciate you cannot see this but it's what happens.

My earlier code the time would tick on one of the schedule times, Cell A20 would go to 1 and the macro would trigger the copy and paste routine into cell A61 (check1). rather than ending there, a second later the macro would trigger again when Cell A20 had gone back to zero, obviously this shouldn't happen at this point or this is not the desired outcome anyhow!

Kj

I assume the code is firing twice as the copy and paste occurs twice and i get a paste in A61 and then another duplicate below A79.

If i either use the command button instead (method1) as posted earlier the macro will run once as desired so i know the macro check 1 is fine. Also if i disable the API live feed and manually enter into C2(sheet1) a time value that matches one of the schedule times this will also fire the check1 macro once as desired.

The problem I assume from all this must be with the sub work calculate routine whilst the feed is live and I can't see why it wants to execute check1 twice.

My last post is based on the initial code posted not the changes made from Carim's proposal.

Thanks by the way Carim for the heads up about the API code being stored in the wrong place (this workbook).

Kj

re my initial code...

The check1 macro fires perfectly using the command button, each click will execute it once and it will be pasted in the desired location. The paste destination is worked depending on the number of runners in the copy location (sheet1)

pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(15 - i, 0).PasteSpecial xlPasteValues

where

i = Worksheets("race1").Range("c20").Value

The offset value needs to be calculated so that all of these locations are always fixed whatever the amount of runners in any given race, this is absolutely essential as the data in these paste locations are then fed into various parts of the sheet.once the race is over the next step will be to export s5:bz18 into a results book.

I have no experience of using sub worksheet calculate but the problem has to be with this rather than the check1 macro as this works fine when triggered by manual input or the command button.

If you click the Command Button on'race1' at C19 you will see the macro fires and a new paste will have appeared in cell A133. If you got to sheet 1 and take one of the runners say 'Poppy Jay' by deleting A8 and then go back to race1 and click the command button again The next paste will have been performed by check1 from cell a151 down. No matter how the number of runners get changed the data will always be pasted into blocks of 18 starting at A61 for each execution of check1.

So check1 definitely works using the command button but it just doesn't when tryiuse the schedule times via Sub Worksheet Calculate

If you got to sheet 1 and take one of the runners say 'Poppy Jay' by deleting A8

*deleting row 8 on sheet1 containing 'Poppy Jay'

Blimey delete row 11 even containing Poppy Jay

I need a break as I'm clearly a bit fatigued with this so will have a look back later.

Kind Regards.