VLookup in VBA

In my workbook, I have a worksheet named ITEMS and another named PLANTS. I am trying to get an answer in ITEMS Cell O12, accessing a reference code in ITEMS Cell O12, and using that reference to look up a value in the second column of a vertical table in PLANTS. The syntax I have used is as follows :

Sheets("ITEMS").Range("O12") = Application. WorksheetFunction. VLookup(Sheets("ITEMS").Range("AD6"),Sheets("PLANTS").Range("B3:F65"), 2,FALSE)

I have looked up the use of the Vlookup function in VBA and the above appears to be correct, but I get an error 1004.

Can anyone find a way round that error?

Hi Robert,

Thanks so much for your reply.

Unfortunately I couldn't get the routines to work.

Would it help if I attached the workbook so that you could see where my error/s are ?

Regards,

Robin

Here is the workbook.

Regards,

Robin

Thanks Robert.

I changed the option but it didn't make any difference.

Did you try?

Regards,

Robin

Sorry Robert,

Thanks so much for the help.

It is really appreciated.

Kind regards,

Robin

Hi Robert,

Me again. I had to edit your subroutine so that I could use it in two different routines but it no longer works.

What did I do wrong?


Option Explicit
Sub Find1()
Sheets("START").Select
If Range("AD6") = 1 Then GoTo Newprice
Sheets("ITEMS").Range("O12").Value = Evaluate(IFERROR(VLOOKUP(" & Sheets("ITEMS").Range("AD6").Value & ",PLANTS!$B$3:$F$65,5,FALSE),0)")
GoTo Finish
Newprice:
Sheets("ITEMS").Range("E23").Value = Evaluate(IFERROR(VLLOKUP(" & Sheets("ITEMS").Range("AD6").Value & ",CHOSEN!$B$3:$I$90,8,FALSE),0)")
Finish:
Kind regards,

Robin

Robin