macro to replace numbers in workseet with corresponding values in another worksheet

Hello Everyone, I am new to the forum. I have been working on creating few macros which I managed fine going through some old threads. Currently I am stuck with one which I can't get working.

I have Sheet1 with the values like below. ( there are a total of 127 rows)
id parent_id name
100298 100266 item 1
100297 9048 item 2
100296 item 3
100295 98 item 4
100290 100171 item 5
100275 item 6
100274 9102 item 7
100273 item 8

Sheet2 is similar to the below. (total of 2290 rows)
id
100298
100298
100298
100098|100297
100098|100297
100098|100297
100098|100297
9003|9158

Only 1 column, but has a | symbol to show multiple id values. I want to replace all the numbers in Sheet2 with the corresponding name mentioned in Sheet1 like "item 1", "item 2" etc. Can someone help me with this?

Your help is much appreciated.

Assuming that your numbers are in column A on sheet1 and item descriptions in column B, try this:

Please Login or Register  to view this content.

You are great. That did the trick. But I had to do a small change. Below is the final code.

Please Login or Register  to view this content.

Hi NickyC, can you please tell me how I can get the last row number dynamically instead of hardcoding B127?

If your values are always in columns A and B, this should work

Please Login or Register  to view this content.

Thanks very much. That works.

Hi NickyC, I am finding one problem. For example if I have 1000 and 100 as two numbers, if 100 comes first, it is replacing the "100" in 1000.
Can you please let me know how it can match the entire cell?

try LookAt:=xlWhole in the search line

That worked. Thanks very much.