时间戳

首页 > 解决方案 > 将公式复制到动态列（无标题）

excel - 将公式复制到动态列（无标题）

问题描述

我有一张会定期更新的表格。由于将插入列，因此我没有固定范围。

我找到了一种查找某个值（“x”）并选择下面的单元格的方法。我设法更新了所有必要的公式并将它们打印在标题下方的单元格中。
它一直有效，直到复制为止。

我试图将公式复制到列中：

Sub Sum_three_months()

    Set Three_months = Range("A1:ZZ10000").Find("x")
    Three_months.Select
    FormularCell = ActiveCell.Offset(1, 0).Select
    Selection.Resize(Selection.Rows.Count, _
    Selection.Columns.Count).Select

    ActiveCell.FormulaR1C1 = "=SUM(R[-0]C[-4]:R[-0]C[-2])"
    
    Sum_six_months ' starts the next update, but has the same format as above
End Sub

我尝试过Autofill，FillDown但是使用自动填充时，我在范围/选定单元格中出现错误，而使用 Filldown 时，它只会复制标题。

标签： excelvbarangecopyingdynamic-columns

解决方案

请尝试此代码：

Sub Sum_three_months()
  Dim sh As Worksheet, Three_months As Range, rngLast As Range, x As String, lastCol As Long
 
  Set sh = ActiveSheet 'use here your sheet
  lastCol = sh.Cells(1, Columns.Count).End(xlToLeft).Column
  x = "x" 'use here your search string...
  Set Three_months = sh.Range(sh.Cells(1, 1), sh.Cells(1, lastCol)).Find(x)
  If Not Three_months Is Nothing Then
      Set rngLast = sh.Cells(sh.Cells(Rows.count, Three_months.Offset(0, -4).Column).End(xlUp).row, Three_months.Column)
    
      sh.Range(Three_months.Offset(1, 0), rngLast).formula = _
                   "=SUM(" & sh.Range(Three_months.Offset(1, -4), Three_months.Offset(1, -2)).address(0, 0) & ")"
  Else
     MsgBox "Not a ""x"" range could be found...": Exit Sub
  End If
    
 Sum_six_months ' starts the next update, but has the same format as above
End Sub

推荐阅读