VBA – Error Type mismatch Worksheet_Change

by

I am trying to make a macro which detects that when some cell change to a value that I specify it will add in another sheet of the same workbook an extra row with the same format as the others, however, when I try to evaluate the value of the cell with the specified text string I get the error that the types do not coincide.
I have tried to change the value of the cell to String but I still get the same error.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim i As Long


Application.ScreenUpdating = False


If Not Intersect(Target, Range("D8:D312")) Is Nothing Then
    If Target.Value = "N.I.O" Then

        Worksheets("Desviaciones UB").Activate
        i = Cells(Rows.Count, 2).End(xlUp).Row
        Rows(i).Copy
        Rows(i).Offset(1, 0).EntireRow.PasteSpecial Paste:=xlPasteFormats
        Rows(i).Offset(1, 0).EntireRow.PasteSpecial Paste:=xlPasteFormulas
        Application.CutCopyMode = False
     End If
End If




Application.ScreenUpdating = True

End Sub

  • When Target is a multi-cell range, its .Value is a 2D array that can’t be directly compared to a String.

    – 

  • 1

    You are probably thinking that after Worksheets("Desviaciones UB").Activate the Rows(i) will refer to the rows on that activated worksheet. It will however still refer to this worksheet’s Rows, so you are copying the row into the worksheet that has the Worksheet_Change, thus triggering Worksheet_Change again, this time with the Target equal to the copied row. See stackoverflow.com/q/10714251/11683.

    – 




Incorporating the feedback from the comments, which primarily means properly referencing the parent worksheet for each instance of Range, Cells, and Rows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Application.ScreenUpdating = False

    Dim rng As Range, cell As Range
    Set rng = Intersect(Target, Me.Range("D8:D312"))
    If rng Is Nothing Then Exit Sub

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Desviaciones UB")

    For Each cell In rng
         If cell.Value = "N.I.O" Then
              Dim lastRow As Long
              lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
              
              ws.Rows(lastRow).Copy
              ws.Rows(lastRow + 1).PasteSpecial Paste:=xlPasteFormats
              ws.Rows(lastRow + 1).PasteSpecial Paste:=xlPasteFormulas
              Application.CutCopyMode = False
         End If
    Next

    Application.ScreenUpdating = True
End Sub

Leave a Comment