auto fill formula on new rows

does this look like it would do the job of auto filling the formula to
new rows if I stick it into the sheet code ?

Private Sub Worksheet_SelectionChange2(ByVal Target As Range)
Application.EnableEvents = False
If Target.Offset(-1, 0).HasFormula = True Then
Range(Target.Offset(-1, 0), Target).FillDown
End If
Application.EnableEvents = True
End Sub

I did it and turned off the extend data range formats and formulas in
tools, options and edit...

SO I am thinking the script isn't working like it should, how close is
it ?

First, there is no "Worksheet_SelectionChange2" event. So that means if you
wanted this to run automatically, it won't work. And it would scare me to use
the "worksheet_selectionchange" event--it would fire way too often for me.

Just my opinion, but if I wanted something like this, I'd want to run it on
demand--not automatic.

If that appeals to you, you may want to look at the way David McRitchie handled
it:
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
look for: InsertRowsAndFillFormulas



RompStar wrote:
>
> does this look like it would do the job of auto filling the formula to
> new rows if I stick it into the sheet code ?
>
> Private Sub Worksheet_SelectionChange2(ByVal Target As Range)
> Application.EnableEvents = False
> If Target.Offset(-1, 0).HasFormula = True Then
> Range(Target.Offset(-1, 0), Target).FillDown
> End If
> Application.EnableEvents = True
> End Sub
>
> I did it and turned off the extend data range formats and formulas in
> tools, options and edit...
>
> SO I am thinking the script isn't working like it should, how close is
> it ?

--

Dave Peterson

cool, for now I went with this, only so many things that I can do in an
8 hour work day :- ) wipping me at the office :- ) lol

for now, I solved it like this with some help..

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo Err
Application.EnableEvents = False

If Intersect(Target.Offset(-1, 0),
Cells.SpecialCells(xlCellTypeAllValidation)) Is Nothing Then GoTo Err
Target.Offset(-1, 0).Copy
Target.PasteSpecial Paste:=xlPasteValidation
Application.CutCopyMode = False

Err:
If Target.Column <> 12 Then GoTo ErrEnd
If Target.Offset(-1, 0).HasFormula = True Then
Range(Target.Offset(-1, 0), Target).FillDown
End If

ErrEnd: Application.EnableEvents = True

End Sub

There is also a change event macro that I just added a couple of days
ago that you may like better than your selection change macro.
http://www.mvps.org/dmcritchie/excel...m#change_event
You have to do your own insert row, and the macro will be triggered
when you type into column A . If it is the last row then you don't
have to insert, just type into Column A. The macro checks to
make sure the rest of the row is empty otherwise it doesn't continue.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"RompStar" <[email protected]> wrote in message news:[email protected]...
> cool, for now I went with this, only so many things that I can do in an
> 8 hour work day :- ) wipping me at the office :- ) lol
>
> for now, I solved it like this with some help..
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> On Error GoTo Err
> Application.EnableEvents = False
>
> If Intersect(Target.Offset(-1, 0),
> Cells.SpecialCells(xlCellTypeAllValidation)) Is Nothing Then GoTo Err
> Target.Offset(-1, 0).Copy
> Target.PasteSpecial Paste:=xlPasteValidation
> Application.CutCopyMode = False
>
> Err:
> If Target.Column <> 12 Then GoTo ErrEnd
> If Target.Offset(-1, 0).HasFormula = True Then
> Range(Target.Offset(-1, 0), Target).FillDown
> End If
>
> ErrEnd: Application.EnableEvents = True
>
> End Sub
>

Wow, lots of cool free info., thanks.