如何将公式复制到其他列

我有一个表,是我通过以前的宏创建的。

在另一个问题的帮助下,我能够在B列中找到 “所有其他”,并在相邻列中插入一个公式。

打印屏幕。enter image description here

现在我想从未知活动单元格中复制公式,并将其粘贴到相邻的列中。D,E,G,H,I,J,和L列中 Offset – 0 Rows.

我现在有。

Sub AllOther()

Dim ws As Worksheet
Dim aOther As Range
Dim DataLastRow As Long

Set ws = ActiveSheet

DataLastRow = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row

    Set aOther = ws.Range("B:B").Find("All Other", LookIn:=xlValues, lookat:=xlWhole)

    If Not aOther is Nothing Then
        aOther.Offset(0, 1).Formula = "=SUM(" & aOther.Offset(3, 1).Address & ":" & ws.Cells(DataLastRow,3).Address & ")"
    Else
        MsgBox """All Other"" not found in column."
    End If

'Copy/Paste into other Columns
End Sub

解决方案:

Q1 什么是”未知活性细胞” 你指的是什么?

Q2 你想在公式中求什么?=SUM(” & aOther.Offset(3, 1).Address & “:” & ws.Cells(DataLastRow,3).Address & “)” ?

幅度的开始 aOther.Offset(3,1).地址。 是3行 下面 aOther和范围的末端是任何地方。

总之,如果你在公式中不混入一个偏移的 a其他 偏移量为 ws.

3 这样做将使你能够像下面的代码一样循环。

Sub AllOther()
    Dim ws As Worksheet
    Dim aOther As Long
    Dim aOtherRow As Integer ' row

    Dim arr As Variant
    arr = Array(3, 4, 5, 7, 8, 9, 11) ' columns to sum

    Set ws = ActiveSheet

    Set aOther = ws.Range("B:B").Find("All Other", LookIn:=xlValues, lookat:=xlWhole).Row

    If Not aOther Is Nothing Then
        aOtherRow = aOther.Row
        For Each i In arr
            ws.Cells(aOtherRow, i).Formula = "=SUM(" & ws.Cells(FirstRow, i).Address & ":" & ws.Cells(LastRow, i) & ")"
        Next i
    Else
        MsgBox """All Other"" not found in column."
    End If
End Sub

其中 第一行最后一行 取决于Q2的答案

Sub AllOther()
    Dim ws As Worksheet
    Dim aOther As Range

    Dim aOtherRow As Long
    Dim DataFirstRow As Long
    Dim DataLastRow As Long
    Dim col as integer

    Dim ColumnsArray As Variant
    ColumnsArray = Array(3, 4, 5, 7, 8, 9, 11) ' columns to sum


    Set ws = ActiveSheet

    Set aOther = ws.Range("B:B").Find("All Other", LookIn:=xlValues, lookat:=xlWhole)

    If Not aOther Is Nothing Then
        aOtherRow = aOther.Row
        DataFirstRow = aOtherRow + 1
        DataLastRow = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row
        For Each col In ColumnsArray
            ws.Cells(aOtherRow, col).Formula = "=SUM(" & ws.Cells(DataFirstRow, col).Address & ":" & ws.Cells(DataLastRow, col).Address & ")"
        Next col
    Else
        MsgBox """All Other"" not found in column."
    End If
End sub

给TA打赏
共{{data.count}}人
人已打赏
未分类

我如何授权谷歌应用脚本中的谷歌语音转文本?

2022-9-13 14:18:39

未分类

转换为rxjs的方法论

2022-9-13 14:18:41

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索