Menu
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)
357
asked Jun 18 '21 06:06
If you believe that the data is present in the spreadsheet, but MATCH is unable to locate it, it may be because: The cell has unexpected characters or hidden spaces. The cell may not be formatted as a correct data type. For example, the cell has numerical values, but it may be formatted as Text.
If you have previously ignored any errors, you can check for those errors again by doing the following: click File > Options > Formulas. For Excel on Mac, click the Excel menu > Preferences > Error Checking. In the Error Checking section, click Reset Ignored Errors > OK.
Preventing #NAME Errors The best way to prevent the #NAME error in Excel is to use the Formula Wizard. When you begin typing a function name in the formula bar, a series of choices will appear in the drop-down. Instead of continuing to type, you can avoid misspelling errors by selecting a function name from the list.
This page shows an example of a dynamic named range created with the INDEX function together with the COUNTA function. Dynamic named ranges automatically expand and contract when data is added or removed. In the example shown, the named range "data" is defined by the following formula: =$A$2:INDEX($A:$A,COUNTA($A:$A))
The column "description" could be the 2nd column in the index or the 50th column in the index. Anyones help would be greatly appreciated. Adjust the INDEX Array (A:Z) to fit your data accordingly. Thank you - Works perfect! Adjust the INDEX Array (A:Z) to fit your data accordingly. Click to expand...
The same Index/Match formula with static range names works perfectly. The range names are worksheet specific. The following is the problem formula and the respective range names: This is the same formula / range names with a “2” suffix using static range names which works fine. Appreciate any help resolving this issue. Thanks for reading.
A #NAME? error occurs if you attempt to use a dynamic array function in an older version of Excel. Please remember that the new functions are only available in Excel 365 and Excel 2021.
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)"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
