Range.R1C1递增2

Question

尝试填充一些公式，尝试使用.FillDown，但它比使用下面的函数要慢得多。

但是，问题是我的公式=IFERROR(VLOOKUP($H11,AdHoc!A:K,11,FALSE),"---")在$H11上增加了2

Private Sub AutoFillFormulas()

    Dim LR As Long
    LR = Range("A" & ActiveSheet.Rows.Count).End(xlUp).row

    Range("R11:AH" & LR).FormulaR1C1 = Range("R1:AH1").FormulaR1C1
    
End Sub

预期成果：

R11 = =IFERROR(VLOOKUP($H11,AdHoc!A:K,11,FALSE),"---")

R12 = =IFERROR(VLOOKUP($H12,AdHoc!A:K,11,FALSE),"---")

R13 = =IFERROR(VLOOKUP($H13,AdHoc!A:K,11,FALSE),"---")

实际结果：

R11 = =IFERROR(VLOOKUP($H11,AdHoc!A:K,11,FALSE),"---")

R12 = =IFERROR(VLOOKUP($H13,AdHoc!A:K,11,FALSE),"---")

R13 = =IFERROR(VLOOKUP($H15,AdHoc!A:K,11,FALSE),"---")

Answer 1

问题是，如果您复制一个公式，如=IFERROR(VLOOKUP(R[10]C8,AdHoc!C[-1]:C[9],11,FALSE),"---")和FormulaR1C1，它将增加行号，R[10]C8到R[11]C8等等。其结果如下：

R1C1                                                                  A1
=IFERROR(VLOOKUP(R[10]C8,AdHoc!C[-1]:C[9],11,FALSE),"---")            =IFERROR(VLOOKUP($H21,AdHoc!Q:AA,11,FALSE),"---")
=IFERROR(VLOOKUP(R[11]C8,AdHoc!C[-1]:C[9],11,FALSE),"---")            =IFERROR(VLOOKUP($H23,AdHoc!Q:AA,11,FALSE),"---")
=IFERROR(VLOOKUP(R[12]C8,AdHoc!C[-1]:C[9],11,FALSE),"---")            =IFERROR(VLOOKUP($H25,AdHoc!Q:AA,11,FALSE),"---")
=IFERROR(VLOOKUP(R[13]C8,AdHoc!C[-1]:C[9],11,FALSE),"---")            =IFERROR(VLOOKUP($H27,AdHoc!Q:AA,11,FALSE),"---")
=IFERROR(VLOOKUP(R[14]C8,AdHoc!C[-1]:C[9],11,FALSE),"---")            =IFERROR(VLOOKUP($H29,AdHoc!Q:AA,11,FALSE),"---")
=IFERROR(VLOOKUP(R[15]C8,AdHoc!C[-1]:C[9],11,FALSE),"---")            =IFERROR(VLOOKUP($H31,AdHoc!Q:AA,11,FALSE),"---")
=IFERROR(VLOOKUP(R[16]C8,AdHoc!C[-1]:C[9],11,FALSE),"---")            =IFERROR(VLOOKUP($H33,AdHoc!Q:AA,11,FALSE),"---")
=IFERROR(VLOOKUP(R[17]C8,AdHoc!C[-1]:C[9],11,FALSE),"---")            =IFERROR(VLOOKUP($H35,AdHoc!Q:AA,11,FALSE),"---")
=IFERROR(VLOOKUP(R[18]C8,AdHoc!C[-1]:C[9],11,FALSE),"---")            =IFERROR(VLOOKUP($H37,AdHoc!Q:AA,11,FALSE),"---")
=IFERROR(VLOOKUP(R[19]C8,AdHoc!C[-1]:C[9],11,FALSE),"---")            =IFERROR(VLOOKUP($H39,AdHoc!Q:AA,11,FALSE),"---")
=IFERROR(VLOOKUP(R[20]C8,AdHoc!C[-1]:C[9],11,FALSE),"---")            =IFERROR(VLOOKUP($H41,AdHoc!Q:AA,11,FALSE),"---")
=IFERROR(VLOOKUP(R[21]C8,AdHoc!C[-1]:C[9],11,FALSE),"---")            =IFERROR(VLOOKUP($H43,AdHoc!Q:AA,11,FALSE),"---")
=IFERROR(VLOOKUP(R[22]C8,AdHoc!C[-1]:C[9],11,FALSE),"---")            =IFERROR(VLOOKUP($H45,AdHoc!Q:AA,11,FALSE),"---")

如果使用.Formula进行复制，它将使用此公式=IFERROR(VLOOKUP($H11,AdHoc!Q:AA,11,FALSE),"---")，并将$H11增加到$H12等，结果如下：

R1C1                                                                  A1
=IFERROR(VLOOKUP(RC8,AdHoc!C[-1]:C[9],11,FALSE),"---")                =IFERROR(VLOOKUP($H11,AdHoc!Q:AA,11,FALSE),"---")
=IFERROR(VLOOKUP(RC8,AdHoc!C[-1]:C[9],11,FALSE),"---")                =IFERROR(VLOOKUP($H12,AdHoc!Q:AA,11,FALSE),"---")
=IFERROR(VLOOKUP(RC8,AdHoc!C[-1]:C[9],11,FALSE),"---")                =IFERROR(VLOOKUP($H13,AdHoc!Q:AA,11,FALSE),"---")
=IFERROR(VLOOKUP(RC8,AdHoc!C[-1]:C[9],11,FALSE),"---")                =IFERROR(VLOOKUP($H14,AdHoc!Q:AA,11,FALSE),"---")
=IFERROR(VLOOKUP(RC8,AdHoc!C[-1]:C[9],11,FALSE),"---")                =IFERROR(VLOOKUP($H15,AdHoc!Q:AA,11,FALSE),"---")
=IFERROR(VLOOKUP(RC8,AdHoc!C[-1]:C[9],11,FALSE),"---")                =IFERROR(VLOOKUP($H16,AdHoc!Q:AA,11,FALSE),"---")
=IFERROR(VLOOKUP(RC8,AdHoc!C[-1]:C[9],11,FALSE),"---")                =IFERROR(VLOOKUP($H17,AdHoc!Q:AA,11,FALSE),"---")
=IFERROR(VLOOKUP(RC8,AdHoc!C[-1]:C[9],11,FALSE),"---")                =IFERROR(VLOOKUP($H18,AdHoc!Q:AA,11,FALSE),"---")
=IFERROR(VLOOKUP(RC8,AdHoc!C[-1]:C[9],11,FALSE),"---")                =IFERROR(VLOOKUP($H19,AdHoc!Q:AA,11,FALSE),"---")
=IFERROR(VLOOKUP(RC8,AdHoc!C[-1]:C[9],11,FALSE),"---")                =IFERROR(VLOOKUP($H20,AdHoc!Q:AA,11,FALSE),"---")
=IFERROR(VLOOKUP(RC8,AdHoc!C[-1]:C[9],11,FALSE),"---")                =IFERROR(VLOOKUP($H21,AdHoc!Q:AA,11,FALSE),"---")
=IFERROR(VLOOKUP(RC8,AdHoc!C[-1]:C[9],11,FALSE),"---")                =IFERROR(VLOOKUP($H22,AdHoc!Q:AA,11,FALSE),"---")
=IFERROR(VLOOKUP(RC8,AdHoc!C[-1]:C[9],11,FALSE),"---")                =IFERROR(VLOOKUP($H23,AdHoc!Q:AA,11,FALSE),"---")

不确定这是否是一个bug而不是一个特性，以及R1C1表示法是如何工作的。

此外，它看起来与复制一系列多列( Range("R11:AH" & LR).FormulaR1C1 = Range("R1:AH1").FormulaR1C1 )有关。

如果您只更改为一列Range("R11:R" & LR).FormulaR1C1 = Range("R1:R1").FormulaR1C1，它将执行OP所期望的操作。