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

arbitrabbit

New Member
Joined
Mar 23, 2012
Messages
8
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:
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)
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

JamesW

Well-known Member
Joined
Oct 30, 2009
Messages
1,197
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:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,577
Messages
5,596,996
Members
414,116
Latest member
sfullnet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top