Using a macro to determine whethet to put a formula into a cell

[Paul]

Hi,

This will be tough to explain, but I'll give it a try!!

Basically I was looking for help on a spreadsheet that has grown
rather large due to 3000 or so formulas filled down in column A

These formulas work out values in column B (there may only be 50
values here)

Values in column B eventually end....but will be added to
periodically.

However formulas still have to populate column A in readyness for the
new values in column B

I was looking for a way for excel to see that there is no value in
column B:B and to leave the corresponding cell in column A empty,
however when a value is added to B, excel sees it and pastes the
formula in column A....and so on.

Is this even possible (if you can work out what the hell I'm talking
about!)

Thanks

Paul

 

[Brad]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then ' If the cell that changed
is column B then
If IsEmpty(Target.Value) Then ' If the value of
the cell is empty then clear the formula
Target.Offset(0, -1).Clear
Else ' If not, create the formula.
Target.Offset(0, -1).Formula = "=sum(" &
Target.Address & ")"
End If
End If
End Sub

 

[Paul]

Thanks a million Brad,

Works perfectly.
I'm surprised I couldn't find this described on the forums elsewhere
at it seems so useful.

Thanks again.

Paul

 

[Paul]

Actually, is there a way of making this an array formula, so that it
changes as the values in b are filled down? b1 into a1, b2 into a2
etc.