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

Question

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

Most likely there is an error within the cell that fails, e.g. division by zero or sth else — Ike
– Ike, Commented Jul 7, 2022 at 14:03
c.Row <> 1 will always be True by the way, if you're looping over ws.Range("A2:R10000"). — BigBen
– BigBen, Commented Jul 7, 2022 at 14:03
c is already referencing the row and column as well so you could consolidate ws.Cells(c.Row, c.Column).Interior.ColorIndex = 3 to c.Interior.ColorIndex = 3 — data_sc
– data_sc, Commented Jul 7, 2022 at 14:16

CLR · Accepted Answer · 2022-07-07 14:08:19Z

2

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

answered Jul 7, 2022 at 14:08

CLR

12.3k1 gold badge13 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TycExcel · Accepted Answer · 2022-07-07 14:22:57Z

0

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.

answered Jul 7, 2022 at 14:22

TycExcel

53 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related