How to avoid the type mismatch error when a row is added?

[arbitrabbit]

Hi

I am trying to create a spreadsheet with commercial leads where I need to capture the status of date when a particular cell takes a specific value.

The cell to be monitor is in column K and the user can select several values from a drop down list based on the current status of the lead. The statuses are values like Lead qualified, Offer sent, Offer Accepted, Drawn Down etc.

Corresponding to each state, I want to capture the date in a paricular column e.g. if status if offer sent, I capture the date in one column and if it is offer accepted, I capture it in another column, so that I have the end to end tracking as the deal moves through various stages.

To do so, I came up with the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("k:k")) Is Nothing Then
    Select Case Target.Value
    Case "Offer"
        Cells(Target.Row, 20) = Date
     Case "Offer Accepted"
       Cells(Target.Row, 21) = Date
     Case "Drawn Down"
        Cells(Target.Row, 22) = Date
     End Select
          
End If
End Sub

The code works well but whenever a new row is inserted or deleted (which happens reasonably frequently), I get an error and the code stops. I was wondering if there is any way to avoid that error when a new row/column is inserted?

Also, can I improve this code any more (Sorry, am bit of a VBA novice and have scraped together this code from bits and pieces I gleaned all over the place)

 

[JamesW]

Hi,

You need to set Target.Count - When you insert a new row it's trying to check the value of Target, but target is a massive range of values

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("k:k")) Is Nothing And [B]Target.count = 1[/B] Then
        Select Case Target.Value
        Case "Offer"
            Cells(Target.Row, 20) = Date
         Case "Offer Accepted"
           Cells(Target.Row, 21) = Date
         Case "Drawn Down"
            Cells(Target.Row, 22) = Date
         End Select
              
    End If
End Sub

 

[arbitrabbit]

Thanks. That seems to do the trick.