CODEWITHSUNDEEP
excelvba
Adi
Adi

Reputation: 41

Run-time error '13': Type Mismatch when deleting records in excel file

I have the following code Which I got from this forum. This is working fine but throwing an error when I delete records in the excel file. The error which I get is Run-time error '13': Type Mismatch.

On clicking debug button, It highlights the folloing line in the code:

If Target.Value <> vbNullString Then Target.Offset(, 1).Value = GetGUID

I think I know what the problem is but don't know how to fix it.

The full code is

Private RANGE_CELL As Range

 Public Function GetGUID() As String
        GetGUID = Mid$(CreateObject("Scriptlet.TypeLib").GUID, 2, 36)
    End Function

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Target.Value <> vbNullString Then Target.Offset(, 1).Value = GetGUID
End If

End Sub

Upvotes: 1

Views: 471

Answers (1)

Scott Craner
Scott Craner

Reputation: 152495

When you are deleting you are changing more than one cell so the Target.Value <> vbNullString throws the error.

You can not bulk compare a range with more than one cell at one time.

Add a check to ensure that only Column A is changing and that there is only one Target cell not a whole range:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Count = 1 Then
    If Target.Value <> vbNullString Then Target.Offset(, 1).Value = GetGUID
End If

End Sub

Upvotes: 2

Related Questions