Another Run Time Error '13': Type Mismatch

Question

I am quite new to Excel VBA. I'm triggering several macros depending on the inputs on a particular cell on the work sheet.

I have no problem with the first 2 options. But everytime I use the third option, which is Delete Row, & everytime I edit a cell or delete a cell, I encounter the error.

The code is below:

 Sub Worksheet_Change(ByVal Target As Range)
 Set Target = ActiveCell
 If Target.Value = "Fuel Supply" Then (<=Error At This Point)
      Fuel_Heating_Value
 ElseIf Target.Value = "Add Row" Then
      Insert_New_Row
 ElseIf Target.Value = "Delete Row" Then
      Delete_Row
 Else:
 End If
 End Sub

The error is always at the third line of the code in the 1st option statement.

The 3rd option code is below:

 Sub Delete_Row()
 '
 ' Delete_Row Macro
 '
     Application.ScreenUpdating = False
     ActiveCell.Select
     ActiveCell.EntireRow.Select
     Selection.Delete Shift:=xlUp
     ActiveCell.Select
     ActiveCell.Offset(-1, 12).Select
     ActiveCell.Select
     Selection.Copy
     ActiveCell.Offset(1, 0).Select
     ActiveSheet.Paste
     Application.CutCopyMode = False
     ActiveCell.Offset(0, -11).Select
     Application.ScreenUpdating = True
  End Sub

Any help to eliminate this problem will be appreciated.

Thanks.

Please mark the line that produces the error in your code (`' <- Error here`), your description is a little confusing. — Andre, Nov 16 '15 at 10:28
As I mentioned in my answer below, the act of deleting a row triggers another change event and the Worksheet_Change tries to run on top of itself. When it does this, the Target is the row that replaced the deleted row so `Target` is not a single cell. It is a collection of 16,384 cells (an entire row) and you cannot compare a collection of 16,384 cells to a single text string. This is the `Type Mismatch`. — , Nov 18 '15 at 08:04

score 0 · Answer 1 · edited May 23 '17 at 11:44

You are deleting rows, copy and pasting cells, etc all from a Worksheet_Change event macro. You should never modify the worksheet you are on from a Worksheet_Change without disabling events or the actions you take will trigger another event and the Worksheet_Change will try to run on top of itself.

Additionally, I do not know what to make of Set Target = ActiveCell. When the Worksheet_Change is triggered, it passes along the address of one or more cells that received a change. You base your conditions on that Target. You do not have to set it to anything. However, you should guard against there being more than a single cell as the Target; your code will throw an error if you try to compare a group of cells to a single text string. Perhaps this is where the error was occurring. Your conditions all depend upon Target being a single cell. I've put exit code if Target is more than one cell because it is pointless to continue if it is plural.

Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub

    On Error GoTo bm_Safe_Exit
    Application.EnableEvents = False

    If Target.Value = "Fuel Supply" Then
        Fuel_Heating_Value
    ElseIf Target.Value = "Add Row" Then
        Insert_New_Row
    ElseIf Target.Value = "Delete Row" Then
        Delete_Row
    Else:
    End If

bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

Now that will not kick another event running on top of the first if anything changes in the worksheet as a result of the Worksheet_Change.

The Delete_Row routine can be improved as well but it would help to have a narrative as to what it is trying to accomplish. There are cells selected that are already selected and the ActiveCell property is thrown around quite a bit.

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

Another Run Time Error '13': Type Mismatch

1 Answers1