输入新行并从上方的单元格中复制公式 [英] Enter a new line and copy formula from cells above
问题描述
我正在尝试创建一个执行以下操作的Excel宏:
I am trying to create an Excel macro that does the following:
-
在文档末尾输入新行
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屋!