Browsing Markets

[negapo]

Hi,
Can anyone give me a little help. I don't understand anything about VBA and i was trying to browse my quick pick list trying to find certain conditions. (i have to do this in VBA because after a "-1" in cell Q2 B.A. clean all values in that cell).

I have ten worksheets with this code in each one

Private Sub Worksheet_Calculate()
If Range("B16").Value <> "0" Then
Range("Q2").Select
ActiveCell.FormulaR1C1 = "-1"
Else
Range("Q2").FormulaR1C1 = "0.1"
End If
End Sub

If the market conditions are met cell B16 as the value 0.
If only one sheet is logging everything is OK but if i run more than one worksheet i get an error:
"Select Method of Range class failed"

Much appreciate for any help.

[dgs2001]

negapo

I'm no expert but the following may help.

Firstly we don't need to select a cell (range) to work on it so E.G.

Code: Select all

Range("Q2").Select
ActiveCell.FormulaR1C1 = "-1"


Becomes

Code: Select all

Range("Q2")="-1"

Secondly we need to stop anything else being done to your sheets while this code is running so in each sheet just after

Code: Select all

Private Sub Worksheet_Calculate()

we put in

Code: Select all

Application.EnableEvents = false

and just before

Code: Select all

End Sub

we put

Code: Select all

Application.EnableEvents = True

So the complete code becomes

Code: Select all


Private Sub Worksheet_Calculate()

Application.EnableEvents = False

If Range("B16").Value <> "0" Then
Range("Q2")= "-1"
Else
Range("Q2") = "0.1"
End If

Application.EnableEvents = True
End Sub


Try this it might work it might not!!

Duncan

[negapo]

Duncan
Thank you very much for the code and the explanation.
It works fine, now i just have a problem with the synchronization because B.A. jumps two markets without checking the validation of my market conditions, he is two fast

By the way, i will use this topic for something related. Does anyone has a strategy for B.A. to loop the quick pick list? I'm searching for market conditions and when it reaches the final market of the list it stops. I have to find a simple way to start the "-2" in cell Q2 when that happens.

Edit:
If the code is insert in
Private Sub Worksheet_Change(ByVal Target As Range)

it works fine

Thank you again

[jokerjoe]

This is a bit written in a bit of a rush, but I wait for at least a couple of refreshes of a new market before actioning any code, I've posted about this somewhere. Key is to maintain a refresh counter, search my posts.

edit: http://gruss-software.co.uk/forum/viewtopic.php?p=16247&highlight=#16247

hope it helps

[negapo]

jokerjoe
Thanks a lot for sharing. I think that is the best idea to run different code on spreadsheets with delay but I'm having a hard time to adapt it (very very newbie). Your code adjusted to my problem:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Private MyMarket As String
Private RefreshCount As Long
            If Range("A1").Value = MyMarket Then
                RefreshCount = RefreshCount + 1
            Else
                MyMarket = Range("A1").Value
                RefreshCount = 1
            End If
If RefreshCount > 20 Then

If Range("B17").Value <> "0" Then
Range("Q2") = "-1"
End If
End If
End Sub


Was trying to run the last if just when the current market had been updated at least 20 times. Thanks in advance for any tip.
Edit: My problem is that nothing happens.

[dgs2001]

Code: Select all

Dim MyMarket as string


This needs to be at the very top above

Private Sub Worksheet_Change(ByVal Target As Range)


Try that

Duncan

[negapo]

Duncan

Thank you for your suggestion. It was that. I was defining the variable inside the sub so every time the end sub came it cleaned the variables MyMarket and RefreshCount values. I have puted in the top as you said, under the "general" part of the code sheet.
Everything is ok now. I managed to browse the markets and when it reaches the end of the quick list i put a -5 in Q2 to go to the first one of the list. I made this by storing the value of the name of the market and when its the same after a few refreshes it assumes its on the end o the quick pick list.

The only problem i have is that B.A. seems to handle badly the constant loading of markets (i have ten spreadsheets browsing) and brings a message box saying: "Failed to load market, please try again."
Does anyone has this problem?