VLookup over multiple sheets

[artsjeroen]

I'm trying to make an userform with a textbox. The value entered in the textbox is the search value for VLOOKUP.
The VLOOKUP must search for the value in two sheets and give its result in a msgbox.

I've been only able to get Err.Number = 1004 as reply.

What am I doing wrong?

Please Login or Register  to view this content.

[Obsessed]

You need error handling. Since you're looking at two sheets you either need to tell it to ignore errors (since it will always error on one of the two sheets), or you need to first check to see which sheet the data is on first. You could do countifs on each range and if countif = 1 you know it's on that sheet, and have the lookup occur on only that sheet.

[artsjeroen]

That sounds obvious. Unfortunately I don't know enough VBA to sort it out.
I do understand that I have to stop the VLOOKUP if the result has been found in the first sheet, but I have no clue how to use the Countif functions.

[artsjeroen]

Any help to get this VBA working is welcome
Can't find a question/solution on VLOOKUP in multiple sheets using VBA on this or any other forum.

The code below works only for sheet1.
but entry in sheet 2 cannot be found. And if the entry is in sheet1, and I enter a wrong batch code I don't get the msgbox "Not present"

Please Login or Register  to view this content.

[Obsessed]

Upload a workbook, so people can take a look at it.