Macro VBA help

[aubweaver]

Thanks Steve and Osknows for your help.
I'll try your suggestions in a while but to answer your (Steves) Q'.
What I want to achieve is not exactly what is shown in that macro, as I said that macro was forwarded onto me but as I couldn't get things to work I wasn't at this stage trying to add my requirements, I was going to try to change it later.
What I ultimately wanted to achieve was ,I think, fairly basic.
I was going to set up a basic standard excel 'IF' 'AND' etc formula in say cell range AJ5:AJ35 to enter a 'flag' (maybe "X') if certain criteria was met, ie SP and 'F' Col. odds, that is the simple bit.
I would then refer to that "X" flag in AJ5:AJ35 (the SP would be at the lower end -3 or -4, so therefore the range could be minimal). Anyway if the "X" appeared the macro would be triggered and would simply put a 'flag'... "W" into the adjacent cell. I would then refer to that "W" to trigger bets accordingly. The reason for using the macro is the "W" remains in the cell(s) regardless of odds changes until being cleared, at or before the start of the next race.
The whole thing has to automated other than for just speed/concentration, as I only bet on the UK mkt. and being in Oz there are obvious time constraints. (I'll be in the UK in two weeks and maybe I can then fully test things in real time).
Having the macro fire and ultimately clear the flags was the issue, as Mark earlier said I needed to tie it into a BA refresh/event change.
I have in the interim come up with a possible solution, if I cannot tie it in to a BA event change I can create my own event change. I have achieved this by using an auto mouse clicker/auto text input which repeats in M'Secs (if necessary) throughout the duration of all the races. This acts the same as if I was sitting by the keyboard and entering a single text and clicking on another cell....'the macro then fires'.
Although this can be a 'saved' setup it still means I have to organise it at the start of the racing to run with all the other automated tasks. Therefore a solution to the macro firing all within the system is still the preferable option.
Many Thanks again for your help,
Peter

[aubweaver]

In answer to your Q Osknows. The macro is in the sheet, although I have tried it in a module as well. Also as mentioned when I shut down my computer and reopened everthing it 'fired'. Not every part of it worked but I was going to address that later. However if I simply cleared the excel sheet logged out of BA and re logged in, it still didn't work, I had to shut the system down which I found a bit strange.
Thanks Peter.

[osknows]

The code you posted up will fail on 'If Range("F3:F40") >= 4 Then '. Application.EnableEvents = false turns off events and because your code fails before events are turned back on then the code will only ever run once.

You can turn events on by entering Application.EnableEvents = true in the Immediate window of the VBA editor and press enter.

Below is an update of your code that should go in the SHEET object of the sheet you are working in (not a module)

Code: Select all

Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
Application.EnableEvents = False

    With ThisWorkbook.Sheets(Target.Worksheet.Name)
   
        ' Check if the race has just changed and clear any old flags
        If CurrentRace <> .Range("A1").Value Then
            .Range("BC5:BD35").ClearContents
        End If
       
        ' Check if a flag must be placed for any BACK bet
        ' Have assumed your checking if any value in F3:F40 is >=4 ??
        If WorksheetFunction.Max(.Range("F3:F40")) >= 4 Then
            .Range("AH5") = "X"
        End If
       
        ' Check if a flag must be placed for any LAY bet
        If .Range("AG3") >= 1 Then
            For x = 5 To .Cells(.Rows.Count, "A").End(xlUp).Row
                If .Range("Q" & x) = "LAY" Then .Range("BD" & x) = "Y"
            Next x
        End If
       
        CurrentRace = .Range("A1").Value
    End With
Application.EnableEvents = True
End If

End Sub


[aubweaver]

Thankyou Osknows,
I'll try to look at the details you attached and test it this weekend.
I did manage to build a macro with the auto mouse/typer thing that I mentioned previously. I'll try to integrate it with the info that you sent and hopefully cancel the need to use the auto mouse stuff.
Thanks again for all your help.
Peter

[aubweaver]

Hi,
Thanks Osknows,(and Steve) I did manage to play around with the code you sent.
I'm still having the odd problems. There were parts of the original code that I didn't need which I have deleted, I had tested it initially and it basically worked, that is it cleared the cells when it opened. It also worked with the deletion. However it immediately refilled the cells whether the range F5:F40 were above or below '4'., even with the single ref. of F5 being below 4 ??
I have added/changed something more in line with my requirements and also made a couple of cell reference changes. I'll paste below the changes I've made and then explain the current issues I'm having.

Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
Application.EnableEvents = False

With ThisWorkbook.Sheets(Target.Worksheet.Name)

' Check if the race has just changed and clear any old flags
If CurrentRace <> .Range("A1").Value Then
.Range("AK5:AK35").ClearContents
End If

' Check if a flag must be placed for any BACK bet
' Have assumed your checking if any value in F3:F40 is >=4 ??
For Each Cell In Range("AJ5:AJ45")
If Cell = "X" Then
Range("AK5:AK40") = "W"
End If


CurrentRace = .Range("A1").Value
End With
Application.EnableEvents = True
End If

End Sub


OK there are errors within my changes but basically what I'm trying to acheive is after the the first main section of 'ClearContents' I want to refer to AJ5:AJ45, where if there's an"X" in any one of those cells I want the Macro to put a "W" into the adjacent AK cell. (The 'AJ' col. will be referring to the odds in the "F" column). I had actually acheived this earlier using a 'SelectCase' set up but I couldn't integrate into your code , (my lack of knowledge). The above macro won't work at the moment because I'm getting a 'CompileError' "End With Without With".?
The other problem that I still seem to be getting is when testing it in it's unchanged set up is it only appears to fully function after restarting the PC, it will continue to work but if I make any changes I have to shut the system down to retest it even if I put the macro back to it original state. I'm not sure how to test run a Private_Sub macro.
I realise there may be other errors in the changes I've made like will a 'W' go into individual cells or fill the complete range..?? .

I have pasted below the macro that I did build , (it kind of evolved) but it essentially worked but needed 'change events' for it to fire. The auto mouse clicker worked but is a pain in the proverbial, as you can't use the PC unless you stop it. Don't read too much into it as it was one I was messing around with to test it, although the 'SelectCase' part is pretty well correct. The -10 bit was referring to the countown timer to act the same as 'ClearContents' ie it simply changed the 'letter' and it all worked.



Sub auto_openG()


' Run the macro DidCellsChange any time a entry is made in a
' cell in WinMkt-3BACK (3).
ThisWorkbook.Worksheets("WinMkt-3BACK (4)").OnEntry = "DidCellsChangeJJ"

End Sub

Sub DidCellsChangeJJ()

' If time is -10 enter "O"
For Each Cell In Range("AK5:AK10")
If Cell = "W" Then
Call KeyCellsChangedO
End If
If Cell = "O" Then
Call KeyCellsChangedO

End If
Next Cell
End Sub


Sub KeyCellsChangedO()
' Macro2 Macro

For Each Cell In Range("AL5")
If Cell = "NO" Then
Range("AK5:AK10") = "O"
End If
If Cell = "YES" Then

Call KeyCellsChangedJ
End If
Next Cell
End Sub

Sub KeyCellsChangedJ()
' Dim Cell As Object
' If the values in AI5:AI45 are less than 2.6...
For Each Cell In Range("AJ5:AJ10")
If Cell = "X" Then
Call TheSelectCase11
Call TheSelectCase12
Call TheSelectCase13
Call TheSelectCase14
Call TheSelectCase15
Call TheSelectCase16
End If
Next Cell
End Sub


Sub TheSelectCase11()

Select Case Range("AJ5").Value

Case "X"

Range("AK5").Value = "W"

End Select

End Sub
Sub TheSelectCase12()


Select Case Range("AJ6").Value

Case "X"

Range("AK6").Value = "W"


End Select

End Sub


Sub TheSelectCase13()


Select Case Range("AJ7").Value

Case "X"

Range("AK7").Value = "W"


End Select

End Sub
Sub TheSelectCase14()


Select Case Range("AJ8").Value

Case "X"

Range("AK8").Value = "W"


End Select

End Sub
Sub TheSelectCase15()


Select Case Range("AJ9").Value

Case "X"

Range("AK9").Value = "W"


End Select

End Sub
Sub TheSelectCase16()


Select Case Range("AJ10").Value

Case "X"

Range("AK10").Value = "W"


End Select

End Sub


Well thats it in more than a nutshell, If you or anybody can assist it would be appreciated in the meantime I'll keep bashing away.
Thanks Peter

[steven]

Hi Peter,

I have read through your post a couple of times and I think this is what you are trying to achieve.



Dim CurrentRace As String

Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Columns.Count = 16 Then
If CurrentRace <> Range("A1").Value Then Range("AK5:AK45").ClearContents
'checks if its a new race
For A = 5 To 45
'this is a count for the row numbers you want to check
If Cells(A, 36).Value = "X" Then Cells(A, 37).Value = "W"
'The numbers 36 & 37 are columns AJ & AK
Next
CurrentRace = Range("A1").Value
End If
Application.EnableEvents = True
End Sub


It doesn't do a lot but hopefully will nudge you along, this event will fire everytime there is a change in your worksheet, but will only do anything when information is changed in 16 columns at the same time IE. when the prices are updated. It will then go through a loop checking if there is an "X" in column AJ and if there is then it will put "W" in the same row in column AK. As long as your formulas in column AJ no longer show an "X" when the race changes then this should work. If your code has an error when you are changing things and it stops working you will need to type "Application.EnableEvents = True" into the immediate window in the VB editor and press enter, this should get it going again, but if the error is still in the code then this will happen every second.

If you want to test this without using live information from betting assistant then add a few comment marks as follows


Dim CurrentRace As String

Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'If Target.Columns.Count = 16 Then
If CurrentRace <> Range("A1").Value Then Range("AK5:AK45").ClearContents
'checks if its a new race
For A = 5 To 45
'this is a count for the row numbers you want to check
If Cells(A, 36).Value = "X" Then Cells(A, 37).Value = "W"
'The numbers 36 & 37 are columns AJ & AK
Next
CurrentRace = Range("A1").Value
'End If
Application.EnableEvents = True
End Sub


This way you can type in information in one cell at a time just to check it works and by changing the value in cell "A1" it should clear column "AK". Then once you are happy with how it performs you can remove the comment marks and use the spread sheet. You raised a lot of other points, so if you still have questions please let me know.


Regards
Steve

[aubweaver]

Thanks Steve,
I'll test the macro as soon as I can log excel again, for some strange reason none of my open workbooks are being found by BA. I've posted a Q and will wait to see what the problem might be.
I'll keep you informed.
Many Thanks,
Peter.

[aubweaver]

Thanks Steve,(and Osknows),
All looks pretty good now. The BA issue with excel not loading has resolved itself...'The mind boggles."
Anyway I was able to simulate and test the macro and all worked. The real test will be when the actual racing starts but I cannot see any reason why it shouldn't work.
So I thank you and the forum for all the help.
Thanks,
Peter