VBA macro for stop words in seperate sheet

[Simone_]

I found this excellent post how to create a macro to clean text of stop words. http://www.excelforum.com/excel-gene...-in-excel.html and found how add more stop words by referencing to another excel sheet. When I run the code I don't get an error message but the stop words are still in the file. I am a bit lost here because it doesn't give me an error message. I checked that the cells are formated as text, but still no solution.
I am using Excel 2013. I cannot attach the file because it is to big with 1.12MB and I deleted the majority of the cells. This is the macro code I am using:

Please Login or Register  to view this content.

Many thanks!
Simone

[alansidman]

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

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] 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.)

[skywriter]

I cannot attach the file because it is to big with 1.12MB and I deleted the majority of the cells.

You can zip the file and upload it.

[stanleydgromjr]

Simone_,

Thanks for the Private Message, and, confidence.

I am using Excel 2013. I cannot attach the file because it is to big with 1.12MB

It was suggested that you ZIP the workbook. I have had bad experience in the past, and, will not accept/open a zipped file.

I can not find my old instructions that will explain how to attach a smaller file.

But, you can post your workbook/worksheets to the following free site (sensitive data changed), and provide us with a link to your workbook:

https://dropbox.com

[alansidman]

Simone:

Attach a sample workbook. It does not need to be the entire file. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Simone_]

Thanks AlanSidman and Skywriter for the suggestions re file size.
I already downsized my Excel sheet with the results by more than half, but the file size didn't go down much. After the second error message from the up-load centre I decided to not pursue the file size issue. In hindsight I could have cut down my vlookup table. And ZIPing is certainly a good idea too! But my first and foremost problem is the macro and not the file size. I guess that is why I focused on the macro question instead of reducing the file size.
Thanks for the information re code posting. Duly noted.

[Simone_]

Enclosed the file :-)
I would need to use about 200 stop words and the raw data will be approx 48 rows x 50 columns in case that matters.
Many thanks,
Simone

[jindon]

Not really sure though...
Do you just want to clear the cells which match to the stopword?

Please Login or Register  to view this content.

[Simone_]

Hi Jindon
Yes that is exactly what I like to do. Quickly tried your code and it worked in both the test and the real data file. Though one thing I noticed is that it doesn't make a difference between 'as' and 'was'. Instead of replacing 'as' with nothing it replaces 'was' with nothing, i.e. leaving 'w..' Is there a way I can tell the macro to only replace exact word matches? I.e. a "false statement"?

Thank you very much! / "Arigato goza-maas"
Simone

[jindon]

Though one thing I noticed is that it doesn't make a difference between 'as' and 'was'. Instead of replacing 'as' with nothing it replaces

It is impossible to me.

It only clears the cell(s) with exact match, so it should not replace "was", unless you have "was" in the stopword list...

[Simone_]

Thanks for clarifying Jindon. I will have another try in a new Excel file to make sure that there is no other code somewhere influencing this macro.