Run Time Error 13 Type Mismatch

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,699
Office Version
  1. 2007
Platform
  1. Windows
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.

Code:
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
Code:
.Value = Choose(m, 12, 4, 10)

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

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

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Re: Run Time Error 13 Type Mismatch advise please

Works for me, but try:

Code:
.Value = Application.WorksheetFunction.Choose(m, 12, 4, 10)
 
Upvote 0
Re: Run Time Error 13 Type Mismatch advise please

Hi,
With that code cell J36 still shows a value of 1
But also cell L36 & O36 now show #N/A

Trying it again using this code .Value = Choose(m, 12, 4, 10)
I then only see the value 1 in cell J36
 
Upvote 0
Re: Run Time Error 13 Type Mismatch advise please

Hi,
With that code cell J36 still shows a value of 1
But also cell L36 & O36 now show #N/A

Trying it again using this code .Value = Choose(m, 12, 4, 10)
I then only see the value 1 in cell J36


The 2 functions work for me.
What do you have in "m" during the execution?
What do you have in G36 during the execution?
Try the following:

Code:
         m = Application.Match(Target.Value, Array("INTERNATIONAL SIGNED FOR", _
                                                    "UK SIGNED FOR", _
                                                    "UK SPECIAL DELIVERY"), 0)
        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
 
Upvote 0
Re: Run Time Error 13 Type Mismatch advise please

Ok
In G36 would be the dropdown list
In m would be empty.

With the code in the last post i get the same error and when i debug this is in yellow

Code:
 Case 1:
 
Upvote 0
Re: Run Time Error 13 Type Mismatch advise please

Ok
In G36 would be the dropdown list
In m would be empty.

With the code in the last post i get the same error and when i debug this is in yellow

Code:
 Case 1:

In cell G36 you must bring a value, check that the data in cell G36 matches one of your values ​​in the code:
"INTERNATIONAL SIGNED FOR", "UK SIGNED FOR", "UK SPECIAL DELIVERY"


Now, Try this:
Code:
         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
 
Upvote 0
Re: Run Time Error 13 Type Mismatch advise please

So here is what i have so far.
I get a block if without end if, this is just when i open the workbook & without even touching the worksheet in question.

Ive looked at the code and i have the same amount

Code:
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
                
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 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
 
Upvote 0
Re: Run Time Error 13 Type Mismatch advise please

So here is what i have so far.
I get a block if without end if, this is just when i open the workbook & without even touching the worksheet in question.

Ive looked at the code and i have the same amount

I did not understand.
Your code already works?
 
Upvote 0
Re: Run Time Error 13 Type Mismatch advise please

No not yet,
I put your code in from post #6 and get this block if without if message.
so far i am unable to advise if the code at post #6 works.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

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
Back
Top