ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,737
- Office Version
- 2007
- Platform
- 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.
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
The full code where it is in use is as follows.
many Thansk
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