Run Time Error 13 Type Mismatch

I

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,226
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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
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

ipbr21054 said:
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
Click to expand...


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

ipbr21054 said:
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:
Click to expand...

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"
Click to expand...


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

ipbr21054 said:
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
Click to expand...

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
You must log in or register to reply here.

Similar threads

I
  • Question
Look for value in column A then check if cell has a value
Replies
4
Views
325
ipbr21054
I
I
  • Question
Advice for if cell has a value then
Replies
7
Views
273
6StringJazzer
6StringJazzer
I
  • Solved
If Else advice to allow or forget transfer of values
Replies
7
Views
368
ipbr21054
I
I
  • Solved
Run time error 13 when closing worksheet
Replies
4
Views
325
ipbr21054
I
A
  • Solved
Getting run-time errors on code that auto-inserts dates
Replies
5
Views
557
rollis13
rollis13

Forum statistics

Threads
1,215,028
Messages
6,122,749
Members
449,094
Latest member
dsharae57

Share this page

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