Run Time Error 13 Type Mismatch

[ipbr21054]

Evening,
After i print my invoice i then use a button to clear the cells on my worksheet..

This is the code that is used on the button.

Private Sub Clear_Invoice_After_Printing_Click()    Dim strFileName As String
    
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\" & Range("N4").Value & ".pdf"
    If Dir(strFileName) <> vbNullString Then
        MsgBox "INVOICE " & Range("N4").Value & " WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly
        Exit Sub
    End If
    
    With ActiveSheet
        .PageSetup.PrintArea = "$G$3:$O$60"
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
        MsgBox "INVOICE " & Range("N4").Value & " WAS SAVED SUCCESSFULLY", vbInformation + vbOKOnly
        Range("G13:I18").ClearContents
        Range("N14:O18").ClearContents
        Range("G27:N35").ClearContents
        Range("G36:N36").ClearContents
        Range("G47:I51").ClearContents
        Range("N4").Value = Range("N4").Value + 1
        Worksheets("INV2").Range("N4").Value = Range("N4").Value
        Range("G13").Select
        ActiveWorkbook.Save
    End With
End Sub

When i now press that button i see an error message of which is run Time Error 13, Type mismatch.
I notice that there is a value of 1 in cell J36 that did not clear.

When i debug i see this shown in yellow

.Value = Choose(m, 12, 4, 10)

The full code where it is in use is as follows.

Private Sub Worksheet_Change(ByVal Target As Range)Dim C As Range, d As Range
If Not Intersect(Target, Range("G13:O17, G27:O42")) Is Nothing Then
Application.EnableEvents = False
    For Each C In Intersect(Target, Range("G13:O17, G27:O42"))
        If C.Column <> 14 Then
            If Not C.HasFormula Then C = UCase(C)
        End If
    Next C
    If Intersect(Target, Range("G13")) Is Nothing Then GoTo NxEvent
    Dim rName As Range, srcWS As Worksheet
    Set srcWS = Sheets("DATABASE")
    Set rName = srcWS.Range("A6:A" & srcWS.Cells(srcWS.Rows.Count, 1).End(xlUp).Row).Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not rName Is Nothing Then
        Range("N15") = srcWS.Range("B" & rName.Row)
        Range("N14") = srcWS.Range("D" & rName.Row)
        Range("N16") = srcWS.Range("L" & rName.Row)
        Range("N17") = srcWS.Range("W" & rName.Row)
        Range("G14") = srcWS.Range("R" & rName.Row)
        Range("G15") = srcWS.Range("S" & rName.Row)
        Range("G16") = srcWS.Range("T" & rName.Row)
        Range("G17") = srcWS.Range("U" & rName.Row)
        Range("G18") = srcWS.Range("V" & rName.Row)
        Application.EnableEvents = True
        End If
End If
                
NxEvent: If Not Intersect(Target, Range("G36")) Is Nothing Then
m = Application.Match(Target.Value, Array("INTERNATIONAL SIGNED FOR", _
                                                   "UK SIGNED FOR", _
                                                   "UK SPECIAL DELIVERY"), 0)
        If Not IsError(m) Then
            Application.EnableEvents = False
            Me.Range("J36").Value = 1
            With Me.Range("L36")
                .Value = Choose(m, 12, 4, 10)
                .NumberFormat = "£0.00"
            End With
        End If
    End If
exitsub:
Application.EnableEvents = True
End Sub

many Thansk

 

[DanteAmor]

Re: Run Time Error 13 Type Mismatch advise please

Not yet
since putting the code from post #6 into my sheet i then see block if without if so unable to advise if the code from post #6 works

I already put 3 alternatives and all work

You can only run one event.
Remove the first event of your code.
Change the data in G36 and debug the code.
But only test an event:

Private Sub Worksheet_Change(ByVal Target As Range)

 If Not Intersect(Target, Range("G36")) Is Nothing Then
         m = Application.Match(Target.Value, Array("INTERNATIONAL SIGNED FOR", _
                                                    "UK SIGNED FOR", _
                                                    "UK SPECIAL DELIVERY"), 0)
If m <> "" Then
        If Not IsError(m) Then
            Me.Range("J36").Value = 1
            With Range("L36")
                '.Value = Choose(m, 12, 4, 10)
                Select Case m
                    Case 1: .Value = 12
                    Case 2: .Value = 4
                    Case 3: .Value = 10
                    Case Else: MsgBox "Value : " & m
                End Select
                .NumberFormat = "£0.00"
            End With
        End If
Else
   MsgBox "m is empty"
End If
exitsub:
Application.EnableEvents = True
End Sub