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

 

[jmthompson]

It's happening because one of your vlookup's is unsuccessful, can't find what you are looking for...

 

[Legacy 93538]

Hi

Thanks for replying!

Its erroring on the first vlookup statement, when writing it i was unsure of the syntax of the vlookup is it right?

 VLookup(cell.Value, PPNew.Sheets("ConvertManual").Range("A2:Y3000"), 10, False)

 

[jmthompson]

Syntax is never my strong suit, but I think you can use cell, instead of cell.value. I think it is also Application.vlookup and not application.worksheetfunction.vlookup

 

[VoG]

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 http://www.dailydoseofexcel.com/archives/2004/09/24/the-worksheetfunction-method/

 

[Legacy 93538]

Hi

Ok i tried application.vlookup and i got a syntax error of "Expected list seperator or )"

 

[VoG]

Odd. The syntax looks fine to me (and the code isn't highlighted in red in the VBE).

 

[Legacy 93538]

Hi

I have been looking at it and i have altered the code and its no longer in a loop and it works now except when it puts the vlookup in the cell i get #N/A instead of a value.

Range("F2").Value = "=If(A1 = 0, 0, VLookup(A1, ConvertManual!A2:Y3000, 6, False))"

I have checked a value in cell A1 is in the range i am asking it to look in.

 

[VoG]

You might have numbers in one sheet and text in the other. See http://www.contextures.com/xlFunctions02.html#trouble

Do you still get #N/A if you try the VLOOKUP as a formula entered manually?

 

[Legacy 93538]

Hi

The range its looking at has both text and numbers.

Would it matter if the column the value it is looking up in is after the column its trying to return data from?