Visual Basic Question

[sagar]

Is there a way to modify this code (originally written by Captain Sensible, see below) to make it work when the value of a cell changes as a result of an Excel formula? At the moment it works only when a data is typed in the spreadsheet or when is imported from BA. Can anyone help me?


Private Sub Worksheet_Change(ByVal Target As Range)
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 Back Values
If iCol = 25 And iRow > 4 And iRow < 500 Then

Application.EnableEvents = False
If IsEmpty(Range("AJ" & iRow).Value) Then Range("AJ" & iRow).Value = 0
If IsEmpty(Range("AI" & iRow).Value) Then Range("AI" & iRow).Value = 1001
If IsEmpty(Range("AH" & iRow).Value) Then Range("AH" & iRow).Value = Range("O" & iRow).Value

If Not IsEmpty(cell.Value) Then
Range("AG" & iRow).Value = Range("O" & iRow).Value
If cell.Value < Range("AI" & iRow).Value And cell.Value > 1 Then Range("AI" & iRow).Value = cell.Value
If cell.Value > Range("AJ" & iRow).Value And cell.Value < 1001 Then Range("AJ" & iRow).Value = cell.Value
End If

Application.EnableEvents = True
End If



Next cell

End Sub

[osknows]

think of the logic as

1. read the current data
2. compare to previous data
3. Run additional code if based on output comparison
4. write current data into previous data cells

In excel create an area to record the previous cell value you want to compare. In the VBA read the current value, compare with previous value in excel, run code if required, write current value to previous value in excel....repeat

[sagar]

Hi, this is exactly what the code does. The problem is that it works perfectly with imported data but not with cells containing Excel formulas. There must be something to change in the code (probably only a couple of lines!) but I can't find it.

think of the logic as

1. read the current data
2. compare to previous data
3. Run additional code if based on output comparison
4. write current data into previous data cells

In excel create an area to record the previous cell value you want to compare. In the VBA read the current value, compare with previous value in excel, run code if required, write current value to previous value in excel....repeat

[GaryRussell]

Cells changed by formulas do not fire the Worksheet_Change event. The formula is based on another cell changing, if that cell is also based on a formula then you need to trace it back to the cell that is actually being written, either by manual input or Betting Assistant writing data. Your code should check for this cell or range of cells (eg. prices) being changed in the Worksheet_Change event and then check if the cell containing your formula has a different value from the previous update.

[osknows]

The code below monitors cell A8 and allows code to be run if value of cell changes. As Gary set earlier the formula in A8 must be updated from other cells changing to trigger the worksheet_change event

Code: Select all

Option Explicit
'Create variable to hold values
Dim Monitored

Private Sub Worksheet_Activate()
    Monitored = Range("E8").Value       'Read in value prior to any changes
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
   
   
    'Compare monitored cell with initial value
    If Range("E8").Value <> Monitored Then
       
        'Insert code to do things as a result of a change

        'Reset Variable with new monitored value
        Monitored = Range("E8").Value
    End If

    Application.EnableEvents = True
End Sub


[osknows]

Sagar, if you need more help with this PM me describing exactly what your trying to do

[sagar]

Thanks Osknows - I sent you a PM.