从上方复制公式 [英] Copying Formula From Above

查看:62
本文介绍了从上方复制公式的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我们有一个宏,它通过Excel 2013中的按钮工作,以从上方插入带有公式的新行.问题是它何时运行,例如说从第10行复制新行11中的公式仍仍读回到第10行而不是11?

We have a macro working via a button in Excel 2013 to insert a new row with formula from above. the Problem is when it runs and i say for example copy from row 10 the formula in the new row 11 all still read back to row 10 not 11?

Sub Loop_InsertRowsandFormulas()

Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Risk Input Sheet")
Dim vRows As Long
Dim lastCol As Long
Dim firstRow As Long

firstRow = InputBox("Enter Row To Start Insert From.")
vRows = InputBox("Enter Number Of Rows Required")

If firstRow = 0 Or vRows = 0 Then Exit Sub
Debug.Print firstRow
IngA = ws.Cells(5, ws.Columns.Count).End(xlToLeft).Column
For myLoop = 1 To vRows
    ws.Range("A" & (firstRow + myLoop)).EntireRow.Insert
    ws.Range("A" & (firstRow + myLoop) & ":BB" & (firstRow + myLoop)).Formula = ws.Range("A" & firstRow & ":BB" & firstRow).Formula
Next

End Sub

推荐答案

您需要执行复制/粘贴.例如,如果 A1 包含:

You need to do a Copy/Paste. For example, if A1 contains:

=B1+C1

运行:

Sub qwerty()
    Range("A2").Formula = Range("A1").Formula
End Sub

还将留下 = B1 + C1 A2 .

如果您希望复制的公式调整",则:

If you want the copied formula to "adjust" then:

Sub ytrewq()
    Range("A1").Copy Range("A2")
End Sub

EDIT#1:

而不是:

ws.Range("A" & (firstRow + myLoop) & ":BB" & (firstRow + myLoop)).Formula = ws.Range("A" & firstRow & ":BB" & firstRow).Formula

使用类似的内容:

ws.Range("A" & firstRow & ":BB" & firstRow).Copy ws.Range("A" & (firstRow + myLoop) & ":BB" & (firstRow + myLoop))

这篇关于从上方复制公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆