0

I need to somehow identify duplicates of a single word within a phrase, within an Excel cell, throughout the entire sheet. Identical to the thread at this link: How to identify duplicates within text in two columns in Excel

However, I tried to follow the instructions by commenter Rosenfeld, and got to the step to insert a module. I copied and pasted the code he said to, but from there, I don't know what to do. There is no option to "save" or "enter" the module, and it is not automatically applying. Unfortunately, I just stumbled upon this post and this website, so I do not have sufficient reputation to comment on the above thread, and have to create my own question.

I should note that i do not need to remove duplicate words. If anything, I would like to highlight or bold them, but will settle for some other indicator pointing out which cells contain duplicates, and I can figure it out manually from there. Also, I have Excel 2016, on a PC.

Thank you!

C S
  • 1

1 Answers1

1

How do I add VBA in MS Office? is “our” standard reference for using VBA in Microsoft Office (i.e., Excel, Word, PowerPoint, …), but it looks like it doesn’t cover your (quite reasonable) beginner questions.  So,

  • You don’t need to save the VBA routine explicitly or separately.  As long as you selected the current Excel file in the Project pane (the narrow panel on the left) in the Visual Basic editor, and your module is listed under your Excel file, your code is associated with the Excel file, and so it will be saved when you save the Excel file.  You will need to “Save As” a “Macro-Enabled Wokbook (*.xlsm)”.  (Excel will tell you this if you try to do an ordinary “Save” with a “.xlsx” extension.)

    In the future, when you open your “.xlsm” file, Excel will ask you whether to enable the macro(s).  As long as you haven’t let anybody else modify the file, you should click “Enable”.

  • Ron Rosenfeld’s answer* shows how to use the VBA code (although it doesn’t explain it).  Just type =WordMatch(arg1, arg2) (for example, =WordMatch(A1, B1)) into a cell where you want the result of the function.

    In general, if your VBA code contains 

    function function_name(args)
    then you can use function_name(args) in formulas in your Excel worksheet.

________________
* and that is an answer, not a comment

Scott - Слава Україні
  • 22,532