5

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)
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Suzie
  • 103
  • 5

1 Answers1

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)"
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250