1

I am getting a syntax error on this statement below that combines an If, IsError and VLOOKUP statement - I been trying everything..... anyone know why?

If Application.Worksheetfunction.IsError(VLOOKUP(cell.Value,'[Codes.xlsx]Processing Codes'!ProcessingCodesTable,5,FALSE)) = FALSE then

Thanks any help would be greatly appreciated!

Samir112
  • 125
  • 1
  • 12

2 Answers2

0

VLOOKUP is not a VBA function.

The previous question "Writing a VLOOKUP function in vba" offers multiple ways of getting around this. It looks like you can just use Application.WorksheetFunction.VLookup in place of VLOOKUP.

Community
  • 1
  • 1
Isaac Moses
  • 1,589
  • 6
  • 26
  • 44
  • Thanks I have corrected this, but I believe there is also an error with my table array syntax because if I were to replace it with a static range such as "A1:A15" the error goes away. – Samir112 Sep 03 '14 at 18:17
  • @user3242388 Whatever the range for ProcessingCodesTable is, you are asking to return the 5th column in that range – datatoo Sep 03 '14 at 18:40
  • Isn't your '[Codes.xlsx]Processing Codes'!ProcessingCodesTable a named range not a VBA variable, so needs to be in double quotes too? – Captain Sep 03 '14 at 18:41
  • yes ProcessingCodesTable is a named range in an external workbook, I have never seen an example of referencing it in double quotes? what would the syntax be to reference a named ranged in an external workbook? – Samir112 Sep 03 '14 at 20:15
0

You might be able to adapt something like this

Sub lookupPC()
On Error GoTo ErrHandler
Dim result As Variant
Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets("Processing Codes")
For Each cell In Range("a1:a100")
  If IsError(Application.WorksheetFunction.VLookup(cell.Value, ws.Range("ProcessingCodesTable"), 5, False)) = False Then
    result = Application.WorksheetFunction.VLookup(cell.Value, ws.Range("ProcessingCodesTable"), 5, False)
  End If
 MsgBox (result)
Next cell
ErrHandler:
 MsgBox (cell.Address & " value not available in table")
End Sub
datatoo
  • 2,019
  • 2
  • 21
  • 28
  • Actually the IsError function maybe should be left out and just do error handling yourself if there isn't a valid vlookup value – datatoo Sep 04 '14 at 15:19