Run-time error '13'

[Randomhero180]

If any one can help me debug this code that would be awesome.

It all works properly but when I do some things like fill multiple cells anywhere on the sheet I get a

Run-time error '13'
Type-mismatch

Like i said it is not when I am doing anything in the cells that utilize the code, its anywhere, it doesn't prevent me from using it, just annoying for the other people using it.

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngData As Range
     
    Set rngData = ActiveSheet.Range("B19:G12679")
     
    If Not Application.Intersect(rngData, Target) Is Nothing Then
        Cells(Target.Row, 8).Value = Now
    End If
    
    If Target.Cells.Value = "Estimate Approved" Then
        Cells(Target.Row, 10).Value = Now
    End If
    
    If Target.Cells.Value = "Contact Sheet Available/Disc Sent" Then
        Cells(Target.Row, 11).Value = Now
    End If
    
    Set rngData = Nothing
     
End Sub

Thanks in advance!

RH

 

[VoG]

Try removing .Cells

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngData As Range
     
    Set rngData = ActiveSheet.Range("B19:G12679")
     
    If Not Application.Intersect(rngData, Target) Is Nothing Then
        Cells(Target.Row, 8).Value = Now
    End If
    
    If Target.Value = "Estimate Approved" Then
        Cells(Target.Row, 10).Value = Now
    End If
    
    If Target.Value = "Contact Sheet Available/Disc Sent" Then
        Cells(Target.Row, 11).Value = Now
    End If
    
    Set rngData = Nothing
     
End Sub

 

[Randomhero180]

Code still works, but I am still getting the

Run-time '13' error

 

[Jonmo1]

Try like this

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngData As Range
    If Target.Count > 1 Then Exit Sub
    Set rngData = ActiveSheet.Range("B19:G12679")
 
    If Not Application.Intersect(rngData, Target) Is Nothing Then
        Cells(Target.Row, 8).Value = Now
    'End If  <--Moved this End If to the end...
 
        If Target.Value = "Estimate Approved" Then
            Cells(Target.Row, 10).Value = Now
        End If
 
        If Target.Value = "Contact Sheet Available/Disc Sent" Then
            Cells(Target.Row, 11).Value = Now
        End If
    End If
    Set rngData = Nothing
 
End Sub

Hope that helps.

 

[Randomhero180]

You rock Jonmo1 Thanks for the help!

 

[Jonmo1]

Glad to help...

Come to think of it, you should probably also turn off Events...

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngData As Range
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Set rngData = ActiveSheet.Range("B19:G12679")
 
If Not Application.Intersect(rngData, Target) Is Nothing Then
    Cells(Target.Row, 8).Value = Now
 
    If Target.Value = "Estimate Approved" Then
        Cells(Target.Row, 10).Value = Now
    End If
 
    If Target.Value = "Contact Sheet Available/Disc Sent" Then
        Cells(Target.Row, 11).Value = Now
    End If
End If
Set rngData = Nothing
Application.EnableEvents = True
End Sub

 

[Randomhero180]

if i do that then the last two if statements dont work

    If Target.Value = "Estimate Approved" Then
        Cells(Target.Row, 10).Value = Now
    End If
 
    If Target.Value = "Contact Sheet Available/Disc Sent" Then
        Cells(Target.Row, 11).Value = Now
    End If

Any the NOW part of the code does not update everytime the cell value changes, which is what I am looking for. The first solution you posted works great.

 

[Jonmo1]

Sure they do, why wouldn't they?

 

[Randomhero180]

i don't know I tested it and it didn't update

 

[Randomhero180]

my fault, i put false false instead of false true.





Thanks again jonmo