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.

On 09/08/2010 18:32, fallowfz wrote:
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)

Hi

This formula should do the trick:

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

Regards,
Per


On 9 Aug., 19:32, fallowfz wrote:
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

On Aug 9, 3:34*pm, Per Jessen wrote:
Hi

This formula should do the trick:

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

Regards,
Per

On 9 Aug., 19:32, fallowfz wrote:



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- Hide quoted text -

- 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.

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.

On Aug 9, 4:46*pm, ManicMiner17 wrote:
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.

Great; thanks for the code!