vbaexcelvlookupexcel-r1c1-notation

activecell.formula with RC notation and vlookup not working vba

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

Solution

  • 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: