Load next market skipping

[PennyFiddler]

Hello

This is the final piece to my bot, and having looked at Gary's example code (to my limited experience) I can only see that he is putting '-1' into Q2.

I dont have a problem finding a way of putting '-1' in Q2.
My problem is that it either stays in too long and skips a market, or it gets cleared too quickly and not read.

My process is to change market when the market becomes closed, seems perfectly rational.

If Range("F2").Value = "Closed" Then Range("Q2").Value = "-1"

At which point it skips the very next line that says

If Range("A5").Value <> Range("Y1").Value Then Range("Q2").Value = "3" (Y1 containing the previous market's A5 selection, that gets updated next, and the refresh rate gets set to 3s)

Is there a way of breaking after the first command and the second?

TIA

[PennyFiddler]

Just found this from Capt.Sensible further into the help sections (with my adaptation with 'Closed' ) see if this works.
(No clue as to these statics, nor how SwitchMarket originates as false, but I dont much care if it works)



Static MyMarket As Variant
Static SwitchMarket As Boolean

If .Range("F2").Value = "Closed" And SwitchMarket = False Then .Range("Q2").Value = -1:SwitchMarket = True

If .Range("A1").Value <> MyMarket Then
MyMarket =.Range("A1").Value
SwitchMarket = False
End If

[GaryRussell]

Presumably your code is in the Worksheet_Change event.

When you change a cell in the worksheet you are triggering another event so it re-enters the change event which gets very confusing. To avoid this use Application.EnableEvents = False

eg.

Code: Select all

Application.EnableEvents = False
If Range("F2").Value = "Closed" Then Range("Q2").Value = "-1"
If Range("A5").Value <> Range("Y1").Value Then Range("Q2").Value = "3"
Application.EnableEvents = True


Also you only want it to insert -1 in Q2 once so use a flag to block this.

eg.

Code: Select all

If Range("F2").Value = "Closed" Then
   If not marketChanged Then
      marketChanged = True
      Application.EnableEvents = False
      Range("Q2").Value = "-1"
      If Range("A5").Value <> Range("Y1").Value Then Range("Q2").Value = "3"
      Application.EnableEvents = True
   End If
Else
   marketChanged = False
End If


marketChanged should be declared as a global variable at the top of your code outside of any subs or functions with Dim marketChanged as Boolean

[PennyFiddler]

Yes, ok Thanks Gary.
I did implement the adapted CS code above, which worked…perhaps I should have mentioned.

I will change it over to your version though just in case.

I’m very slowly grasping the idea.

‘If not marketChanged then
marketChanged = true’

Just seems so un wrong

(That’s no a question btw, I’ll get my head around it)
Thanks.

[alrodopial]

‘If not marketChanged then’

means : if the variable "marketChanged" is not true
(it's the same as: If marketChanged<>True"

so the code does: if the flag is not true change it into true