View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
ManicMiner17 ManicMiner17 is offline
external usenet poster
 
Posts: 30
Default trigger formula execution based on user entry

This is quite crude, no error trapping of events etc.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim iSect1 As Range
Dim iSect2 As Range
Dim rngEnd As Long
Dim r As Range
Dim ws As Worksheet


Set ws = Sheets("Sheet1")
rngEnd = Range("A" & Rows.Count).End(xlUp).Row

Set iSect1 = Application.Intersect(Range("a1:a" & rngEnd), Target)
If Not iSect1 Is Nothing Then
For Each r In iSect1
If Target < 0 And Target.Offset(0, 1) < 0 Then
Target.Offset(0, 2) = Target * Target.Offset(0, 1)
End If
Next r
End If

Set iSect2 = Application.Intersect(Range("b1:b" & rngEnd), Target)
If Not iSect2 Is Nothing Then
For Each r In iSect2
If Target.Offset(0, -1) < 0 And Target < 0 Then
Target.Offset(0, 1) = Target.Offset(0, -1) * Target
End If
Next r
End If
End Sub


Thanks for the suggestions, but I'd prefer to do this with a VB script
instead of having a formula in the 'result' cell. The requirement
would be that the 'result' cell be completely empty until columns A
and B are filled.