none
Type mismatch error RRS feed

  • Question

  • I'm new to VBA and I wrote the following code to keep a history of values in a cell (when cell changes in column F). It works, but when I right-click and Insert or Delete a row, I get "Run-time error '13': Type mismatch." on the line that's bold. The code appends the contents of the "history" cell with each new value of the cell be tracked. Here's an example:

    A1: "1"  A3: "1"

    A1: "2" A3: "1, 2"

    A1: "3" A3: "1, 2, 3"

    etc...

    What am I doing wrong? 

    Is there a better way to write VBA to do this?

    Thanks,

    Rick

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim KeyCells As Range
    Dim CurrentCellContents As String
    Dim CellHistory As String

    Set KeyCells = Range("F4:F1000")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
       CurrentCellContents = Target() 'What is the current Sprint? <--- stops here with runtime error type mismatch
        CellHistory = Target.Offset(0, 3) 'Is there any previous Sprint history?
        If CellHistory = "" Then
            Target.Offset(0, 3) = CurrentCellContents
        Else
            Target.Offset(0, 3) = CellHistory & ", " & CurrentCellContents
        End If
    End If

    End Sub

    Sunday, January 12, 2020 9:15 PM

Answers

  • In the situation that you describe, the Target will be the entire row that you insert or delete, so the line

    CurrentCellContents = Target

    tries to assign the value of a multi-cell range to a single String variable. That doesn't match.

    To prevent this, insert the following line immediately below the variable declarations:

        If Target.Count > 1 Then Exit Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Rick8181 Wednesday, January 15, 2020 8:59 PM
    Sunday, January 12, 2020 9:22 PM

All replies

  • In the situation that you describe, the Target will be the entire row that you insert or delete, so the line

    CurrentCellContents = Target

    tries to assign the value of a multi-cell range to a single String variable. That doesn't match.

    To prevent this, insert the following line immediately below the variable declarations:

        If Target.Count > 1 Then Exit Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Rick8181 Wednesday, January 15, 2020 8:59 PM
    Sunday, January 12, 2020 9:22 PM
  • Try this too:

     

       CurrentCellContents = Target.Value

     


    • Edited by Viorel_MVP Monday, January 13, 2020 6:12 AM
    Monday, January 13, 2020 6:11 AM
  • That solved the problem. Thanks for the great help. 
    Wednesday, January 15, 2020 9:00 PM