CODEWITHSUNDEEP
vbaexcelexcel-formula
Alexandre Gentil
Alexandre Gentil

Reputation: 149

How do I put VLOOKUP in a cell and paste it down?

I'm using VlookUp in VBA, but it only works for one cell.

For Each MyCell In Selection
    MyCell = application.WorksheetFunction.VlookUp(Range("C2"), Range("A2:B50000"), 2 , 0)
Next

I want to set a VlookUp in a cell, then roll this to all other cells. But I am only able to put the formula in the first cell, then a double click to others.

Upvotes: 1

Views: 86

Answers (1)

SierraOscar
SierraOscar

Reputation: 17637

Try this instead:

With Selection
    .FormulaR1C1 = "=VLOOKUP(RC3,R2C1:R50000C2,2,FALSE)"
    .Value = .Value '// Comment out if you don't want to paste values.
End With

This enters the VLOOKUP() formula into each cell using a relative reference for the lookup value's row and then effectively pastes values to replace the formula.

If you really want you can use VBA syntax:

For Each myCell In Selection
    myCell.Value = WorksheetFunction.Vlookup(Cells(myCell.Row, 3), Range("A2:B50000"), 2, False)
Next

Upvotes: 1

Related Questions