trigger formula execution based on user entry

[fallowfz]

very elementary i'm sure, but i haven't been able to find a solution
yet...

I'd like to be able to trigger the execution of a simple formula once
a user has entered data into the necessary cells.

e.g.

A B C
Entry # 1 Entry # 2 Result

I'd like the macro to run once entries have been made in Columns A and
B.

I'd like Column C to be completely empty until Entries 1 and 2 have
been made by the User. I've tried using
=IF(ISBLANK(A1)=TRUE,,<calculation>) in column C, but hasn't worked
well for my application where I'm using the data in Column C in a
dynamic chart.

-fallowfz

 

[ManicMiner17]

very elementary i'm sure, but i haven't been able to find a solution
yet...

I'd like to be able to trigger the execution of a simple formula once
a user has entered data into the necessary cells.

e.g.

A B C
Entry # 1 Entry # 2 Result

I'd like the macro to run once entries have been made in Columns A and
B.

I'd like Column C to be completely empty until Entries 1 and 2 have
been made by the User. I've tried using
=IF(ISBLANK(A1)=TRUE,,<calculation>) in column C, but hasn't worked
well for my application where I'm using the data in Column C in a
dynamic chart.

-fallowfz

Does the cell have to be empty or just evaluate to zero?

You don't say what the formula is, this assumes a simple multiplication.

=IF(OR(ISBLANK(A2), ISBLANK(B2)),"",A2*B2)

 

[Per Jessen]

Hi

This formula should do the trick:

=IF(AND(A1<>"",B1<>""),<calculation>,"")

Regards,
Per

 

[fallowfz]

Hi

This formula should do the trick:

=IF(AND(A1<>"",B1<>""),<calculation>,"")

Regards,
Per







- Show quoted text -

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.

 

[ManicMiner17]

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

 

[fallowfz]

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

Great; thanks for the code!