excel - 调整ActiveCell.FormulaR1C1

Question

您好，我有一个问题，我有用宏记录器制作的代码:

ActiveCell.FormulaR1C1 = "=New_Order!RC[-42]+New_Order!RC[-41]+New_Order!RC[-40]"

我如何调整这段代码，使其成为这样的东西:

'ActiveCell.Formula = Sheets("New_Order").Range(n & LastRow) + Sheets("New_Order").Range(O & LastRow) + Sheets("New_Order").Range(p & LastRow)

我在 N、O、P 列中名为 New_Order 的不同工作表中有值。我想对这些值求和并自动填充工作表注册中的整个列。完整的代码如下所示:

 Sub Registrereren()

Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False

On Error Resume Next

Dim oWkSht As Worksheet
Dim LastColumn As Long
Dim c As Date
Dim myCell As Range
Dim LastRow As Long

Sheets("Registration").Activate


Set oWkSht = ThisWorkbook.Sheets("Registration")
LastColumn = oWkSht.Range("A" & Columns.Count).End(xlToRight).Column
LastRow = oWkSht.Range("C" & Rows.Count).End(xlUp).Row

c = Date

Set myCell = oWkSht.Range("1:1").Find(What:=c, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False, SearchOrder:=xlByColumns)

If Not myCell Is Nothing Then
    myCell.Offset(1, 0).Select

    ActiveCell.FormulaR1C1 = "=New_Order!RC[-42]+New_Order!RC[-41]+New_Order!RC[-40]"
    'ActiveCell.Formula = Sheets("New_Order").Range(n & LastRow) + Sheets("New_Order").Range(O & LastRow) + Sheets("New_Order").Range(p & LastRow)
    myCell.Offset(1, 0).Select
    Range(myCell.Offset(1), Cells(LastRow, myCell.Column)).Select
    Selection.FillDown

End If

Sheets("Main").Activate

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Answer 1

我做出了一些假设，并因此进行了一些更正。我假设您在注册表的“A”列中的条目与 New_Order 表中的行数相匹配。

Option Explicit


Sub Registrereren()

  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  Application.ScreenUpdating = False

  On Error Resume Next

  Dim oWkSht As Worksheet
  Dim LastColumn As Long
  Dim c As Date
  Dim myCell As Range
  Dim LastRow As Long

  Sheets("Registration").Activate

  Set oWkSht = ThisWorkbook.Sheets("Registration")
  LastColumn = oWkSht.Cells(1, oWkSht.Columns.Count).End(xlToLeft).Column
  LastRow = oWkSht.Cells(oWkSht.Rows.Count, "A").End(xlUp).row

  c = Date
  Set myCell = oWkSht.Cells(1, 1).Resize(, LastColumn).Find(What:=c, LookIn:=xlFormulas, _ 
               lookat:=xlWhole, MatchCase:=False, SearchOrder:=xlByColumns)

  If Not myCell Is Nothing Then
    myCell.Offset(1, 0).Formula = "=New_Order!N1+New_Order!O1+New_Order!P1"
    Range(myCell.Offset(1), Cells(LastRow, myCell.Column)).Select
    Selection.FillDown
  End If

  Sheets("Main").Activate

  Application.Calculation = xlCalculationAutomatic
  Application.EnableEvents = True
  Application.ScreenUpdating = True

End Sub