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

[lougs7]

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

Data file

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?

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">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

</code>

 

[JoeMo]

This:<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">

Range(ActiveCell.Offset(0, 1) & ActiveCell.Row)

is not a valid range object.
</code><code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">
</code>

 

[lougs7]

This:<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">

Range(ActiveCell.Offset(0, 1) & ActiveCell.Row)

is not a valid range object.
</code><code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">
</code>

I cahnged it to this...

"Range(ActiveCell.Offset (0,1).Value=WorksheetFunction.VLookup(ActiveCell, x, 2, 0)"

and I stillg et an error with syntax, do you know how to do this?

 

[JoeMo]

I cahnged it to this...

"Range(ActiveCell.Offset (0,1).Value=WorksheetFunction.VLookup(ActiveCell, x, 2, 0)"

and I stillg et an error with syntax, do you know how to do this?

Try like this:

ActiveCell.Offset (0,1).Value=WorksheetFunction.VLookup(ActiveCell, x, 2, 0)