Can anyone help!??
Trying to automate a ditching strategy using excel.
I need odds to be captured & copied form one part of my workbook to another. The copy is triggered when match timer hits a certain point. - which changes a cell value form 0 to 1.
I currently use worksheet_calculate to fire on the formula calculation, then use an IF statement to check if the value is 1. If it is - calls the copy sub.
But the problem is the worksheet is constantly firing due to the number of other formulas I have.
I thought a simple fix would be to use the worksheet_change event - like many people seem to in this forum - but that doesn't seem to work with formula changes - only manual inputs?
Can anyone explain how to use it in automation?
How do you actually use Worksheet_Change event?
From something I used a while ago, cant be arsed to shorten it or explain it but works fine for me
EMPTY_STRING is a global variable which is simply
Public Const EMPTY_STRING As String = ""
EMPTY_STRING is a global variable which is simply
Public Const EMPTY_STRING As String = ""
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
If IsNumeric(Me.Range("F4")) Then
gPriceUpdate "BA Data - 5", Me.Range("F4"), Me
End If
End Sub
Code: Select all
Public Sub gPriceUpdate(ByVal pstrSheet As String, _
ByVal pdteCountdown As Date, _
ByRef pobjParent As Excel.Worksheet)
Dim objWs As Excel.Worksheet
Dim lngRow As Long
Dim n As Long
Const LTP_ROW As Long = 15
Set objWs = ThisWorkbook.Worksheets(pstrSheet)
' If SET_SVs are set and outside of time range then clear. The first row will have a value populated so can check that
If pobjParent.Cells(9, 12) <> EMPTY_STRING Then
If pdteCountdown < TimeSerial(0, 2, 50) Or pdteCountdown > TimeSerial(0, 3, 0) Then
mClearRuleCells pobjParent
End If
End If
' If time is in range then set the values
If pdteCountdown > TimeSerial(0, 2, 55) And pdteCountdown <= TimeSerial(0, 3, 0) Then
If pobjParent.Cells(9, 12) = EMPTY_STRING Then
mTriggerBet pobjParent
End If
End If
' Time is in capture range, find the associated time and make sure data is empty
If pdteCountdown <= TimeSerial(0, 10, 10) And pdteCountdown >= TimeSerial(0, 1, 0) Then
lngRow = objWs.Range("AF2").Value
If lngRow > 0 Then
' Empty - populate all selections with the LTP
If objWs.Cells(lngRow, 2) = EMPTY_STRING Then
Application.EnableEvents = False
For n = 2 To 29 Step 3
If Not IsError(objWs.Cells(LTP_ROW, n)) Then
objWs.Cells(lngRow, n) = objWs.Cells(LTP_ROW, n)
End If
Next n
Application.EnableEvents = True
End If
End If
End If
' Does the associated data worksheet need to have its data cleaned
If (pdteCountdown < TimeSerial(0, 1, 0) And Application.WorksheetFunction.CountA(objWs.Range("B18:B628")) > 0) Or _
(pdteCountdown > TimeSerial(0, 10, 11) And Application.WorksheetFunction.CountA(objWs.Range("B18:B628")) > 0) Then
Application.EnableEvents = False
For n = 2 To 29 Step 3
With objWs
.Range(.Cells(18, n), .Cells(628, n)).ClearContents
End With
Next n
Application.EnableEvents = True
End If
End Sub