0

I'm trying to write a macro that will highlight any cells in the range e5:e20000 that have a spelling error. I get a "runtime error 113" every time I hit a cell with more than 255 characters in it. I tried adding a condition to skip +255 char cells, but it's not really working.

Ideally, I want to include all cells, regardless of the character number. Any advice? Thanks!

Sub Cellswithtypos()
For Each cl In ActiveSheet.UsedRange.Range("E5:E20000").Cells
    If Len(cl.Value) <= 255 And Not Application.CheckSpelling(Word:=cl.Text) Then _
cl.Interior.ColorIndex = 18
Next cl
End Sub
Chrismas007
  • 6,085
  • 4
  • 24
  • 47
Amanzaloko
  • 13
  • 2
  • 1
    It did not work because VBA [does not short circuit](http://stackoverflow.com/q/24641923/11683). – GSerg Jan 27 '15 at 21:41

1 Answers1

1

You have to nest to check the length of cell first. I also added in a check for blank cells so you can bypass blanks (should speed up code).

Sub Cellswithtypos()
For Each cl In ActiveSheet.UsedRange.Range("E5:E20000").Cells
    If Len(cl.Value) <= 255 And Len(cl.Value) > 0 Then
        If Not Application.CheckSpelling(Word:=cl.Text) Then
            cl.Interior.ColorIndex = 18
        End If
    End If
Next cl
End Sub
Chrismas007
  • 6,085
  • 4
  • 24
  • 47
  • Thanks, @Chrismas007!! I was looking into the short-circuiting tip and working out a solution, but your code came in faster. And it works perfectly. – Amanzaloko Jan 27 '15 at 22:09
  • 1
    `WorksheetFunction.CountA(cl) <> 0` is a rather obscure way to check if the cell is not empty, there is `Not IsEmpty(cl.Value)` for that. However both `CountA` and `IsEmpty` will treat empty strings as non-empty values, as opposed to true blanks, so the condition should really be `If Len(cl.Value) <= 255 an Len(cl.Value) > 0`. – GSerg Jan 27 '15 at 22:22