Find value in Workbook2 from value in Workbook 1

I have recorded a Macro from one Workbook to the other, which provides the following code,

Please Login or Register  to view this content.

When I incorporate it into my code it throws up the following error,

Run-time error:
Application-defined or object-defined error

The only additional line I have is the following, to allow for opening the "Test tracker" Workbook if closed.

Please Login or Register  to view this content.

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

I've read this thread before and began to suspect that the code was run from the sheet module. And it looks like I wasn't wrong.
A lot of errors are in this code.
Only in this line:

Please Login or Register  to view this content.

I counted as many as 5 of them.
As I mentioned earlier, the code is run from the sheet module. The lack of full qualifiers causes the machine to interpret Cells as an object in this sheet. That is, an attempt is made to search for the Reference value (that is, 1) in the Memo sheet, starting from the active cell (but in the Tracker sheet!) and if the value was found it is supposed to activate the found cell in the Memo sheet when the Tracker sheet is active. You may have to read the previous sentence multiple times to understand what is happening.
In addition, you should not look for the fragment in the cell values, but the value of the whole cell. So you can't use xlPart. If the searched value was not found, the Activate method would cause an error.

A more correct code could look like this:

Please Login or Register  to view this content.

Note also that the code does not use cell C5.

Artik

Untitled.png

Please Login or Register  to view this content.

................................
THANKS.gif

Originally Posted by AndyEd

I've gone with sintek' solution as it contains the least amount of code.

Aha, so it's supposed to be as short as possible.
Here you go

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

However, programming is not about coming up with the shortest code possible, because not every shorter code executes for less time. It all depends on the methods used.
In addition, often shorter code is unreadable and more difficult to modify. This remark just doesn't apply to your case. Sintek wrote basically the same code as I did, only shorter. As you can see, you can still be shorter.
Only that these short codes will someday not work properly. There may not be values or they will be wrong in cells B3 or B5, the "Test Tracker" workbook may not be in the defined location, the "Trucker" sheet may have a different name or not be the first sheet in the workbook, the target sheet may be protected, etc, etc.
My "long" code at least somewhat protects the execution from unexpected error. No, it's not perfect, but it's safer.
So in the future, don't look at whether the code is short, you just need to analyze it in terms of the method used (whether it is the best you know) and whether the code is protected from unexpected errors. Many times it happens that security features take up more code than the main task to be performed.

Artik