error 1004 replace method of range class failed

Hi all,
First time poster here, very much a VBA novice- hoping you can help me out!
I have a sheet of 64227 lines. I've used a substitute formula to get down to a list of categories in column J.
Because of the substitute formula, each cell contains duplicate values: eg category one, category 1, category 1, category 2.
I need these cells to just display "category one, category two".
I've been manually finding and replacing but the scale of the sheet is making this unmanageable.

So I've created a find and replace macro using this formula:

Please Login or Register  to view this content.

But when I click "Run Macro" it converts some values, but not others.
Debugger points to this line : myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 1).Value, LookAt:=xlWhole

But I can't work out what's causing the problem there.
I've googled for hours, can anyone shed light on this for me?
Thanks!

So, I don't know enough VBA to tell you why you're getting the error, but if you post a sample workbook, I'd be willing to try a solution.

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between

Please Login or Register  to view this content.

tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

When you run your macro and it errors out and you look at the highlighted code, what cell has the code stopped at. There is probably some incompatibility with the cell that halts the code. Posting your sheet as K64 would help. Like a doctor trying to diagnose a patient. If he doesn't see the patient, it is difficult to determine what is wrong.

I've looked at your file, but don't understand what you are trying to accomplish. Instead of telling us what you have done, please tell us what your objective is because that is not at all clear from the information shown. The code refers to column J, but there is no data in column J, so I'm guessing you changed things up as well.

How do you know which ones is the corresponding value?

Yes, except that none of the values match.

Put this in B2, enter it with ctrl-shift-enter, and drag it down. There are multiple matches. This grabs the last match. Normally, MATCH() is the perfect function for this, but I think the strings are too long for it.

Formula:

Please Login or Register  to view this content.

Here, try this

Please Login or Register  to view this content.

In the loop, I'm matching column 1 to 1 and replacing 2 with 2. You can change those column numbers as needed.

Sent from my SPH-D710 using Tapatalk

UBound(arr1, 6) should be UBound(arr1, 1), which means "the upper bound of the 1st dimension (rows) or arr1.