Formula for spelling errors

[ChrisFoster]

Hi,

I have a document with tens of thousands of rows of data. I need to check the sheet for spelling errors as they are all SMS campaigns that staff want to send to customers in a professional capacity.
F7 highlights each error but this is not sustainable when I have so much data.

Is there a formula I can use to check data in column B for spelling errors and simply return True/False so I can easily filter the data?

Kind regards,

Chris

 

[duggie33]

Hi ChrisFoster,

You could try something like this...

Book1
	A	B
1	dog
2	dag	FALSE
3	cat
4	cta	FALSE
5	horsey
6	horses
7	horsee	FALSE
8	goat
9	gaot	FALSE
Sheet1

 Sub SpellCheckResults()

    Dim blnCorrect As Boolean
    Dim strCheck As String
    Dim cell As Range

    For Each cell In Intersect(Range("A1:A10"), ActiveSheet.UsedRange)
        strCheck = cell.Value
        blnCorrect = Application.CheckSpelling(strCheck)
        If Not blnCorrect Then
            cell.Offset(0, 1).Value = blnCorrect
        End If
    Next

End Sub

Hope that helps,

Doug

 

[ChrisFoster]

Hi ChrisFoster,

You could try something like this...

Book1
	A	B
1	dog
2	dag	FALSE
3	cat
4	cta	FALSE
5	horsey
6	horses
7	horsee	FALSE
8	goat
9	gaot	FALSE
Sheet1

 Sub SpellCheckResults()

    Dim blnCorrect As Boolean
    Dim strCheck As String
    Dim cell As Range

    For Each cell In Intersect(Range("A1:A10"), ActiveSheet.UsedRange)
        strCheck = cell.Value
        blnCorrect = Application.CheckSpelling(strCheck)
        If Not blnCorrect Then
            cell.Offset(0, 1).Value = blnCorrect
        End If
    Next

End Sub

Hope that helps,

Doug

Thanks Doug, that brings back a run time error though (type mismatch) when it gets to "blnCorrect = Application.CheckSpelling(strCheck)".

 

[duggie33]

Application.CheckSpelling method (Excel)

Office VBA reference topic

learn.microsoft.com

It checks the spelling of a single word. Are you testing it on a single word?

Doug

 

[ChrisFoster]

Application.CheckSpelling method (Excel)

Office VBA reference topic

learn.microsoft.com

It checks the spelling of a single word. Are you testing it on a single word?

Doug

Not single words. As they are SMS messages, each message contains many different words.

 

[duggie33]

Weird, I testing on strings of multiple words and it seems to work.

 

[duggie33]

Type mismatch would normally be an error where the type being returned (boolean in this case) does not match the type specified for the variable.