Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Marco
I know what I marco is, but I am not at all familiar with using them. Here is
my dilemma. I have a worksheet that needs to be updated by adding new cells above a row of totals and below the last set of formulas. Use this for an example. Lets say a1=f1*8, a2=f2*8, a3=f3*8, a4=f4*8; and b1=g1*8, b2=g2*8, b3=g3*8, b4=g4*8. Underneath them in a5 and b5 would be a sum function for example. What a want to do is insert two rows above a5 and b5, so the sum function will now be in a7 and b7. Then copy cells a1:b2 and paste them in a5:b6. If i would use the marco again it would insert two more rows and copy cells a1:b2 and paste them in a7:b8 and have the sum function be pushed down to a9 and b9. Not sure if this is even possible, or if a marco will work for it, but any help is appreciated! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Marco
This will do what you asked, but it must be run with the worksheet you want
to use it on active (that is, go to the worksheet you want to use this on, press Alt+F8, select InsertTwoDataRows from the list and click the Run button)... Sub InsertTwoDataRows() Dim LastRow As Long LastRow = Cells(Rows.Count, "A").End(xlUp).Row Cells(LastRow, "A").EntireRow.Insert Cells(LastRow, "A").EntireRow.Insert Range("1:2").Copy Cells(LastRow, "A") End Sub Rick "JBoyer" wrote in message ... I know what I marco is, but I am not at all familiar with using them. Here is my dilemma. I have a worksheet that needs to be updated by adding new cells above a row of totals and below the last set of formulas. Use this for an example. Lets say a1=f1*8, a2=f2*8, a3=f3*8, a4=f4*8; and b1=g1*8, b2=g2*8, b3=g3*8, b4=g4*8. Underneath them in a5 and b5 would be a sum function for example. What a want to do is insert two rows above a5 and b5, so the sum function will now be in a7 and b7. Then copy cells a1:b2 and paste them in a5:b6. If i would use the marco again it would insert two more rows and copy cells a1:b2 and paste them in a7:b8 and have the sum function be pushed down to a9 and b9. Not sure if this is even possible, or if a marco will work for it, but any help is appreciated! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Marco
I assume you want the formula in the Sum cells adjusted to cover the old
range plus the added cells: Sub InsertTwoRows() Dim LastRow As Long LastRow = Cells(Rows.Count, "A").End(xlUp).Row Cells(LastRow, "A").Resize(2, 2).Insert Shift:=xlShiftDown Range("A1:B2").Copy Cells(LastRow, "A") Cells(LastRow + 2, "A").Resize(1, 2).FormulaR1C1 = _ "=SUM(R1C:R[-1]C)" End Sub -- Regards, Tom Ogilvy "JBoyer" wrote: I know what I marco is, but I am not at all familiar with using them. Here is my dilemma. I have a worksheet that needs to be updated by adding new cells above a row of totals and below the last set of formulas. Use this for an example. Lets say a1=f1*8, a2=f2*8, a3=f3*8, a4=f4*8; and b1=g1*8, b2=g2*8, b3=g3*8, b4=g4*8. Underneath them in a5 and b5 would be a sum function for example. What a want to do is insert two rows above a5 and b5, so the sum function will now be in a7 and b7. Then copy cells a1:b2 and paste them in a5:b6. If i would use the marco again it would insert two more rows and copy cells a1:b2 and paste them in a7:b8 and have the sum function be pushed down to a9 and b9. Not sure if this is even possible, or if a marco will work for it, but any help is appreciated! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Marco
This is how I modified your formula to fit my sheet. However you are right I
need to have the last row change to accomadate for the newly added cells, however my formulas are more complicated than a simple sum function so I hope you can help. Sub InsertFiveRows() Dim LastRow As Long LastRow = Cells(Rows.Count, "A").End(xlUp).Row Cells(LastRow, "A").EntireRow.Insert Cells(LastRow, "A").EntireRow.Insert Cells(LastRow, "A").EntireRow.Insert Cells(LastRow, "A").EntireRow.Insert Cells(LastRow, "A").EntireRow.Insert Range("AU1:BF5").Copy Cells(LastRow, "A") End Sub Cell B47 currently has the formula =COUNTA(B2:B46) Cell C47 currently has the formula =IF(SUMIF(E2:E46,"0",C2:C46)0,SUMIF(E2:E46,"0", C2:C46),"") Cell D47 currently has the formula =IF(C47<"",E47/C47,"") Cell E47 currently has the formula =IF(SUM(E2:E46)0,SUM(E2:E46),"") All of these cells need to accomadate 5 newly added rows. Hope you can help, and thanks for the fast replies last time. "Tom Ogilvy" wrote: I assume you want the formula in the Sum cells adjusted to cover the old range plus the added cells: Sub InsertTwoRows() Dim LastRow As Long LastRow = Cells(Rows.Count, "A").End(xlUp).Row Cells(LastRow, "A").Resize(2, 2).Insert Shift:=xlShiftDown Range("A1:B2").Copy Cells(LastRow, "A") Cells(LastRow + 2, "A").Resize(1, 2).FormulaR1C1 = _ "=SUM(R1C:R[-1]C)" End Sub -- Regards, Tom Ogilvy "JBoyer" wrote: I know what I marco is, but I am not at all familiar with using them. Here is my dilemma. I have a worksheet that needs to be updated by adding new cells above a row of totals and below the last set of formulas. Use this for an example. Lets say a1=f1*8, a2=f2*8, a3=f3*8, a4=f4*8; and b1=g1*8, b2=g2*8, b3=g3*8, b4=g4*8. Underneath them in a5 and b5 would be a sum function for example. What a want to do is insert two rows above a5 and b5, so the sum function will now be in a7 and b7. Then copy cells a1:b2 and paste them in a5:b6. If i would use the marco again it would insert two more rows and copy cells a1:b2 and paste them in a7:b8 and have the sum function be pushed down to a9 and b9. Not sure if this is even possible, or if a marco will work for it, but any help is appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Marco | Excel Discussion (Misc queries) | |||
Marco Help | Excel Programming | |||
help me with this marco | Excel Programming | |||
I need some help with a Marco | Excel Discussion (Misc queries) | |||
Marco to run every day at 12 | Excel Programming |