I have this code and I cannot get it to work with RC notation
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],ARTICULOS!$A:$I,4,FALSE)"
The value that I'm looking for is 1 column after the "activecell".
Asked
Active
Viewed 1,098 times
1
JUAN CARLOS PERON
- 17
- 6
-
You're mixing both [R1C1 and A1 notation](https://bettersolutions.com/excel/formulas/cell-references-a1-r1c1-notation.htm). Why not stick with one or the other? (preferably A1) – ashleedawg Sep 03 '18 at 21:58
-
2You cannot mis r1c1 notation and a1 notation. Can get the second argument to C1:C9 – Scott Craner Sep 03 '18 at 21:58
-
because i need to generalize the formula, but the data is in other sheet. How can I do it? – JUAN CARLOS PERON Sep 03 '18 at 22:18
1 Answers
1
As @Scott pointed out, you cannot mix R1C1 and A1 notation.
Whatever it is you're trying to do, there is probably a more efficient way to accomplish it, but for now, this should give roughly your desired result: a VLOOKUP formula with a lookup_value of whatever is in one cell to the right of the ActiveCell.
ActiveCell.Formula = "=VLOOKUP(INDIRECT(ADDRESS(ROW(),COLUMN()+1)),ARTICULOS!$A:$D,4,FALSE)"
Note that I also changed the lookup_range to stop on column D because if you're looking in the 4th column starting with A, then D would be the last needed column.
Also note that I removed R1C1 since it's not needed here.
There's also probably a better method that using INDIRECT(ADDRESS(ROW(),COLUMN()+1)) but I can't think of it just now, and this way works too.
More Information:
- Office Support :
INDIRECTfunction (Excel) - Office Support :
ADDRESSfunction (Excel) - BetterSolutions :
A1orR1C1Notation - Office Support : Create or change a cell reference
ashleedawg
- 20,365
- 9
- 72
- 105