Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Fill Equations in VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Fill Equations in VBA
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Fill Equations in VBA
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Fill Equations in VBA
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. "CSUS_CE_Student" wrote in message ... | 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Fill Equations in VBA
Why not this?
Sub InsertRow() ActiveCell.EntireRow.Copy ActiveCell.EntireRow.Insert ActiveCell.EntireRow.Insert End Sub "CSUS_CE_Student" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Fill Equations in VBA
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! "Homey" wrote: 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. "CSUS_CE_Student" wrote in message ... | 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto fill or auto search from a list or drop-down list??????? | Excel Discussion (Misc queries) | |||
Auto-populate, Auto-copy or Auto-fill? | Excel Worksheet Functions | |||
using auto fill edit or fill handel | Excel Worksheet Functions | |||
Auto fill down | Excel Programming | |||
auto filter and auto fill | Excel Programming |