vba to correct spelling mistakes of pre-determined words using wildcards

[cjcass]

Hi,

I'm looking for some vba that will do the following:

I have cells in Sheet2 from cell I101 down (the last cell is variable) that contain text as phrases.

I have a table in Sheet1 W6:X###. In column W each cell has a letter/wildcard combination (eg. c*m*c*n). Next to each of these in adjacent cells there are words in column X (eg. communication). The table will vary in number of rows down as it grows.

I'm looking for some vba that will look at all the phrases in Sheet2 I101:I### and where it finds a word in the phrase that meets a letter/wildcard combination (from Sheet1 W6:W##) it changes it to the word that corresponds in Sheet1 Column X. The words in the phrases will vary in terms of their case, so cases should not be matched during the vba's search.

So, if Sheet2 cell I111 contains the phrase 'Please send comuniccation now' the vba will replace the wrongly spelled 'comuniccation' with the correct 'communication' spelling and highlight the changed word bold red.

Notes:
- Ignore the column headers in the examples shown they will not be present.
- I need the letter/wildcard combinations to look for single words only and not spread across several words
- the table in Sheet2 shows the finished output although I noticed in the preview of the post that the changed words were not highlighted red?!

I am aware that this method has limitations and could change a word that meets the criteria but isn't meant to be 'communication' so I will have to set up the letter/wildcard combinations carefully. It won't be 100% but it will be pretty close and I'm targeting commonly misspelled words in the context of the project. The red highlighting will assist me checking the output. If anyone has any ideas re. the advanced use of wildcards etc. then I'm all ears!

Any help much appreciated.

Book1.2.xlsx
	V	W	X	Y
4
5		Lookup	Correction
6		c*m*c*n	communication
7		m*g*r	manager
8		m*g*nt	management
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Sheet1

Book1.2.xlsx
	H	I	J
99
100		phrase
101		the person walks down the road
102		The hotel management are all away on holiday
103		why is the restaurant closed?
104		it's the best place around here
105		what do you mean??
106		"It's not funny anymore!"
107		sixty five benches
108		I am the new manager here
109		the bus is late
110		the car is so slow
111		please send communication now
112		the cat and the dog got hit by a train
113		great parking here
114
115
116
117
118
119
120
121
Sheet2

 

[cjcass]

Wondered if anyone had any ideas on this one?
Thanks in advance.

 

[cjcass]

I have the code below but it only works changing a word that exists on it's own in the cell.
Does anyone know how I can adapt it so it will find the word in a text string in a cell and make the change?
Any help much appreciated.

Dim LRow As Long, i As Long
Dim varSearch As Variant

With Sheet10
    LRow = .Cells(.Rows.Count, 23).End(xlUp).Row '23 relates to Col W
    varSearch = .Range("W6:X" & LRow)
End With

With Sheet1.Range("I:I")
    For i = LBound(varSearch) To UBound(varSearch)
        .Replace what:=varSearch(i, 1), replacement:=varSearch(i, 2), lookat:=xlWhole
    Next
End With

 

[cjcass]

Any thoughts out there?

 

[cjcass]

I guess I'd better try another forum for this one.