making a cell recalculate as data is entered in rows

Posted by Warren on November 04, 2000 1:26 PM

Here is some simple data in a spreadsheet:
A B C D E
1 20 4 5 5 20

2 20 5 4 9 40

3 20

The spreadsheet is set up such that each row remains blank until I input a number into column B of that row. Suppose I want cell A50 to diplay the result of E2/D2 after I input a number in B2. What function or macro can I use to make A50 automatically calculate E3/D3 after I enter a number in B3 and so on for Row 4, Row 5, Row 6 etc?

Posted by Celia on November 04, 2000 2:21 PM

Warren

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2:B49")) Is Nothing Then
Target.Offset(48, -1).FormulaR1C1 = "=R[-48]C[4]/R[-48]C[3]"
End If
End Sub

Celia

Posted by Warren on November 04, 2000 8:17 PM

Thanks for your response Celia, but I must admit that I am a dummy when it comes to the language of computers. Could you please explain what that stuff means and how I implement it in my worksheet.

Thanks

Posted by Celia on November 04, 2000 10:30 PM

Warren
Activate your workbook and open the VB Editor Alt+F11).
Open the Project window( View>Project Explorer).
Select ThisWorkbook (under the VB Project which has your workbook name).
Put the macro in the code module.

The macro should run whenever there is an input to a cell in the range B2:B49 and should do what you asked for in your original message.
Try it and post again if it doesn't do what you need.
Celia