• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

List - Incorrectly spelled words in a sheet [SOLVED]

D

Dhamo

New Member
Hi,


In the review phase, I would like to get list of incorrectly spelled words in a sheet with its cell address.


Output Sheet:

1. taht A2

2. tesign A9

etc


Is that possible?
 
Good day Dhamo


Unless who have setup some settings in the proofing section how would Excel know to word is incorrectly spelled
 
Check this out. Assumes that a worksheet called "Output" already exists. Checks contents of active sheet. Note that I'm not sure how long this would take to run on a large sheet.

[pre]
Code: 
Sub spellList()
Dim c As Range
Dim myWord As String
Dim i As Long, x As Long
Dim myCell As String
Dim textCells As Range
Dim splitWords

Set textCells = Union(ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, xlTextValues), _
ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, xlTextValues))

i = 1
Application.ScreenUpdating = False
'Check all the constants
For Each c In textCells
myCell = c.Address
splitWords = Split(c.Value, " ")
For x = 0 To UBound(splitWords)
myWord = splitWords(x)
If Not (Application.CheckSpelling(myWord)) Then
Worksheets("Output").Cells(i, "A").Value = myWord
Worksheets("Output").Cells(i, "B").Value = myCell
i = i + 1
End If
Next x
Next c
Application.ScreenUpdating = True

End Sub
[/pre]
 
Hi Luke,


I am getting error on 'Set textCells = Union(ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, xlTextValues), _

ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, xlTextValues))'


Error name: Run time error, No cells were found.


File link: http://www.2shared.com/file/-2_e9gZU/Spell_Test.html
 
Hi Dhamo ,


Make this small modification :

[pre]
Code: 
Sub spellList()
Dim c As Range
Dim myWord As String
Dim i As Long, x As Long
Dim myCell As String
Dim textCells As Range, textCells1 As Range, check_range As Range
Dim splitWords

On Error Resume Next
Set textCells = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
Set textCells1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, xlTextValues)
On Error GoTo 0

If Not (textCells Is Nothing) Then
Set check_range = textCells
End If

If Not (textCells1 Is Nothing) Then
Set check_range = Union(check_range, textCells1)
End If

i = 1
Application.ScreenUpdating = False
'Check all the constants
For Each c In check_range
myCell = c.Address
splitWords = Split(c.Value, " ")
For x = 0 To UBound(splitWords)
myWord = splitWords(x)
If Not (Application.CheckSpelling(myWord)) Then
Worksheets("Output").Cells(i, "A").Value = myWord
Worksheets("Output").Cells(i, "B").Value = myCell
i = i + 1
End If
Next x
Next c
Application.ScreenUpdating = True
End Sub
[/pre]
Narayan
 
Thanks Narayan for update. I might suggest adding another check to make sure there are actually "some" cells in Check_range, but hopefully the user will only run macro if there are words somewhere on the worksheet. =P

@scottyg

Sorry I didn't comment everything out. I'll add comments to Narayan's code to explain whats going on.

[pre]
Code: 
Sub spellList()
Dim c As Range
Dim myWord As String
Dim i As Long, x As Long
Dim myCell As String
Dim textCells As Range, textCells1 As Range, check_range As Range
Dim splitWords

'Use the Special Cells group to look for text string in constants and in formulas
'XL throws an error if no cells exist within one of these groups, hence
'the On error resume Next comment
'Purpose is to limit our scope to just cells with text. This is better than searching
'every cell as it will be much faster
On Error Resume Next
Set textCells = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
Set textCells1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, xlTextValues)
On Error GoTo 0

'Check each group to make sure something exists
If Not (textCells Is Nothing) Then
Set check_range = textCells
End If

If Not (textCells1 Is Nothing) Then
Set check_range = Union(check_range, textCells1)
End If

'I added the next line in case there were no text strings anywhere in sheet
If check_range Is Nothing Then Exit Sub

i = 1
Application.ScreenUpdating = False
'Check all the constants

'First, we're going to look at each cell within our check_range
For Each c In check_range
'Store the cell address for later use
myCell = c.Address

'Split the contents of the cell into the splitWords array.
'We split at every space
splitWords = Split(c.Value, " ")

'Now, loops through every word within our array
For x = 0 To UBound(splitWords)
'Store the word for later use
myWord = splitWords(x)

'This is the "spell checker" for VB. It has a few other options
'you can read about in help file, but basically you give it
'a word and it returns True/False if the word is spelled correct
If Not (Application.CheckSpelling(myWord)) Then

'If it wasn't spelled correct, add them to our list
Worksheets("Output").Cells(i, "A").Value = myWord
Worksheets("Output").Cells(i, "B").Value = myCell

'Increment our output counter so the next word will
'be written to next row
i = i + 1
End If
Next x
Next c
Application.ScreenUpdating = True
End Sub
[/pre]
 
Just found this site. So glad I did just wish it had been a couple of years sooner.

This macro is exactly what I've been looking for and have already put it to good use.

Unfortunately I'm not skilled in writing code but I'm willing to try. So I was wondering if it would even be possible to add something to this macro whereby it adds only one occurrence of each misspelled word.


Example

Suppose you had the range:

R1C1 = taht ball is blue

R2C1 = taht ball is red

R3C1 = taht ball is green

R4C1 = taht ball is yello


Instead of output like:

taht A1

taht A2

taht A3

taht A4

yello A4


It returned:

taht A1

yello A4


What do you all think? Is it "doable"?
 
Hi, juanr!


With the implicit and never asked permission of Luke M, I tried to fix the posted procedure so as to display all the items or each only once. This is the updated but not tested code:

-----

[pre]
Code: 
Option Explicit

Sub spellList()
Const kbOnlyOnce = True
Dim bShouldAdd As Boolean

Dim c As Range, c1 As Range
Dim myWord As String
Dim i As Long, x As Long
Dim myCell As String
Dim textCells As Range, textCells1 As Range, check_range As Range
Dim splitWords

'Use the Special Cells group to look for text string in constants and in formulas
'XL throws an error if no cells exist within one of these groups, hence
'the On error resume Next comment
'Purpose is to limit our scope to just cells with text. This is better than searching
'every cell as it will be much faster
On Error Resume Next
Set textCells = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
Set textCells1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, xlTextValues)
On Error GoTo 0

'Check each group to make sure something exists
If Not (textCells Is Nothing) Then
Set check_range = textCells
End If

If Not (textCells1 Is Nothing) Then
Set check_range = Union(check_range, textCells1)
End If

'I added the next line in case there were no text strings anywhere in sheet
If check_range Is Nothing Then Exit Sub

i = 1
Application.ScreenUpdating = False
'Check all the constants

'First, we're going to look at each cell within our check_range
For Each c In check_range
'Store the cell address for later use
myCell = c.Address

'Split the contents of the cell into the splitWords array.
'We split at every space
splitWords = Split(c.Value, " ")

'Now, loops through every word within our array
For x = 0 To UBound(splitWords)
'Store the word for later use
myWord = splitWords(x)

'This is the "spell checker" for VB. It has a few other options
'you can read about in help file, but basically you give it
'a word and it returns True/False if the word is spelled correct
If Not (Application.CheckSpelling(myWord)) Then

' If it should appear only once then check if previously exists
' otherwise add it always
If kbOnlyOnce Then
Set c1 = Worksheets("Output").Columns("A").Find( _
myWord, Worksheets("Output").Cells(1, "A"), xlValues, xlWhole)
bShouldAdd = c1 Is Nothing
Else
bShouldAdd = True
End If

' Check if should be added
If bShouldAdd Then

'If it wasn't spelled correct, add them to our list
Worksheets("Output").Cells(i, "A").Value = myWord
Worksheets("Output").Cells(i, "B").Value = myCell

'Increment our output counter so the next word will
'be written to next row
i = i + 1

End If

End If
Next x
Next c
Application.ScreenUpdating = True
End Sub
[/pre]
-----


Showing one or all values is handled thru the constant kbOnlyOnce (True for only once and False for every). Added 2 variables, bShouldAdd as indicator for displaying or not, and c1 as a new range used to check previous existence of the new entry.


Please check it carefully and just advise if any issue. Better to Luke M, so he works a little. But in this case all credit to him and previous posters, I just only added an "If".


Regards!
 
I've got a lot of data to check, but so far it has done exactly what I needed it to do. Many thanks to both Luke M and SirJB7.


Hopefully I will have something useful to contribute one day.
 
Hi, juanr!

Glad you solved it. Thanks for your feedback and for your kind words too, but once again all credit to Luke M and previous posters, my two cents were just 2 Dims and 1 If.

Welcome back whenever needed or wanted. And hopefully as a contributor as well.

Regards!


EDITED


Marked as solved, since it works with more features than those originally requested. If OP comes back and follows up, mark will be reset.
 
You must log in or register to reply here.
Back
Top