vba help

[Captain Sensible]

Also add this Sub routine to your module and if your events stop because of some error you can turn them back on by running as a macro, the status of events will show in S1 if you use the code posted above

Code: Select all

Sub Reset()
  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic
  'etc
End Sub

[mak]

Sorry Captain. I was wrong
your code is working
I was not connected correct..
I am just trying out right now!

[Captain Sensible]

Just use the latest code I posted and remember if you want to test manually you just need to change the line

Code: Select all

If Target.Columns.Count <> 16 Then Exit Sub

to

Code: Select all

'If Target.Columns.Count <> 16 Then Exit Sub

That way when you change anything on the sheet the routines will run, just change it back when you're happy things work as expected and connect to Betting assistant.

[mak]

can you give me an example (last think for today) how to find smallest value in f column and highest in h?

[Captain Sensible]

With VBA there are plenty of ways to get the same data if you soley want the values you can convert all the basic excel functions like so the following will give you those values in U1 & 2

Code: Select all

                .Range("U1").Value = Application.WorksheetFunction.Min(.Range("F5:F" & rowFindLast).Value)
                .Range("U2").Value = Application.WorksheetFunction.max(.Range("H5:H" & rowFindLast).Value)

[mak]

thanks!
tomorrow I would like to try to add something like Al code
to move to the next market and alter the FULL-STREAM on off..
what I have noticed until now regarding speed and cpu usage everything is almost identical

[mak]

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.count = 16 Then
Dim BA_Array() As Variant, MyQ2 As Variant
Application.EnableEvents = False
With ThisWorkbook.Sheets(Target.Worksheet.Name)

BA_Array = .Range("A1:BZ55").Value ' this read is probably too much for every update while full stream is on

'If BA_Array(2, 59) <= 520 And Target(2, 6) <> "Closed" Then
'MyQ2 = "FULL-STREAM-ON"
'Else
'MyQ2 = "FULL-STREAM-OFF"
'End If


If Target(2, 5) = "In Play" And Target(2, 6) = "" Then ' is this If statement needed????
MyQ2 = "FULL-STREAM-ON"
' ...........
ElseIf (Target(2, 5) = "In Play" And Target(2, 6) = "Suspended") Or Target(2, 6) = "Closed" Then
If Not marketChanging Then
marketChanging = True
currentMarket = Target(1, 1)
MyQ2 = -1
Else
If Target(1, 1) <> currentMarket Then marketChanging = False
End If
Else
MyQ2 = "FULL-STREAM-OFF"
End If

If triggerInQ2 <> MyQ2 Then
.Range("Q2").Value = MyQ2
triggerInQ2 = MyQ2
End If
' ..........
End With
' ..........
Application.EnableEvents = True
End If

End Sub

[Captain Sensible]

thanks!
tomorrow I would like to try to add something like Al code
to move to the next market and alter the FULL-STREAM on off..
what I have noticed until now regarding speed and cpu usage everything is almost identical

What I usually do is just think of the different scenarios of what'll go in Q2 and then code it from there, you need to avoid overcomplicating your code as there probably are only three options you'll be entering . It's also worth breaking parts up into separate sub routines and put them in a module rather than cramming loads of code into the worksheet_change routine.

Most PC's are very efficient these days, excel really shouldn't be putting much of a strain on it if you're having probelms with the code snippets I've posted it may be something wrong with the PC.

[mak]

I am trying to add some more code which

will move to the next market if race ends
and alter STREAM on and off depending if the market is inplay or not

guess what
nothing works if I add the new statements



If .range("E2") = "In Play" And .range("F2")= ""
MyQ2 = "FULL-STREAM-ON"
' ...........
ElseIf .range("E2") = "In Play" And .range("F2")= "Suspended") Or .range("F2") = "Closed" Then
If Not marketChanging Then
marketChanging = True
currentMarket = Target(1, 1)
MyQ2 = -1
Else
If Target(1, 1) <> currentMarket Then marketChanging = False
End If
Else
MyQ2 = "FULL-STREAM-OFF"
End If

If triggerInQ2 <> MyQ2 Then
.Range("Q2").Value = MyQ2
triggerInQ2 = MyQ2
End If
' ..........
End With

Code: Select all

Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rowFindLast As Long
    Dim readArray() As Variant
    Dim i As Long
    Dim BetUntilOdds As Double
    Dim marketChanging As Boolean, currentMarket As String
    Dim MyQ2 As Variant
   
    Application.EnableEvents = True
        If Target.Columns.Count <> 16 Then Exit Sub
        Application.EnableEvents = False

       
       

          With Target.Parent
           
            rowFindLast = LastRow(.Range("A:AD"), 0) 'get the last row
            readArray = .Range("A1:AD" & rowFindLast).Value 'put our data into an array
           
           
           
            For i = 5 To UBound(readArray, 1) 'using 5 rather than LBound(readArray, 1) because we only want price data now
               

            If GetTicks(CCur(readArray(i, 6)), CCur(readArray(i, 8))) >= 5 _
            And readArray(i, 6) >= 5 And readArray(i, 6) <= 3000 _
            And .Range("E2").Value = "Not In Play" Then
            readArray(i, 19) = 2
            readArray(i, 18) = plusTicks(CCur(readArray(i, 8)), 1) 'display ticks
            readArray(i, 17) = "LAY"
            Else
            If readArray(i, 20) = "CANCELLED" Then
            readArray(i, 19) = "" ' stake
            readArray(i, 18) = "" ' odds
            readArray(i, 17) = "" ' trigger
            readArray(i, 20) = "" ' betref
       
            End If
            End If
           
            Next i
           
           .Range("A1:Z" & rowFindLast).Value = readArray 'dump our amended arrayback to the sheet
         End With
       
       ' End If
        Application.EnableEvents = True
    End Sub


[mak]

the green code is working as I need

[Captain Sensible]

It's a bit too vague saying nothing works, are events showing as TRUE

[mak]

Well, I can not make it run at all!
for start --> I don't know where is should place the code

I tried before and after of
With Target.Parent
but it gives problems (did not note them)

[mak]

the best I did
was to have the code running
before of -- With Target.Parent

but I had to erase all . (periods) before ranges eg .Range("F2") to Range("F2")

but it was not do anything
no bets , no calculations

[Captain Sensible]

Might be an idea to read thru things again so you understand what the coding does and things like why you're putting a period before range rather than just copying and pasting. That way it'll be easier to fix any problems when they crop up.

You use the periods with Range within your With Target.Parent ................End With coding, basically it saves us from having to write out the full cell address Worksheets("Sheet1").Range("A1").Value etc especially if we're working with a few sheets within a workbook, mybets sheet, results sheet etc

The state of your events should be showing in S1 from the code I'd posted so run the Reset macro if that shows as FALSE

[mak]

I think I am close.
However I can not understand the "MyQ2" variable yet

Right now it does everything right, but it moves 2 markets not 1

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rowFindLast As Long
    Dim readArray() As Variant
    Dim i As Long
    Dim BetUntilOdds As Double
    Dim NoRunners As Double
    Dim marketChanging As Boolean
    Dim currentMarket As String
    Dim TriggerInQ2 As Variant
    Dim MyQ2 As Variant
   
   
    Application.EnableEvents = True
        If Target.Columns.Count <> 16 Then Exit Sub
        Application.EnableEvents = False


          With Target.Parent
         
            If .Range("E2").Value = "In Play" And Range("F2").Value = "" Then
            MyQ2 = 0.2
            ElseIf .Range("E2") = "In Play" And .Range("F2") = "Suspended" Or .Range("F2") = "Closed" Then
            '.Range("Q2").Value = 0.5
           
            If Not marketChanging Then
            marketChanging = True
            currentMarket = .Range("A1")
            MyQ2 = -1
            Else
            If .Range("A1") <> currentMarket Then marketChanging = False
            End If
            Else
            MyQ2 = 0.5
            End If

            If TriggerInQ2 <> MyQ2 Then
            .Range("Q2").Value = MyQ2
            TriggerInQ2 = MyQ2
            End If
           
           
            rowFindLast = LastRow(.Range("A:AD"), 0) 'get the last row
            readArray = .Range("A1:AD" & rowFindLast).Value 'put our data into an array
           
            For i = 5 To UBound(readArray, 1) 'using 5 rather than LBound(readArray, 1) because we only want price data now
               

            If GetTicks(CCur(readArray(i, 6)), CCur(readArray(i, 8))) <= 5 _
            And readArray(i, 6) >= 2 And readArray(i, 6) <= 4 _
            And .Range("E2").Value = "In Play" _
            And readArray(i, 20) = "" _
            And readArray(i, 26) = "N" _
            And BetUntilOdds >= 2 Then
            readArray(i, 19) = 0.2
            readArray(i, 18) = plusTicks(CCur(readArray(i, 8)), 1) 'display ticks
            readArray(i, 17) = "LAY"
            Else
           
            readArray(i, 19) = "" ' stake
            readArray(i, 18) = "" ' odds
            readArray(i, 17) = "" ' trigger
            readArray(i, 20) = "" ' betref
           
       
            End If
            End If
           
            Next i
           
           .Range("A1:Z" & rowFindLast).Value = readArray 'dump our amended arrayback to the sheet
         End With
       
       ' End If
        Application.EnableEvents = True

End Sub