我需要在 VBA 中向下拖动一个公式

Question

Sub Insert_Formula()
Dim NetPrice As String
NetPrice = "=I7*(100-N7)"
Worksheets(1).Range("O7").Formula = NetPrice
Dim StandardMargin As String
StandardMargin = "=O7-Q7-S7"
Worksheets(1).Range("T7").Formula = StandardMargin
Dim StandardMarginPercentage As String
StandardMarginPercentage = "=T7/O7"
Worksheets(1).Range("U7").Formula = StandardMarginPercentage
Dim GrossMargin As String
GrossMargin = "=T7-AB7"
Worksheets(1).Range("AC7").Formula = GrossMargin
Dim GrossMarginPercentage As String
GrossMarginPercentage = "=AD7/O7"
Worksheets(1).Range("AD7").Formula = GrossMarginPercentage
End Sub

这是我当前的公式，问题是我需要将公式从第 7 行向下拖动到表中的每一列的最后一行，该列都有一个公式，但每一列都不紧挨着另一列。

Answer 1

您可以使用.AutoFill，也可以以R1C1格式写入.FormulaR1C1。

首先，您需要弄清楚最后一行...

' Get the Last row
Dim LastRow as long
' You'll want to use a column that you know will have a value, to find the last row
LastRow = GetLastRow(9, Worksheets(1))        ' 9 = Column I

获取最后一行函数

Public Function GetLastRow(Optional Col As Integer = 1, Optional Sheet As Excel.Worksheet) As Long
If Sheet Is Nothing Then Set Sheet = Application.ActiveSheet
GetLastRow = Sheet.Cells(Sheet.Rows.Count, Col).End(xlUp).Row
End Function

自动填充

Worksheets(1).Range("O7").AutoFill _
Destination:=Worksheets(1).Range("O7:O"& LastRow), _
Type:=xlFillDefault

公式R1C1

NetPrice = "=RC[-6]*(100-RC[-1])"
Worksheets(1).Range("O7:O"& LastRow).FormulaR1C1 = NetPrice

Answer 2

您需要遍历每一行。

下面是第一个公式的示例：

Sub Insert_Formula()
Dim NetPrice As String
For i = 7 to 17
NetPrice = "=I" & i & "*(100-N" & i & ")"
Worksheets(1).Range("O" & i).Formula = NetPrice
Next i
End Sub