Auto Fill Equations in VBA

[CSUS_CE_Student]

Hey every, i have a problem.
I have a spreadsheet that several rows, and multiple calculated in the
columns in that row. The values are dependant on the row above, and also
another cell that is "locked" (eg. $C$4). What i need to do is insert a row
in the middle of the rows, and then have it "drag" the row above the inserted
rown down 2 rows to add and correct the equations in the blank row. The row
under the inserted row is wrong because now it looks at the cell 2 rows above
it, not one. Anyone know a simple code to fill a range down with the
formula? I have this from the macro but i need to make it general so it
automaticly will do it for any place i insert a row.

Sub Macro2()
' Macro2 Macro
Selection.EntireRow.Insert
Selection.AutoFill Destination:=Range("B21:K23"), Type:=xlFillDefault
Range("B21:K23").Select
End Sub

The Range("B21:K23") is what i need to generalize.

I hope i wasn't too wordy.

Thanks in Advance

CSUS_CE_Student

 

[joel]

You need to do it the same way you would on a worksheet manually. The best
method is to copy a row with the formula and the Insert the row where you
want the new row. The formulas will automatcally be adjusted. Then you can
modify the cels that don't have formulas as required.

 

[CSUS_CE_Student]

Thanks Joel,

I ended up with this and it seems to work. Pretty simple, not sure why i
didint firgure it out before.

Sub InsertRow()
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.PasteSpecial
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.PasteSpecial
End Sub

 

[Homey]

Selection.EntireRow.Insert
Intersect(ActiveCell.Offset(-1).EntireRow. _
Resize(Selection.Rows.Count + 2), Range("B:K")).FillDown

this work if you have more than 1 row in selection.

| Thanks Joel,
|
| I ended up with this and it seems to work. Pretty simple, not sure why i
| didint firgure it out before.
|
| Sub InsertRow()
| Selection.EntireRow.Insert
| ActiveCell.Offset(-1, 0).Select
| ActiveCell.EntireRow.Copy
| ActiveCell.Offset(1, 0).Select
| ActiveCell.EntireRow.PasteSpecial
| ActiveCell.Offset(1, 0).Select
| ActiveCell.EntireRow.PasteSpecial
| End Sub
|
| "joel" wrote:
|
| > You need to do it the same way you would on a worksheet manually. The
best
| > method is to copy a row with the formula and the Insert the row where
you
| > want the new row. The formulas will automatcally be adjusted. Then
you can
| > modify the cels that don't have formulas as required.
| >
| > "CSUS_CE_Student" wrote:
| >
| > > Hey every, i have a problem.
| > > I have a spreadsheet that several rows, and multiple calculated in the
| > > columns in that row. The values are dependant on the row above, and
also
| > > another cell that is "locked" (eg. $C$4). What i need to do is insert
a row
| > > in the middle of the rows, and then have it "drag" the row above the
inserted
| > > rown down 2 rows to add and correct the equations in the blank row.
The row
| > > under the inserted row is wrong because now it looks at the cell 2
rows above
| > > it, not one. Anyone know a simple code to fill a range down with the
| > > formula? I have this from the macro but i need to make it general so
it
| > > automaticly will do it for any place i insert a row.
| > >
| > > Sub Macro2()
| > > ' Macro2 Macro
| > > Selection.EntireRow.Insert
| > > Selection.AutoFill Destination:=Range("B21:K23"),
Type:=xlFillDefault
| > > Range("B21:K23").Select
| > > End Sub
| > >
| > > The Range("B21:K23") is what i need to generalize.
| > >
| > > I hope i wasn't too wordy.
| > >
| > > Thanks in Advance
| > >
| > > CSUS_CE_Student

 

[joel]

Why not this?

Sub InsertRow()
ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.Insert
ActiveCell.EntireRow.Insert
End Sub

 

[CSUS_CE_Student]

Thanks Homey,

I realized after lunch there was still an error incopying, the cell values
wernt changing correctly. The Filldown command worked perfectly to correct
this.

Thanks again!