Posts by KjBox

What are you trying to match in what range?

Not sure why you are getting that error. Here is a test file I made with the code, click the button.

Attach your workbook

I no longer have the file that you originally posted so I can't see exactly what changes you have made, so I am stabbing in the dark a bit here!

Try changing the entire last With.....End With to

Try this

Change that line of the code to

It needs to be, because the code will compare the text in A2 with the text in A3, then compare A3 with A4 and so on.

I can include a bit of code that will sort the data by Column A before the comparing code runs.

Will Column A always be sorted alphabetically?

OK, I understand the logic now (I think!), I will work on a code using scripting dictionary

I cannot understand the logic you used to get your "Desired Result".

You extract "hexacosafluoro" from "hexacosafluoro", but nothing from "hexacosafluorohexadecahydrofluoranthene";

You extract "hexadecafluorod" from "hexadecafluorodecane", but nothing from "hexadecafluorononyl" (why not "hexadecafluoron"?);

You extract "hexadecafluoroo" from "hexadecafluorooctane", but nothing from "hexadecafluorooctyl";

and so on.

Any formula or VBA solution has to work with welldefined logic, can you explain the logic of what should be extracted and what should be ignored.

Try this

It will work for just the rows in Column Q that contain data, so can extend beyond 1000 rows if your database grows in the future.

It is also an array based code that will run faster than an object based code, especially if the database is large.

To answer the first part, use

or is you truly mean you want to convert numbers to text (not recommended as formulas would not work) then use

As for the second part do you mean you want to save the file as a .csv file using pipe as the delimiter?

Your explanation and code do not match. Do you want to copy the names from Column R and paste in Column P when Column Q is "Group By", leaving the "Group By" still in Column Q?

First click the "Edit" box at the bottom right of your post, and select "Edit Post". Then highlight your code, scroll to the top of the page and you will see a blue menu bar, the </> is the 3rd from the right of the top row. Click that then scroll to the bottom and click save.

FYI the rolling eyes are nothing to do with you, the caption at the bottom of the picture actually says "Borneo VLOOKUP", the caption showed fine in the old OZgrid site but not here.

In future when you reply to a post or create a new post you will see the same menu bar at the top of the Reply window, or the new Post window.

Try this

After 82 posts you should know that any code has to be enclosed in code tags.

I have done it for you in this instance.

In future, please highlight the code and click </> in the menu bar.

You're welcome

Try this

Change this bit of code

to

you use an additional line of code for example