Sub Routines ??

[excelhasey]

Evening all the following code records your balance before each race and works perfectly :-

Option Explicit

Dim currentMarket As String

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
If [A1] <> currentMarket Then
currentMarket = [A1]
logBalance
End If
End If
End Sub

Private Sub logBalance()
Application.EnableEvents = False
Dim r As Integer
r = 2
While Sheet2.Cells(r, 1) <> ""
r = r + 1
Wend
Sheet2.Cells(r, 1) = currentMarket
Sheet2.Cells(r, 2) = [I2]
Application.EnableEvents = True
End Sub

The following captures (copies) certain data when the Market goes In Play and also worked perfectly until I decided to add another tab sheet called balance and tried to integrate the above code but it does not record the balance before each race - What have I done wrong ??

Option Explicit

Dim inPlay As Boolean
Dim inPlayTime As Date
Dim oddsCaptured As Boolean
Dim currentMarket As String

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If [E2] = "In Play" Then
If Not inPlay Then
inPlay = True
inPlayTime = Now
End If
[T1] = DateDiff("s", inPlayTime, Now)
Else
If inPlay Then
inPlay = False
[T1] = ""
End If
End If
If [A1] <> currentMarket Then
oddsCaptured = False
Range("BI5:BI44") = ""
Range("BO5:BO44") = ""
End If
currentMarket = [A1]
If [T1] <= 0 And [E2] = "In Play" And Not oddsCaptured Then
oddsCaptured = True
Range("F5:F44").Copy Range("BI5:BI44")
Range("P5:P44").Copy Range("BO5:BO44")
End If
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
If [A1] <> currentMarket Then
currentMarket = [A1]
logBalance
End If
End If
End Sub

Private Sub logBalance()
Application.EnableEvents = False
Dim r As Integer
r = 2
While Sheet2.Cells(r, 1) <> ""
r = r + 1
Wend
Sheet2.Cells(r, 1) = currentMarket
Sheet2.Cells(r, 2) = [I2]
Application.EnableEvents = True
End Sub

[alrodopial]

Your variable "currentMARKET" is set to A1 from the first Worksheet_Change and in the second Worksheet_Change is already equal to A1, thats why it doesnt fire the logBalance.
change ie the first currentMARKET to currentMARKET1 and the second to currentMARKET2

by the way what "wend" does?

[excelhasey]

Thanks alrodopial I will try that later on today

Its not my code its Gary Russell's and I am no VBA expert but I am assuming Wend = While end ?? - As the previous statement was

While Sheet2.Cells(r, 1) <> ""
r = r + 1

But that's my amateur opinion

[excelhasey]

I have amended the code to read currentmarket1 and currentmarket2 but now VBA says I have a :-

compile error ambiguous name detected worksheet_change

and it highlights the 2nd Private Sub Worksheet_Change(ByVal Target As Range) statement

But if I delete this line I get a compile error variable not defined and it highlights currentmarket1

So what is VBA looking for ??

[alrodopial]

Try the below - not tested - :

Option Explicit

Dim inPlay As Boolean
Dim inPlayTime As Date
Dim oddsCaptured As Boolean
Dim currentMarket As String

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

If [E2] = "In Play" Then
If Not inPlay Then
inPlay = True
inPlayTime = Now
End If
[T1] = DateDiff("s", inPlayTime, Now)
Else
If inPlay Then
inPlay = False
[T1] = ""
End If
End If
If [A1] <> currentMarket Then
oddsCaptured = False
Range("BI5:BI44") = ""
Range("BO5:BO44") = ""
End If
currentMarket = [A1]
If [T1] <= 0 And [E2] = "In Play" And Not oddsCaptured Then
oddsCaptured = True
Range("F5:F44").Copy Range("BI5:BI44")
Range("P5:P44").Copy Range("BO5:BO44")
End If

End If
Application.EnableEvents = True
End If
End Sub

Private Sub logBalance()
Application.EnableEvents = False
Dim r As Integer
r = 2
While Sheet2.Cells(r, 1) <> ""
r = r + 1
Wend
Sheet2.Cells(r, 1) = [A1]
Sheet2.Cells(r, 2) = [I2]
Application.EnableEvents = True
End Sub

[excelhasey]

Wow ..............

That works many many thanks I have been trying to work that out for weeks !!!!!

Any advice you can give me as to your thought process of why my code wasn't performing and why yours did ??

[alrodopial]

You can only have one worksheet change event per sheet, and one global one in the workbook.
I deleted the "currentMarket = [A1]" from the first subroutine and left it in the second one because I inserted the second subroutine inside the first one

[alrodopial]

I have been trying to work that out for weeks !!!!!

No need for such a long searching time, do your search in a logical period and if need it post it here, os will give a solution to your question for sure, others will also help if they can

[osknows]

One other tip, where you use soemthing like currentMarket = [A1], [] is a shortcut for the function Evaluate so is exactly the same as currentMarket = Evaluate("A1")

The evaluate function is more than 5 time slower than using a fully quilified range, ie currentMarket = Sheet1.Range("A1").Value

You can test the speed differences with the following

Code: Select all

Sub test()
Dim a As Variant
Dim i As Long

t = Timer
For j = 1 To 50000
        a = Sheet1.Range("A1").Value
Next j
Debug.Print "1: ", Timer - t

t = Timer
With Sheet1
For j = 1 To 50000
        a = .Range("A1").Value
Next j
End With
Debug.Print "2: ", Timer - t

t = Timer
For j = 1 To 50000
        a = [A1]
Next j
Debug.Print "3: ", Timer - t

t = Timer
For j = 1 To 50000
        a = Evaluate("A1")
Next j
Debug.Print "4: ", Timer - t


End Sub