excel - 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),"---")

不确定这是否是错误而不是功能以及 R1C1 表示法的工作原理。

此外，它看起来与复制多个列的范围有关 Range("R11:AH" & LR).FormulaR1C1 = Range("R1:AH1").FormulaR1C1如果只更改为一列 Range("R11:R" & LR).FormulaR1C1 = Range("R1:R1").FormulaR1C1它符合 OP 的预期。