VBA Error - "Unable to get the Count property of the Characters class"

[beartooth91]

Trying to count characters in each cell and flag entries with more than 20 characters. Its failing on the "Unable to get the Count property of the Characters class" error because the string contains numbers (Ex: 0-FHP-CE-0001A). Looking for a SIMPLE fix to be able to count these entries......

'Missing/Improper Component Number
  For Each cell In .Range("B11:B" & lastRow)
    'cell.NumberFormat = "@"
    If cell.Value = "" Or _
       cell.Characters.Count > 20 Then
       cell.Interior.ColorIndex = 22
    End If
  Next cell

 

[Fluff]

Try

'Missing/Improper Component Number
  For Each cell In .Range("B11:B" & lastRow)
    'cell.NumberFormat = "@"
    If cell.Value = "" Or _
       Len(cell) > 20 Then
       cell.Interior.ColorIndex = 22
    End If
  Next cell

 

[beartooth91]

Trying to count characters in each cell and flag entries with more than 20 characters. Its failing on the "Unable to get the Count property of the Characters class" error because the string contains numbers (Ex: 0-FHP-CE-0001A). Looking for a SIMPLE fix to be able to count these entries......

'Missing/Improper Component Number
  For Each cell In .Range("B11:B" & lastRow)
    'cell.NumberFormat = "@"
    If cell.Value = "" Or _
       cell.Characters.Count > 20 Then
       cell.Interior.ColorIndex = 22
    End If
  Next cell

Using Len seems to fix my problem......

'Missing/Improper Component Number
  For Each cell In .Range("B11:B" & lastRow)
    If cell.Value = "" Or _
       Len(cell) > 20 Then
       cell.Interior.ColorIndex = 22
    End If
  Next cell

 

[beartooth91]

Try

'Missing/Improper Component Number
  For Each cell In .Range("B11:B" & lastRow)
    'cell.NumberFormat = "@"
    If cell.Value = "" Or _
       Len(cell) > 20 Then
       cell.Interior.ColorIndex = 22
    End If
  Next cell

Yeah, I figured it out and we both posted at the same time. Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.