Dynamic Column Index Name Error in Formula

I am trying to calculate the component q'ty from Sheet Plan to Sheet Result by using Vlookup to fill in column,having:

• Count_col: is the total column in sheet Plan
• Last_col: is the total column in sheet Result (before add column Demand)

Sheet Results

Sheet Plan

    For i = 1 To count_col
        Cells(1, i + last_col).Value = "=Plan!RC[-2]"
        Cells(1, i + last_col).Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,Plan!C1:C15,i+1,0)"
    Next i

But the result is name error, I checked the spelling of function is correct and dont know how to fix. Why there is "@" in my formula?

Detail of error

=VLOOKUP($A2,Plan!$A:$O,@i+1,0)

i+1 inside "" behaves like a string rather than a variable. Try this.

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,Plan!C1:C15," & i + 1 & ",0)"

Also avoid the use of .Select. Your code can be written as

Cells(1, i + last_col).Offset(1, 0).FormulaR1C1 = _
"=VLOOKUP(RC1,Plan!C1:C15," & i + 1 & ",0)"

Recommended Reading: How to avoid using Select in Excel VBA

Also you are mixing R1C1 and A1 style of referencing. I would recommend using one of them. A simple Google search R1C1 vs A1 style will explain what they are.

In R1C1, Plan!C1:C15 needs to be written as Plan!R1C3:R15C3. So your final code would be

Cells(1, i + last_col).Offset(1, 0).FormulaR1C1 = _
"=VLOOKUP(RC1,Plan!R1C3:R15C3," & i + 1 & ",0)"