“Unable to get the VLookup property of the WorksheetFunction Class” error

Hi all, I am trying to set the values in column B in my Workbook "Copy of Dash Board Shell" (shown in the image below) equal to the value derived from my HLOOKUP function from my data workbook shown as second image below
Copy of Dash Board Shell
Capture.PNG

Data File
capture 1.PNG

so esentially the code will find a match in the data file and copy its MIS number and paste it in the desired location on the Dash Board file, heres my code, I keep getting an error because I dont know the syntax, can someone help?

Sub RetrieveData()

    Dim wbDash              As Workbook 'workbook where the data is to be pasted
    Dim wbData              As Workbook 'workbook from where the data is to copied
      
    
    'set to the current active workbook to Dashboard
    Set wbDash = ActiveWorkbook
    'Setting data sheet
    Set wbData = Workbooks.Open("C:\Users\673157897\Documents\Pro Fees Dash Board\Copy of Data.xls")
  
    Workbooks("Copy of Dash Board Shell").Worksheets("Data").Activate
       
With Sheets("Data")

    ' Selects the first cell to check
    Range("A3").Select
    Dim x As Variant
    x = wbData.Worksheets("Sheet1").Range("A2:B7")
    Workbooks("Copy of Dash Board Shell").Worksheets("Data").Range("A3").Select
   
    ' Loops through all rows until an empty row is found
    Do Until IsEmpty(ActiveCell)

        Range(ActiveCell.Offset(0, 1) & ActiveCell.Row).Value = Application.WorksheetFunction.VLookup((ActiveCell.Column & ActiveCell.Row), x, 2, 0)
        ActiveCell.Offset(1, 0).Select

' error is the syntax of the VLOOKUP

    Loop
End With

Call wbData.Close(False)
End Sub

I get another syntax error with that.... Capture.PNG

sorry wont post .PNG anymore. What file format is best?

Jpeg is probably the safest if you do need to post an image.

I have edited my previous post to show why you had the error with xladept's suggestion.

Which line was the error caused by in my code? It was a quick untested hash so it could have a couple of typos in it.

You don't need to select or activate ranges or sheets in your code, the only purpose those functions serve is to make your code slower.
Personally I would use Range.Find over calling Vlookup, something like.

Option Explicit
Sub RetrieveData()

Dim wbDash As Workbook 'workbook where the data is to be pasted
Dim wbData As Workbook 'workbook from where the data is to copied
Dim c As Range, rng As Range, rfound As Range
    'set to the current active workbook to Dashboard
Set wbDash = ActiveWorkbook
    'Setting data sheet
Set wbData = Workbooks.Open("C:\Users\673157897\Documents\Pro Fees Dash Board\Copy of Data.xls")
Set rng = wbData.Worksheets("Sheet1").Range("A2:A7")
With Workbooks("Copy of Dash Board Shell").Sheets("Data")

    ' Selects the first cell to check
    For Each c In .Range("A3", .Cells(Rows.Count, "A").End(xlUp).Row)
        Set rfound = rng.Find(what:=c, after:=rng(1), LookIn:=xlValues, lookat:=xlWhole)
        If Not rfound Is Nothing Then
            c.Offset(0, 1) = rfound.Offset(0, 1)
        Else
            c.Offset(0, 1) = "N/A"
        End If
    Next
End With
    wbData.Close (False)
End Sub

Originally Posted by jason.b75

You don't need to select or activate ranges or sheets in your code, the only purpose those functions serve is to make your code slower.
Personally I would use Range.Find over calling Vlookup, something like.

Option Explicit
Sub RetrieveData()

Dim wbDash As Workbook 'workbook where the data is to be pasted
Dim wbData As Workbook 'workbook from where the data is to copied
Dim c As Range, rng As Range, rfound As Range
    'set to the current active workbook to Dashboard
Set wbDash = ActiveWorkbook
    'Setting data sheet
Set wbData = Workbooks.Open("C:\Users\673157897\Documents\Pro Fees Dash Board\Copy of Data.xls")
Set rng = wbData.Worksheets("Sheet1").Range("A2:A7")
With Workbooks("Copy of Dash Board Shell").Sheets("Data")

    ' Selects the first cell to check
    For Each c In .Range("A3", .Cells(Rows.Count, "A").End(xlUp).Row)
        Set rfound = rng.Find(what:=c, after:=rng(1), LookIn:=xlValues, lookat:=xlWhole)
        If Not rfound Is Nothing Then
            c.Offset(0, 1) = rfound.Offset(0, 1)
        Else
            c.Offset(0, 1) = "N/A"
        End If
    Next
End With
    wbData.Close (False)
End Sub

Hey Jason, Im open to anything if you think find is better I may just use that.

I got an error though, it says "Application-defined or Object-defined error" when I run the code

Attachment 423947

Do you know how to get around this?

Hopefully xladept can answer that one, error handling is one of my reasons for using Find instead of Vlookup.

Using Find, this part traps any potential errors

        If Not rfound Is Nothing Then
            c.Offset(0, 1) = rfound.Offset(0, 1)
        Else
            c.Offset(0, 1) = "N/A"
        End If

Basically no match returns an empty variable (rFound Is Nothing) so we can tell the code what to do if the match is nothing or if it is something, there is no error involved here.
With Vlookup, no match automatically returns an error, so you have to trap in, either with more worksheet functions, or with vba On Error, both of which can get pretty messy.