unable to get the vlookup property of the worksheetfunction class

[Legacy 93538]

Hi

I have created this loop, which loops through a cell range and for cell it takes the value of the cell and uses it to place a vlookup in the cell in the columns 6, 10, 21, 22, 23.

However when i run the code it produces an error saying "unable to get the vlookup property of the worksheetfunction class"

Nrow = 2If bImport = vbYes Then 'if the user selected yes to the question at the start
    'Opens perious version doc entered and copy data into temp sheet and closes it
    Application.StatusBar = "Importing previous version data"
    Set PPOld = Workbooks.Open(StrFldr & "\" & "HDE_PPIII_MONTH_Input_Reference_Form_V" & bVImport & ".xlsx")
    PPNew.Sheets.Add.Name = "ConvertManual"
    PPOld.Sheets("InputRefapd").cells.Copy Destination:=PPNew.Sheets("ConvertManual").Range("A1")
    PPOld.Close
    'Loops through column in
    For Each cell In PPNewIR.Range("A2:A3000")
        If cell.Value <> "" Then
           PPNewIR.cells(Nrow, 6).Value = Application.WorksheetFunction.VLookup(cell.Value, PPNew.Sheets("ConvertManual").Range("A2:Y3000"), 10, False)
           PPNewIR.cells(Nrow, 10).Value = Application.WorksheetFunction.VLookup(cell.Value, PPNew.Sheets("ConvertManual").Range("A2:Y3000"), 10, False)
           PPNewIR.cells(Nrow, 21).Value = Application.WorksheetFunction.VLookup(cell.Value, PPNew.Sheets("ConvertManual").Range("A2:Y3000"), 21, False)
           PPNewIR.cells(Nrow, 22).Value = Application.WorksheetFunction.VLookup(cell.Value, PPNew.Sheets("ConvertManual").Range("A2:Y3000"), 22, False)
           PPNewIR.cells(Nrow, 23).Value = Application.WorksheetFunction.VLookup(cell.Value, PPNew.Sheets("ConvertManual").Range("A2:Y3000"), 23, False)
        End If
        Application.StatusBar = cell.Address
    Next cell
Application.StatusBar = "Formatting and deleteing temporary Sheet"
PPNewIR.Select: PPNewIR.cells.Copy: PPNewIR.PasteSpecial Paste:=xlPasteValues
PPNew.Sheets("ConvertManual").Delete
End If

Does anyone know why its doing this and how to solve it? as i have never come across this error before.

Thanks

Jessicaseymour

 

[Legacy 93538]

Hi

I have figured it out, it was because the column i was referencing was not the column in the range. But it is now so its working fine.

Thanks to all who helped me!

 

[alfranco17]

Using WorksheetFunction will cause a VBA error if VLOOKUP returns #N/A. Try

PPNewIR.Cells(Nrow, 6).Value = Application.VLookup(cell.Value, PPNew.Sheets("ConvertManual").Range("A2:Y3000"), 10, False)

See here for a discussion Daily Dose of Excel » Blog Archive » The WorksheetFunction Method

Thanks. It was driving me nuts.