VBA Spellcheck not working

[markh999]

I have a long string (with no spaces) that I am trying to split up into legal words with one space between them. I want it fully automated so am using the attached code to spell check individual words.

However, to test this code works ok I have entered the single letters 'a' to 'z' in the first 26 rows of column A. For every row (except the letter 'i') it comes up with TRUE (indicating a correct spelling). This can't be right as only 'i' (which is showing FALSE) and 'a' (showing TRUE) I think are the only legal 1 letter words. So, what am I doing wrong ...... ?

Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With Range("A" & i)
.Offset(, 2).Value = Application.CheckSpelling(.Value)
End With
Next i
End Sub

 

[SkipVought]

hi,

This can't be right as only 'i' (which is showing FALSE)

Try I rather than i.

You should get ALL TRUE with a capital I. Just do it from the sheet.

Try aa of bb instead!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[markh999]

Thanks Skip

Sorry but I don't understand your answer?

Why are letters such as 'c' 'd' and 'e' coming up as true?

I also don't want it to be case sensitive - it should recognise 'I' and 'I' both as valid words?

What do you mean by aa of bb ?

 

[SkipVought]

[tt]
skip TRUE
skkp FALSE
aa FALSE
a TRUE
i FALSE
I TRUE
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[madonnac]

Letters on their own can be used as valid words:
c is the 3rd letter of the alphabet

as word has no access to context, the only time it can make a (semi) accurate guess as to if a single letter is incorrect is with i, as it is always used in upper case (with English dictionaries)

what Skip is suggesting is to use:
aa
bb
cc
dd
..
xx
yy
zz

 

[markh999]

I see - thank you for the clarification.

But this now gets weirder. I have just got Spellchecked 'catsatonthemat' and got FALSE (which is what I would expect!). But if I do 'catsatonthemat2' it says TRUE !!!

What is going on? I must have something set somewhere- could you try the same code on yours and let me know what it says?

Thank you for your help.

 

[combo]

You may need to configure spelling options first in excel or via vba (for instance Application.SpellingOptions.IgnoreMixedDigits = False).

combo