VBA Type Mismatch Error With Range.Value and Range.Row-CodePudding

I have the following VBA code and it seems to work partially? Sometimes it works, and sometimes it doesnt. I am getting a type mismatch error on the "If Len(c.Value) > 30 And c.Row <> 1" Then line. Any help would be great, thanks.

Sub GSSLength()
    Dim c As Range
    Dim ws As Worksheet
    Dim j As Long

    j = 0

    For Each ws In ActiveWorkbook.Worksheets    
        If ws.Name Like "BOM" Then        
            For Each c In ws.Range("A2:R10000")
                If Len(c.Value) > 30 And c.Row <> 1 Then        
                    ws.Cells(c.Row, c.Column).Interior.ColorIndex = 3            
                    j = j   1                
                End If        
             Next c    
        End If
    Next ws

    If j > 0 Then
        MsgBox j & " errors found where the length of cell is longer than 30 characthers, please correct in columns that are red."
    End If

End Sub

CodePudding user response：

I'm assuming this isn't your final code..? The range you've specified will never have Row = 1 and you're using "BOM" in a Like statement that seems unnecessary. Maybe you've put this in just for this question.

Ignoring those oddities, you've probably got a cell in that range that contains an error. You can trap these out like so:

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "BOM" Then
        For Each c In ws.Range("A2:R10000")
            
            If Not (IsError(c)) Then

                If Len(c.Value) > 30 And c.Row <> 1 Then
                    ws.Cells(c.Row, c.Column).Interior.ColorIndex = 3
                    j = j   1
                End If
            End If
         Next c
    End If
Next ws

CodePudding user response：

Thanks for all the tips, I just noticed that I had an error in my cells where it was a number format instead of a string, and the LEN function was throwing an error there.