Search and Replace Macro debug help

[TheChristige]

Hello everyone, I have another simple (I hope question). As I am new to VBA I am not very good at debugging my code (in this case it is not a bug rather it is working but not as intended). What I would like this macro to do is replace all instances of their with his, them with he and they with his. I defined what to look for in cells T35:T37 and to replace these with in cells U35:U37. The problem is it replaces every instance in the worksheet but I would only like it to find a replace in cell M14 (which is actually 3 merged cells, M14:M16). I thought the line "Worksheets("Comments Prototype").Range("M14").Select" limit the replace to the Range("M14") but it does the entire workbook instead. Please help me limit the function of this macro to a single cell! Thank you! <3

Gradebook Prototypes.xlsm
	T
35	their
Comments Prototype

 

[TheChristige]

Sorry here is the proper mini-sheet

Gradebook Prototypes.xlsm
	M	N	O	P	Q	R	S	T	U
13	Comment Creator:		Vocabulary
14			4	excellent	mastery	perceptive	insightful
15			3	good	well-developed	clear	well-reasoned
16			2	basic	developing	adequate	concrete
17			1	not meeting	beginning	vague	undeveloped
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33								Find and Replace
34								Find	Replace
35								their	his
36								them	him
37								they	he
38								his	her
39								him	her
40								he	she
Comments Prototype

 

[Joe4]

This is actually a bit trickier than meets the eye.
The issue is if you search and replace a word like "he", you are going to pick up any word that has "he" in it, which is going to cause lots of problem (also true for your other search terms, though "he" is probably the most common on your list).

I think I might go about this project in a different manner. I am geussing that this may not be a "one-time shot", and you may want to change these "on-the-fly" depending on the person that you want to apply them to. If that is the case, I would recommend making a table of pronouns to use for each case, and then decide which one to use based on a selection.

For example, let's say that in column A you have Male pronouns, and in column B you have Female pronouns. Then, we can make a formula in column C to determine which one to return,.
For example, let's say that in C1 we enter whether it to use Male or Female.
Then for row 2, we can use this formula in column C:

=IF(C$1="Male",A2,IF(C$1="Female",B2,""))

and copy down for the rest of the rows.

Then, in your bodies of text, you can reference these cells, for example:

="In Math class, " & C2 & " performed well."