How to extract values from VLOOKUP via VBA in Excel?

Question

In one sheet in Excel (called March 19), I want to use EACH cell (from range A5:A59) as a lookup value against another sheet in Excel. Then, when it's done I want to paste or extract what's been found into March 19 sheet (side-by-side with their lookup values).

I've looked into several solutions online, but my problem is that I can't seem to write the code for pasting the values found through vlookup? Look at the last line before End Sub.

My knowledge of VBA is pretty basic, and I'm still learning through tutorials :) Your help will be greatly appreciated.

Sub CopyNumbers() 
    Dim account As Long, aop As Long 
    account = Worksheets("March 19").Range("A5:A59") 
    For each c1 in account 
    aop = Application.WorksheetFunction.Vlookup(c1,Worksheets("AOP").Range("A5:P39"),6,FALSE) 
    Worksheets("March 19").Range("D5:D59") = aop
End Sub

Tim Williams Tim Williams · Accepted Answer · 2019-04-29T05:11:15

Try something like this

Sub CopyNumbers() 
    Dim c As Range, aop As Variant  
    For each c in Worksheets("March 19").Range("A5:A59").Cells
        'drop the Worksheetfunction and you won't get a run-time
        '  error if there's no match
        aop = Application.Vlookup(c, Worksheets("AOP").Range("A5:P39"),6,FALSE) 
        c.Offset(0, 3).Value = IIf(IsError(aop), "No Match", aop)
    Next c
End Sub

How to extract values from VLOOKUP via VBA in Excel?

1 Answers