Range.R1C1 Incrementing by 2

user_10587052 0
Viewed 52

Trying to fill down some formulas, tried using the .FillDown but it's quite slow compared to using the below function.

However, the issue is that my formula =IFERROR(VLOOKUP($H11,AdHoc!A:K,11,FALSE),"---") is incrementing by 2 on $H11

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

Expected Results:

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

Actual Results:

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

excel-formula vba excel
1 Answers

The issue is if you copy a formula eg =IFERROR(VLOOKUP(R[10]C8,AdHoc!C[-1]:C[9],11,FALSE),"---") with FormulaR1C1 it will increase the row number R[10]C8 to R[11]C8 and so on. Resulting in the below:

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

While if you copy down using .Formula it will use this formula =IFERROR(VLOOKUP($H11,AdHoc!Q:AA,11,FALSE),"---") and increase the $H11 to $H12 and so on resulting in:

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

Not sure if this is a bug rather than a feature and just how R1C1 notation works.

Further it looks like it has to do with copying a range of multiple columns Range("R11:AH" & LR).FormulaR1C1 = Range("R1:AH1").FormulaR1C1

If you change to only one column Range("R11:R" & LR).FormulaR1C1 = Range("R1:R1").FormulaR1C1 it does what the OP expected.

user_32196130
Related