将Excel公式复制到多个工作表上的最后一行

3 回答

• 1

  你只是远离目标的一个参考：

  Sub ConcatForm()
      Dim wSht As Worksheet
  
      lastRow = Range("M" & Rows.count).End(xlUp).row '<--| without explicit worksheet qualification it will reference a range in the "active" sheet
      Application.ScreenUpdating = False
      For Each wSht In Worksheets
          If wSht.Name <> "Pie" Then
              wSht.Range("N2").FormulaR1C1 = "=CONCATENATE(RC[-3],RC[-2],RC[-1])"
              wSht.Range("N2").AutoFill Destination:=wSht.Range("N2:N" & lastRow) '<--| this will reference a range in 'wSht' worksheet
          End If
      Next
      Application.ScreenUpdating = True
  End Sub
  

  回复于 2024-05-01T11:15:40+08:00
• 0

  您无需使用 Autofill 来实现此目的 .

  • 只需将您的公式直接应用于您的范围和 use relative references ，即 K2 ，而不是绝对参考，即 $K$2 . 它将为您填写并更新公式 .

  • 确保您完全符合参考资格 . 例如，查看我使用 ThisWorkbook 的位置以及如何初始化 lastrow 的更新 . 否则，Excel可能会混淆并抛出其他错误 .

  • 您的 lastrow 变量尚未标注尺寸，因此它是一个隐含的 Variant . 你最好明确地将它标注为 Long .

  ---

  Sub ConcatForm()
      Application.ScreenUpdating = False
  
      Dim wSht As Worksheet
      Dim lastrow As Long
  
      With ThisWorkbook.Worksheets("Sheet1") 'which worksheet to get last row?
          lastrow = .Range("M" & .Rows.Count).End(xlUp).Row
      End With
  
      For Each wSht In ThisWorkbook.Worksheets
          If wSht.Name <> "Pie" Then
              wSht.Range("N2:N" & lastrow).Formula = "=CONCATENATE(K2,L2,M2)"
          End If
      Next wSht
  
      Application.ScreenUpdating = True
  End Sub
  

  回复于 2024-05-01T11:15:40+08:00
• -2

  使用以下子...

  Sub ConcatForm()
      Dim wSht As Worksheet
  
      Lastrow = Range("A" & Rows.Count).End(xlUp).Row
      Application.ScreenUpdating = False
      For Each wSht In Worksheets
      With wSht
          If .Name <> "Pie" Then
              .Select
              .Range("N2").FormulaR1C1 = "=CONCATENATE(RC[-3],RC[-2],RC[-1])"
              .Range("N2").AutoFill Destination:=Range("N2:N" & Lastrow)
          End If
      End With
      Next wSht
  
      Application.ScreenUpdating = True
  
  End Sub
  

  回复于 2024-05-01T11:15:40+08:00