3

I am trying to create an Excel macro that does the following:

  1. Enter a new line at the end of document

  2. copy the formulas from the cells above

So far I have this:

    Sub New_Delta()

    ' Go to last cell
    Range("A4").Select
    Selection.End(xlDown).Select
    LastCell = [A65536].End(xlUp).Offset(-1, 0).Address
    Range(LastCell).Select

    ' Enter new line
    Selection.EntireRow.Insert Shift:=xlUp, CopyOrigin:=xlFormatFromLeftOrAbove

    ' Copy formula from cell above
    Dim oCell As Range
        For Each oCell In Selection
            If (oCell.Value = "") Then
            oCell.Offset(-1, 0).Copy Destination:=oCell
            End If
        Next oCell

End Sub

This copies the formula for the first cell "A" but not the following ones

I want to do something like Selection.Offset(0, 1).Select and then iterate over that up to "K" (preferably without "G" and "H")

But I'm stuck, and could really use some help.

EDIT: I want something like this (Non working pseudo code)

    ' Copy formula from cell above
Dim oCell As Range
        While (oCell.Offset(-1, 0).Value != "") ' If the cell above is not empty
        oCell.Offset(-1, 0).Copy Destination:=oCell ' Copy the formula from the cell above
        Selection.Offset(0, 1).Select ' Move one cell to the right
pnuts
  • 58,317
  • 11
  • 87
  • 139
Jon Carlstedt
  • 2,317
  • 5
  • 22
  • 27
  • why would you insert a new line if the next empty line is already empty? are columns different lengths? What is the point of doing that? Sorry but its unclear to me what youre doing –  Oct 09 '13 at 11:20
  • probably to get the formatting from the row above (`xlFormatFromLeftOrAbove`). – K_B Oct 09 '13 at 11:29
  • Yes, I want the formatting from above. There are also other objects further down in the document that I want to keep moving down. Otherwise the rows would start to go under text boxes and buttons after a while. This seemed like a quick and painless solution. – Jon Carlstedt Oct 09 '13 at 11:35

1 Answers1

8

You could simply copy/insert the row before into the new row

Sub New_Delta()

  ' Go to last cell
  Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select

  ' Copy formula from cell above
  Rows(Selection.Row - 1).Copy
  Rows(Selection.Row).Insert Shift:=xlDown

End Sub
K_B
  • 3,668
  • 1
  • 19
  • 29