将R1C1公式放在变量行上以引用同一列

Question

我需要帮助来编写一个代码，把一个R1C1公式到行的单元格。

每次运行宏时，行的起始位置都会有所不同。即。如果宏是第一次运行，则公式将作为R-5C3输入到行B16中。这种情况下的R-5是E12。

但是，当宏再次运行时，当它输入到行B25中时，我仍然希望它引用E3，但它引用E20。

以下是我的代码

Dim cell As Range, MyRange As Range 

Set MyRange = Range("B1:B5000")

For Each cell In MyRange

If cell = " " And cell.Offset(, 1) <> "Record" Then

cell.FormulaR1C1 = "=SUM(R[-5]C[3]: SUM(R[-5]C[4])"

End If

Next cell

End With

你是对的，我的代码试图说This row - 5, this column + 3: this row -5, this column + 4，我遇到的问题是，这行可以是任何行&我想使用相对引用，因为这个公式复制到下一行，所以我尝试这样做

Cell B16 = E11+F11
Cell B17 = E12+F12
Cell B18 = E13+F13 etc

然后，当宏再次运行时，开始单元格为E25，然后

Cell E25 = E20+F20
Cell E26 = E21+F21
Cell E26= E22+F22 etc

因此，无论宏指向哪个单元格，它都将始终从E11+F11开始计算

Answer 1

以下是我的代码

Dim单元格作为范围，MyRange作为范围设置MyRange =范围(“B1:B5000”)

对于MyRange中的每个单元格

如果单元格=“”且cell.Offset(，1) <>“记录”，则

cell.FormulaR1C1 = "=SUM(R-5C3: SUM(R-5C4))“

结束If

下一个单元格

结尾为

Answer 2

您在公式中使用了相对引用，如果在单元格B6中输入，公式将不会像您试图说的那样工作。任何高于该值的行，它都会尝试引用表外的内容。

要使用绝对引用，请使用R3C5 (第3行，第5列= E3)。

你的公式充其量是想说This row - 5, this column + 3: this row -5, this column + 4

也许可以试试和=SUM($E$3:$F$3)一样的"=SUM(R3C5:R3C4)"。

还有- cell = " " -单元格必须包含一个空格？应该是cell = ""吗？

编辑：为了响应您的编辑-如果您希望第一个公式总是看E11:F11，下一个公式是E12:F12等，您可以使用以下解决方案之一：

在一次命中中将公式添加到所有行-这不会检查带空格的单元格：

Public Sub Test()

    Dim MyRange As Range
    Dim lOffset As Long

    Set MyRange = Range("B1:B5000")

    With MyRange
        lOffset = 11 - .Row
        .FormulaR1C1 = "=IF(RC[1]<>""Record"",SUM(R[" & lOffset & "]C5:R[" & lOffset & "]C6),"""")"
    End With

End Sub  

要在添加公式之前检查每个单元格是否有空格：

Public Sub Test1()

    Dim MyRange As Range
    Dim rCell As Range
    Dim lOffset As Long

    Set MyRange = Range("B30:B5000")

    lOffset = 11 - MyRange.Row
    For Each rCell In MyRange
        If rCell = " " And rCell.Offset(, 1) <> "Record" Then
            rCell.FormulaR1C1 = "=SUM(R[" & lOffset & "]C5:R[" & lOffset & "]C6)"
        End If
    Next rCell

End Sub

下面是显示公式始终从第11行开始的第二个代码块的结果：

​

如果你改变范围，这个公式就会出现：

​

编辑2:

如果第20:24行有记录，则会将=SUM($E11:$F11)放在第25行。如果第26行有记录，则第27行将有=SUM($E12:$F12)

Public Sub Test1()

    Dim MyRange As Range
    Dim rCell As Range
    Dim lOffset As Long

    Set MyRange = Range("B20:B30")

    lOffset = 11
    For Each rCell In MyRange
        If rCell = " " And rCell.Offset(, 1) <> "Record" Then
            rCell.FormulaR1C1 = "=SUM(R[" & lOffset - rCell.Row & "]C5:R[" & lOffset - rCell.Row & "]C6)"
            lOffset = lOffset + 1
        End If
    Next rCell

End Sub