Why am I getting a type mismatch error here?

1
votes

I create a new module and insert this code:

Sub test()
   Set wsData = ThisWorkbook.Worksheets("Data")
   sCount = wsData.Columns(14).SpecialCells(xlCellTypeBlanks).Count
   msgbox sCount
End Sub

In the worksheet "Data", I have this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.CountLarge = 1 Then
        If Not Intersect(Target, Range("K:M")) Is Nothing And Target.Value <> "" Then
            'code
        End if
    End if
End Sub

When I run the test() sub, I get a type mismatch error on If Not Intersect(Target, Range("K:M")) Is Nothing, as Target wrong type.

Why this is happening?

Why is test triggering the Change Event? I dont get the same error if manually filter column 14 of my Data sheet to leave only the blank cells!

2
vbaexcel
What happens if you try Worksheets("Data").Range("K:M") instead of Range("K:M")? - Mistella
Why is test triggering the Change Event? - Tom
Can I suggest that you add what you intend for your code to do to your question? Also wbT is not defined in test()? - Dan
I wonder if .SpecialCells(xlCellTypeBlanks) maybe triggers a filter on the sheet... do you get the same error if you manually filter column 14 of your Data sheet to leave only the blank cells? - Dan
@Tom .SpecialCells(xlCellTypeBlanks) does trigger the selectionChange event. The selection is the blank cells. I knew it just 30 sec before :P - newacc2240

2 Answers

5
votes

The problem with the type mismatch, is that the Target.Cells is more than one cell. Thus, the Target.Value <> "" throws type mismatch, because multiple cells cannot be compared to "". See the MsgbBox with the number of cells:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.CountLarge = 1 Then
        If Target.Cells.CountLarge > 1 Then MsgBox Target.Cells.CountLarge
        If Not Intersect(Target, Range("K:M")) Is Nothing And Target.Value <> "" Then
            'code
        End If
    End If
End Sub

Based on the business logic there could be several solutions.

  • The easiest one is to write If Target.Cells.CountLarge > 1 Then Exit Sub in the _SelectionChange event.

  • Another way is to disable events around

sCount = wsData.Columns(14).SpecialCells(xlCellTypeBlanks).Count like this:

Sub TestMe()
   Set wsData = ThisWorkbook.Worksheets("Data")
   Application.EnableEvents = False
   sCount = wsData.Columns(14).SpecialCells(xlCellTypeBlanks).Count
   Application.EnableEvents = True
   msgbox sCount
End Sub
1
votes

I almost closed this question as a duplicate.

I will answer both your questions but in the reverse order so that you can understand it better.

Why is test triggering the Change Event?

I have explained it in SpecialCells causing SheetSelectionChange event in Excel 2010

When I run the test() sub, I get a type mismatch error on If Not Intersect(Target, Range("K:M")) Is Nothing, as Target wrong type. Why this is happening?

When the procedure Test triggers the Worksheet_SelectionChange event, your code will fail on the line

If Not Intersect(Target, Range("K:M")) Is Nothing And Target.Value <> "" Then

It is because Target.Value <> "" is the culprit as SpecialCells(xlCellTypeBlanks).Count may return multiple cells.

If you break the above line in 2 lines then you will not get an error

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Range("K:M")) Is Nothing Then
        If Target.Value <> "" Then
             'code
        End If
    End If
End Sub