VBA help again

[Ilena]

Hi,
With Osknows and others help here was able to get a combination of code to track highest and lowest prices from SP and first bet. I've now tried to include Gary's greenup opposing odds code. I can now track the greenup piece, but have lost the highest lowest prices piece. I can't see where the problem is.
The opposing odds spreadsheet inserts an extra column, but I've taken account of that so it must be in the VBA, which isn't my strong point. I've "tried" to combine these into sheet 1, any extra module and MyBets code is per Gary's sample spreadsheet.

Option Explicit

Dim currentMarket As String
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
Application.EnableEvents = False
If currentMarket <> [A1] Then getNumberOfRunners
currentMarket = [A1]
calculateGreenUpStakes
Application.EnableEvents = True
End If
End Sub

Private Sub getNumberOfRunners()
Dim r As Integer
r = 5
nrRunners = 0
Do
nrRunners = nrRunners + 1
r = r + 1
Loop Until Cells(r, 1) = ""
End Sub

Private Sub getPrices()
Dim iRow As Integer
Dim iCol As Integer
Dim cell As Object


For Each cell In Target
iRow = cell.Row
iCol = cell.Column

' Update Max and Min Matched Values from start of inplay
If Cells(2, 5) = "In Play" And iCol = 15 And iRow > 4 And iRow < 7 Then
Application.EnableEvents = False
If IsEmpty(Range("AK" & iRow).Value) Then Range("AK" & iRow).Value = 0
If IsEmpty(Range("AJ" & iRow).Value) Then Range("AJ" & iRow).Value = 1001
If IsEmpty(Range("AI" & iRow).Value) Then Range("AJ" & iRow).Value = Range("O" & iRow).Value
If Not IsEmpty(cell.Value) Then
Range("AH" & iRow).Value = Range("O" & iRow).Value
If cell.Value < Range("AJ" & iRow).Value And cell.Value > 1 Then Range("AJ" & iRow).Value = cell.Value
If cell.Value > Range("AK" & iRow).Value And cell.Value < 1001 Then Range("AK" & iRow).Value = cell.Value
End If
Application.EnableEvents = True
End If

' Update Max and Min Matched Values from first bet
If Cells(4, 38) = "|" And iCol = 15 And iRow > 4 And iRow < 7 Then
Application.EnableEvents = False
If IsEmpty(Range("AG" & iRow).Value) Then Range("AG" & iRow).Value = 0
If IsEmpty(Range("AF" & iRow).Value) Then Range("AF" & iRow).Value = 1001
If IsEmpty(Range("AE" & iRow).Value) Then Range("AE" & iRow).Value = Range("O" & iRow).Value
If Not IsEmpty(cell.Value) Then
Range("AD" & iRow).Value = Range("O" & iRow).Value
If cell.Value < Range("AF" & iRow).Value And cell.Value > 1 Then Range("AF" & iRow).Value = cell.Value
If cell.Value > Range("AG" & iRow).Value And cell.Value < 1001 Then Range("AG" & iRow).Value = cell.Value
End If
Application.EnableEvents = True
End If

Next cell
End Sub


Any help would be most appreciated. Sorry, I guess an 8 italic = "Cool".

Many Thanks and Kind Regards,
Ilena

[osknows]

Hello,

Private Sub Worksheet_Change(ByVal Target As Range) is the change event for the sheet and is triggered when a cell(s) changes on the sheet.

You have 2 other subroutines
Private Sub getNumberOfRunners()
Private Sub getPrices()

The Private Sub getNumberOfRunners() is called from the Worksheet_Change event using If currentMarket <> [A1] Then getNumberOfRunners

However, I can't see a call to getPrices...

Is that the problem?

Just change to

Code: Select all

Option Explicit

Dim currentMarket As String
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
Application.EnableEvents = False
If currentMarket <> [A1] Then getNumberOfRunners

getPrices

currentMarket = [A1]
calculateGreenUpStakes
Application.EnableEvents = True
End If
End Sub


[Ilena]

Hi osknows,
Many Thanks for your time again. That may be the problem. The script that begins Private Sub getPrices() is the procedure to track the highest and lowest prices which you kindly helped out with previously, but I've blindly copied it after Gary's green up code without entering the additional string line. I can see that now (a little light, albeit dim, has just come on).
I'll enter the line and see if it does it.

Many, Many Thanks,
Ilena

[osknows]

Also, I've just noticed that you have

Application.EnableEvents = False
Application.EnableEvents = True

in more than one sub routine.

Remove it from everything except the Worksheet_Change Event otherwise when you call a routine it will return to the calling routine with Events = True which you definitely don't want

[Ilena]

Thanks osknows.
I'd removed them, but I've been struggling with the
"For Each cell in Target" line as that Private_Sub was a Worksheet_Change event in its own right before trying to combine them. I seem to have to need that line the way it's set up, but I can't get the syntax. I'm thinking it should be something like = Worksheets("Sheet1").Cells if I move it to a module as a public sub, but nothing seems to work. This is beyond me

[osknows]

How about

Code: Select all

Option Explicit

Dim currentMarket As String
Dim nrRunners As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Integer
Dim iCol As Integer
Dim cell As Object

If Target.Columns.Count = 16 Then
Application.EnableEvents = False
If currentMarket <> [A1] Then getNumberOfRunners
currentMarket = [A1]

'=====

For Each cell In Target
    iRow = cell.Row
    iCol = cell.Column
   
    ' Update Max and Min Matched Values from start of inplay
    If Cells(2, 5) = "In Play" And iCol = 15 And iRow > 4 And iRow < 7 Then
   
    If IsEmpty(Range("AK" & iRow).Value) Then Range("AK" & iRow).Value = 0
    If IsEmpty(Range("AJ" & iRow).Value) Then Range("AJ" & iRow).Value = 1001
    If IsEmpty(Range("AI" & iRow).Value) Then Range("AJ" & iRow).Value = Range("O" & iRow).Value
    If Not IsEmpty(cell.Value) Then
    Range("AH" & iRow).Value = Range("O" & iRow).Value
    If cell.Value < Range("AJ" & iRow).Value And cell.Value > 1 Then Range("AJ" & iRow).Value = cell.Value
    If cell.Value > Range("AK" & iRow).Value And cell.Value < 1001 Then Range("AK" & iRow).Value = cell.Value
    End If
   
    End If
   
    ' Update Max and Min Matched Values from first bet
    If Cells(4, 3) = "|" And iCol = 15 And iRow > 4 And iRow < 7 Then
   
    If IsEmpty(Range("AG" & iRow).Value) Then Range("AG" & iRow).Value = 0
    If IsEmpty(Range("AF" & iRow).Value) Then Range("AF" & iRow).Value = 1001
    If IsEmpty(Range("AE" & iRow).Value) Then Range("AE" & iRow).Value = Range("O" & iRow).Value
    If Not IsEmpty(cell.Value) Then
    Range("AD" & iRow).Value = Range("O" & iRow).Value
    If cell.Value < Range("AF" & iRow).Value And cell.Value > 1 Then Range("AF" & iRow).Value = cell.Value
    If cell.Value > Range("AG" & iRow).Value And cell.Value < 1001 Then Range("AG" & iRow).Value = cell.Value
    End If
   
    End If

Next cell
'====

calculateGreenUpStakes
Application.EnableEvents = True
End If
End Sub

Private Sub getNumberOfRunners()
Dim r As Integer
r = 5
nrRunners = 0
Do
nrRunners = nrRunners + 1
r = r + 1
Loop Until Cells(r, 1) = ""
End Sub


[Ilena]

osknows.
You're a Complete Star! Removed the third line "Dim nrRunners As Integer" and it seems to be working fine.
I need to work through the sequencing to get my head around how all this works. Loads to learn.

Many, Many, Many Thanks,

Kindest Regards,
Ilena