输入新行并从上方的单元格中复制公式 [英] Enter a new line and copy formula from cells above

问题描述

我正在尝试创建一个执行以下操作的Excel宏:

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

1. 在文档末尾输入新行

1. Enter a new line at the end of document

从上面的单元格中复制公式

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

这将复制第一个单元格"A"的公式，而不是随后的单元格

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

我想做类似Selection.Offset(0, 1).Select的操作，然后将其迭代到"K"(最好没有"G"和"H")

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.

我想要这样的东西(非工作伪代码)

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

推荐答案

您可以简单地将之前的行复制/插入到新行中

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

这篇关于输入新行并从上方的单元格中复制公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！