Referencing loop in actual formula

[Excel Jr]

Hi,

I'm not sure how to reference the loop to where it changes not only the cell it's pasted in, but also the formula itself.

The formula I'm trying to loop:

Sheets("Inputs").Range("D11").Formula = "=IFERROR(VLOOKUP(A11,LoadAnswerFields!E:F,2,FALSE),"""")"

My Best Guess:

Dim i as long

For i = 11 To 42
    Sheets("Inputs").Range("D" & i).Formula = "=IFERROR(VLOOKUP(A & i,LoadAnswerFields!E:F,2,FALSE),"""")"
Next i

Thanks!

 

[Frank_Excel]

Dim i As Long

For i = 11 To 42
    Sheets("Inputs").Range("D" & i).Formula = "=IFERROR(VLOOKUP(A" & i & ",LoadAnswerFields!E:F,2,FALSE),"""")"
Next i

 

[Joe4]

If you get your formula in relative reference (R1C1 format), this should be easier to do.
The easy way to get that is to turn on the Macro Recorder, and record yourself manually entering the formula in the first cell in your range.

The nice thing about using the R1C1 format is that you can apply the formula to your whole range at once without a loop, i.e.

    Range("D11:D42").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-3],LoadAnswerFields!C[1]:C[2],2,FALSE),"""")"

Anytime you can avoid loops, it is a plus, as loops tend to be slow and are memory hogs. So eliminating them should increase macro performance.

 

[Excel Jr]

Thanks for the tip! However, I'm a bit confused as I haven't used R1C1 before. Do these mean the same thing?

Sheets("Inputs").Range("D" & i).Formula = "=IFERROR(VLOOKUP(A" & i & ",LoadAnswerFields!E:F,2,FALSE),"""")"

Range("D11:D42").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-3],LoadAnswerFields!C[1]:C[2],2,FALSE),"""")"

Does "RC[-3]" basically say take the value from the Range defined earlier ("D11"), go 3 columns to the left and keep the row, making it A11. Then C[1] takes the value from the same Range and only provides the column value 1 to left? And it won't have to loop because it adjusts based on the range.

Thanks for all of the help! Everyone on this forum is awesome. However, I have some serious issues after copying/pasting anything in the reply box.

 

[Joe4]

If the numbers is in square brackets, it means it is relative to the cell the formula appears in.
So, if the formula is in D11, RC[-3] means stay in the current row, and go three columns to the left.

If there are number not in brackets, it is an "absolute" reference, i.e. RC3 would mean third column in the current row.

See here for a deeper explanation: https://excelchamps.com/blog/r1c1-reference-style/