PDA

View Full Version : Handle Spelling Errors



cchart
09-08-2017, 08:54 AM
On my spreadsheet, I have two sheets. Sheet2 has a table with three columns. See in the example below, Column A has Villages, B has Regions, and C has Village with Regions (In the same cell). In Sheet 1, I daily import information which includes Villages in Column M which has empty spaces between Villages. I would like to put the Region below the Village in bold when they are bolded. Someone was very nice to write the below code for me but, it errors if the information in Column M of Sheet1 one does not match exactly what is on the Table. (I didn't have a Regions Column when the code was written). The information I import has spelling errors and Villages I don't need regions for. Is it possible to overlook these spelling errors/Villages or even better bold them too? Any help would be appreciated.



Villages
Regions
Village with Regions


ADAK
ANCHORAGE
ADAK ANCHORAGE


AKHIOK
KODIAK
AKHIOK KODIAK


AKIACHAK
BETHEL
AKIACHAK BETHEL



AKIAK
BETHEL
AKIAK BETHEL


AKUTAN
ANCHORAGE
AKUTAN ANCHORAGE


ALAKANUK
BETHEL
ALAKANUK BETHEL


ALEKNAGIK
DILLINGHAM
ALEKNAGIK DILLINGHAM


ALLAKAKET
FAIRBANKS
ALLAKAKET FAIRBANKS


AMBLER
KOTZEBUE
AMBLER KOTZEBUE














Adding Code Format Tags to Code inside the Table deleted the code. I am sorry.

Please repost your code and use the # Icon to insert Code Formatting Tags into your post. You can Paste the Code between the tags, OR, Paste the code, then select it, then click the # icon. SamT, Moderator

mdmackillop
09-08-2017, 10:34 AM
Try this

Sub SubstitutionsBold2()
Set Rng = Sheets("Sheet1").Columns(13).SpecialCells(2)
Set Data = Sheets("Sheet2").Columns(1)
For Each cel In Rng
Set reg = Data.Find(cel, lookat:=xlWhole)
If reg Is Nothing Then
cel.Interior.ColorIndex = 6
Else
With cel(2)
.Value = reg(1, 2)
.Font.Bold = True
End With
End If
Next
End Sub

cchart
09-08-2017, 11:49 AM
Thank you for your response! Thanks to your code, I do not need to highlight or bold spelling errors/new Villages. Is it possible to not to highlight or bold them? Additionally, with Regions, all should be bold with the exception of "Anchorage," but this code is bolding Anchorage. Is it possible not to? Thank you for your help!

mdmackillop
09-08-2017, 12:06 PM
Sub SubstitutionsBold2()
Set Rng = Sheets("Sheet1").Columns(13).SpecialCells(2)
Set Data = Sheets("Sheet2").Columns(1)
For Each cel In Rng
Set reg = Data.Find(cel, lookat:=xlWhole)
If reg Is Nothing Then
'cel.Interior.ColorIndex = 6
Else
With cel(2)
.Value = reg(1, 2)
If .Value <> "ANCHORAGE" Then .Font.Bold = True
End With
End If
Next
End Sub

cchart
09-08-2017, 12:29 PM
Thank you so much. Is it possible to have the only effect Column M of Sheet 1?

mdmackillop
09-08-2017, 12:49 PM
Omission from code corrected

Set Rng = Sheets("Sheet1").Columns(13).SpecialCells(2)

cchart
09-08-2017, 09:17 PM
Your code is working well, I am having an issue though. If something is misspelled in Column M, your code ignores it, which is great. But Regions show up in Column M, these I want to bold only. I tried a conditional format, but it didn't work well. The Regions are :


Bethel


Sitka


Barrow


Kotzebue


Kodiak


Nome


Dillingham


Fairbanks


Kenai





If you can help, that would be great

mdmackillop
09-08-2017, 11:28 PM
Can you post a workbook showing your data and results.

cchart
09-09-2017, 03:04 PM
Thank you!

mdmackillop
09-10-2017, 08:21 AM
I daily import information which includes Villages in Column M
No mention of Regions in Column M as per your example.

This will bold Regions (excl. Anchorage) only if they appear on Sheet 2



Sub SubstitutionsBold4()
Set Rng = Sheets("Sheet1").Columns(13).SpecialCells(2)
Set Data = Sheets("Sheet2").Columns(1)
Set Regions = Sheets("Sheet2").Columns(2)
For Each cel In Rng
cel.Select
Set reg = Data.Find(cel, lookat:=xlWhole)
If reg Is Nothing Then
'Bo nothing
Else
cel(2).Value = reg(1, 2)
End If
Set reg = Regions.Find(cel, lookat:=xlWhole)
If reg Is Nothing Then
'Do nothing
Else
If Trim(cel) <> "ANCHORAGE" Then cel.Font.Bold = True
End If
Next
End Sub

cchart
09-10-2017, 09:21 AM
Thank you for your help. The original Regions are not bolding after this change.

cchart
09-11-2017, 12:36 AM
I'm not sure how to mark this as solved. Running both scripts works well.

Thank you very much!