excel - Writing a VLOOKUP function in vba

Question

I'm trying to lookup a value on a spreadsheet within a table array using the VLOOKUP function in my vba code. I don't know how to write it correctly.

Here is the normal VLOOKUP formula with all the references:

=VLOOKUP(DATA!AN2,DATA!AA9:AF20,5,FALSE)

Answer 1

Have you tried:

Dim result As String 
Dim sheet As Worksheet 
Set sheet = ActiveWorkbook.Sheets("Data") 
result = Application.WorksheetFunction.VLookup(sheet.Range("AN2"), sheet.Range("AA9:AF20"), 5, False)