Trouble using Vlookup from another workbook

Question

I'm intending to make some work more or less automatic and for that am thinking to use Excel VBA. Basically I want a separate workbook to fetch data from some different workbooks.

For this I have tried:

Dim openWb As Workbook
Dim openWs As Worksheet

Path = Dir("PathToFile\File.xlsx")
filepath = "PathToFile"

Set openWb = Workbooks.Open(filepath & Path)
Set openWs = openWb.Sheets("Sheet1")

currentwb.Sheets("Sheet2").Cells.(2, 20).Value = openWs.VLOOKUP("John",openWs.Range("A1:S30"), openWs.Match("Age", openWs.Range(A1:S1))

What I want to achieve is basically to fetch a value from the row corresponding to "John" and the column corresponding to "Age". I'm using Match with a rather big range (instead of just one column) in order to avoid a situation where someone else alters the file would cause any problem.

This gives me Syntax error on the row:

Value = openWs.VLOOKUP("John",openWs.Range("A1:S30"), openWs.Match("Age", openWs.Range(A1:S1))

I assume that it has to do with how I use "openWs" and such inside the VLookUp argument. Could anyone identify where I have thought wrong?

I am using Path and filepath as variables as the program will later have a new Path and a new filepath every month, and this is the best way I know to achieve that (basically, the path and filepath consists of month names and year names which I contatenate based on today's date).

Answer 1

Replace all of the "openWs" in front of Match and VLOOKUP with "Application" so that it reads:

Value = Application.VLOOKUP("John",openWs.Range("A1:S30"), Application.Match("Age", openWs.Range("A1:S1")))