Could someone please look at this VLookup...

[London_Calling]

.. before I go slightly mental and sit in the corner rocking backwards and forwards:

Dim PR As Range
Set PR = Workbooks("codeBookNew.xlsm").Worksheets("Sheet1").Range("B2:C35")
finalArr1(x, 3) = Application.WorksheetFunction.VLookup(Cells(i, 6), PR, 2, False)

It's generating 'Unable to get the Vlookup property of the WorksheetFunction Class'. I just can't see the error.....

Most grateful if someone can. Cheers.

 

[VoG]

That means that it isn't finding a match. This should suppress the error message but you'll still get an error value returned

Dim PR As Range
Set PR = Workbooks("codeBookNew.xlsm").Worksheets("Sheet1").Range("B2:C35")
finalArr1(x, 3) = Application.VLookup(Cells(i, 6), PR, 2, False)

 

[London_Calling]

Of course!

You've saved my sanity yet again, Peter. Thanks very much (again!).

 

[London_Calling]

jonmo1 - I did (eventually) check the Dim statements because I've made that error before. But Peter is quite right, it's not finding a match.

Thanks for your interest!

 

[Jonmo1]

To prevent from debugging upon #N/A errors, And eliminate #N/A errors from appearing in the array...

1. drop the worksheetfunction
2. assign the result of the Vlookup to a variable.
3. if the variable is not an error, assign the variable to the array.

Try like

Set PR = ThisWorkbook.Worksheets("Sheet1").Range("B2:C35")
MyVariable = Application.VLookup(Cells(i, 6), PR, 2, False)
If Not IsError(MyVariable) Then
    finalArr1(x, 3) = MyVariable
End If

 

[London_Calling]

Thanks jonmo, that's just what I was asking.....

 

[VoG]

Maybe like this or jonmo's suggestion

Dim PR As Range
Set PR = Workbooks("codeBookNew.xlsm").Worksheets("Sheet1").Range("B2:C35")
If Not IsError(Application.VLookup(Cells(i, 6), PR, 2, False)) Then finalArr1(x, 3) = Application.VLookup(Cells(i, 6), PR, 2, False)

 

[London_Calling]

Much to ponder as always. Thanks very much to you both. I now need a walk in the sun.....

Cheers!