CODEWITHSUNDEEP
excelvba
Filip Frątczak
Filip Frątczak

Reputation: 157

Type mismatch error if I press delete button for target cell

In worksheet change event I have some function to merge cells if CX cell value is higher than 1. If I press delete button on CX cell, which have already been merged, I get type mismatch error with line:

If Target.Value > 1 Then

The whole code is below.

If Not Intersect(Target, Range("C14:C50")) Is Nothing And Sh.Name <> "Dane" Then
    For i = 1 To 8 Step 1
        If i <> 6 And i <> 7 And Cells(Target.row, i).MergeCells Then
            Cells(Target.row, i).UnMerge               
        End If
    Next i
    If Target.Value > 1 Then
      For i = 1 To 8 Step 1
        If i <> 6 And i <> 7 Then
            Range(Cells(Target.row, i), Cells(Target.row + Target.Value - 1, i)).Merge
        End If
      Next i
    End If
End If

EDIT: I've added function which is executed before error line. It should unmerge any target cells before error line is executed. It works fine if value is higher than 1, but still gives error if I press delete when editing CX cell if it's merged. @FunThomas solution Target(1, 1).Value solves the problem, but it shoudn't occur with that code anyway.

Upvotes: 0

Views: 137

Answers (1)

FunThomas
FunThomas

Reputation: 29612

If Target (or any range) points to merged cells, it contains a range of all cells. You can't access the Value-Property of a range that contains more than one cell.

You can use Target(1).Value or (Target(1, 1).Value)to access the first cell which is the cell containing the data.

To check if a range is a merged cell, use target.MergeCells (returns true if merged).

To check the number of cells in a range, use target.Count

Upvotes: 1

Related Questions