automatic fill formulas down

[Guest]

Is it possible to have a formula to feed down automaticly, as I don't know
how many entry's there will be, and having mutible formulas on every line,
I'm looking for a way that I can put a formula in and when the data is
entered into the first column , the formuia will move to the next line.

 

[Susan]

i don't know of a formula way.........
i know a couple of other ways.......

1. enter the formula down the column as far down as you need & set
conditional formatting to make the text white if the first column on
that row is empty. then you won't SEE all the formulas sitting
there. the trouble with this method is it will make your page(s)
automatically as long as you dragged down........... so even if you
only have entries in column A to row 10, if you've dragged the white-
text-formula down to row 100, that's what your used range will be for
printing pages - not a problem but you've got to manually change the
page range, thn.

2. set up a worksheet_change macro that everytime a cell in column A
is changed/info entered, the appropriate formula is entered into the
other column. harder to do but will make your life (potentially)
easier.

if you want help with #2, let me know.
hope this helps!
susan

 

[Susan]

pardon me if this is a duplicate - not sure if 1st went thru.........
here is code for suggestion #2. it goes in a WORKSHEET module for
whichever worksheet you want it to work in:

Option Explicit

Private Sub worksheet_change(ByVal Target As Range)

Dim ws As Worksheet
Dim rTarget As Range
Dim sFormula As String
Dim lRow As Long

lRow = ActiveCell.Row
Set ws = ActiveSheet
Set Target = ws.Range("a" & lRow)
Set rTarget = ws.Range("d" & lRow)
sFormula = "=b" & lRow & "+c" & lRow

Application.EnableEvents = False
'if you don't turn off EnableEvents, every time
'it changes rTarget, it sees that as a worksheet_
'change, & it keeps looping endlessly!

If Target = "" Then
Application.EnableEvents = True
Exit Sub
Else
rTarget = sFormula
End If

Application.EnableEvents = True

End Sub



susan