Row Insert, Add Text, Add Formula

[Guest]

Have a spreadsheet with budget numbers. Need macro to insert row ABOVE each
existing row, insert text "Actual" in "B,row#" at EVERY line (so I can key in
actuals), then insert a row BELOW the existing data, insert text "Variance"
in "B,row#", then in each column C-N insert a formula that subtracts the
value in the Budget row from the Value in the Actual row. Then insert a
blank row beneath the Variance row. I have 1400 rows to process.

 

[Guest]

Hi
Not sure exactly what you mean by "Actual" in "B,Row#". I am assuming form
the rest of your message that you want to enter actual data into columns C
throug N of new rows created above existing Budget figures, with the Word
"Actual" in column B. This would enable you to enter your Formula (Actual -
Budget) into another new row created below the budget row, with "Variance"
in column B.

So we would end up with Actual Row, Budget Row, Variance Row, Blank Row,
Actual Row, Budget Row, Etc.

'Try This (simple, but should work):
Sub Row_Inserter()

Range("A12").Select '(Or the cell immediately after your last data entry
cell)'
ActiveCell.FormulaR1C1 = "XXX"

Range("A1").Select '(Or whatever Row you have your first data on)'
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(2, 0).Select
Do While ActiveCell <> "XXX"
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
ActiveCell.Offset(4, 0).Select
Loop

End Sub

Using the above method, it is easy to write a second subroutine, equally
simple to fill in the Actual, Budget and Variance headers

And a third routine as well for the variance figures, using the following:
ActiveCell.FormulaR1C1 = "=R[-2]C-R[-1]C" 'this will calc the variance'

Hope it Helps

Bigg19