Dynamic Column Index Name Error in Formula

Question

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

Material	Component	Demand W1	Demand W2
ABCD1000	nc200	#NAME?	#NAME?

Sheet Plan

Material	Demand W1	Demand W2
ABCD1000	1000	200

    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)

Siddharth Rout · Accepted Answer · 2021-06-18 06:33:14Z

5

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

edited Jun 18, 2021 at 6:33

answered Jun 18, 2021 at 6:23

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic Column Index Name Error in Formula

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related