|First, I don't know if I have told you about this tutorial before. These debugging techniques might help you tweak the code:|
As for your latest questions...
re: I was thinking you said in your earlier response that if there is a cell in Sheet1 that has a catalog number of U87295, the macro will eventually remove the 5 and search Sheet2 for U8729.
Yes, that is correct.
re: Now, if there is a catalog number in Sheet2 of U87294, even though there is a 4 at the end, it should still treat that as a match and replace the cell in Sheet1 with the U87294.
No, that is not correct, at least not as the code is currently written.
The code is only modifying the values in Sheet 1 and then looking for a exact match of that modified value in the list on Sheet 2. It is doing nothing to the values on Sheet 2, so U87294 is not a match for when searching for U8729 - as the code is currently written.
There was nothing in your original requirements that indicated that you want something like U87295 replaced with U87294. In fact, you specifically asked for exact matches of the modified values.
re: Sort of like, even if it searches for U87, it will still find a match for the first catalog number in Sheet2 that starts with U87.
The VBA .Find method uses the Find feature in Excel. One of the options of the Find feature is "Match entire cell contents". In VBA, that is handled by setting the "lookat:=" argument to xlWhole.
You could try changing all 4 occurrences (or whichever ones you think are appropriate) of xlWhole to:
If xlPart is used, then U87 would be a match for any value that contains U87 anywhere in the cell, e.g.
Take the U87 exit
If you only want U87 found at the beginning of the cells being searched, things get a bit more complicated. I don't think that you can use .Find in the manner we currently are. You would need to parse out the first 3 characters of each value on Sheet 2 and compare them to the first 3 characters of the search string. That would need to be done on a cell by cell basis or with a helper column or via something other than a simple .Find against the existing Sheet 2 list.
The following is not really related to your issue, but just a fun fact.
You may have noticed that when you write VBA code, the editor will capitalize any built in function or argument. For example, if you type this and then hit enter...
if not e2 is nothing then
the VBA editor will change it to:
If Not e2 Is Nothing Then
The VBA editor knows that e2 is a user created variable and that all the other words "belong" to VBA.
OK, now enter this in VBA and press enter.
set e2 = .find("Bob", lookin:=xlvalues, lookat:=xlwhole)
Look carefully at what did not get capitalized, other than e2. That bug has been part of VBA for as long as I can remember. 20+ years.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.