- Sesion # 01. Membuat Worksheets Product
- Sesion # 02. Membuat Worksheets AddStock
- Sesion # 03. Membuat Worksheets Invoice
- Sesion # 04. Membuat Worksheets Statement
- Sesion # 05. Membuat Worksheets Template
Sesion #3. Membuat Worksheets Invoice
Formula
Add This Formula to :
Cell D27 =IF(E27<>"",COUNTA($E$27:E27),"")
Cell H27 =VLOOKUP(F27&G27,CHOOSE({1,2},Product!$D$9:$D$1000&Product!$E$9:$E$998,Product!$F$9:$F$1000),2,FALSE)
Cell I27 =IF(ISNA(VLOOKUP($H27,Product!F9:G1000,2,0)),"",VLOOKUP($H27,Product!F9:G1000,2,0))
Cell J27 =IF(I27="","",E27*I27)
Cell L27 =IF(J27="","",IF(K27="",J27,J270-(J270*K27)))
Cell J14 =IF(ISNA(VLOOKUP($J$14,Customer,3,0)),"",VLOOKUP($J$14,Customer,3,0))
Cell J15 =IF(ISNA(VLOOKUP($J$14,Customer,4,0)),"",VLOOKUP($J$14,Customer,4,0))
Cell J16 =IF(ISNA(VLOOKUP($J$14,Customer,5,0)),"",VLOOKUP($J$14,Customer,5,0))
Cell J17 =IF(ISNA(VLOOKUP($J$14,Customer,6,0)),"",VLOOKUP($J$14,Customer,6,0))
Cell J14 =IF(ISNA(VLOOKUP($J$14,Customer,7,0)),"",VLOOKUP($J$14,Customer,7,0))
Cell K38 = SUM(J27:J36)
Cell K39 = K38-K40
Cell K40 = SUM(L27:L36)
Cell C3 = F22
Cell E3 = F21
Cell F3 = J13
Cell G3 = F23
Cell H3 = K38
Cell I3 = K39
Cell J3 = K40
Cell K3 = F38
Gambar Show Formula pada WorkSheets Invoice
Gambar WorkSheets Sales
Data Validation
Cell J13 = Customer_Name
Cell F27 =Category
Cell G27 =OFFSET(Product!$E$9,MATCH($F27,Category_Product,0)-1,0,COUNTIF(Category_Product,$F27),1)
Cell K27 = .1,.2,.3,.4,.5,.6,.7,.8,.9
Dynamic Name Range :
Customer_Name
=OFFSET(Customer!$B$4,,,COUNTA(Customer!$B$4:$B$1000))
Customer
=OFFSET(Customer!$B$4,,,COUNTA(Customer!$B$4:$B$1000),7)
List_Sale
=OFFSET(Invoice!$Z$9,,,MATCH(9.99999999999999E+307,Invoice!$Z$9:$Z$52),6)
Clear_Invoice
=Invoice!$J$13
,Invoice!$F$22, Invoice!$F$23, Invoice!$J$21:$J$23,
Invoice!$E$27:$G$36,Invoice!$K$27:$K$36
Module
Sub CreateInvoice()
'error handler
On Error GoTo Excell_Invoice:
'variables
Dim RnSales As Range 'destinasi range
Dim RnSrc As Range 'source range
'destination variable
Set RnSales = Worksheets("Sales").Cells(Rows.Count,
2).End(xlUp).Offset(1, 0) 'range("b5")
Application.ScreenUpdating = False
If Worksheets("Invoice").Range("F21") = ""
Then
MsgBox "The invoice number is missing"
Exit Sub
ElseIf Worksheets("Invoice").Range("F22") =
"" Then
MsgBox "Please add the Date"
Exit Sub
ElseIf Worksheets("Invoice").Range("J13") =
"" Then
MsgBox "Please add the Customer"
Exit Sub
Else
'give the user a chance to exit here
Select Case MsgBox _
("You are about to finalise this invoice." _
& vbCrLf & "Check everything before you proceed", _
vbYesNo Or vbExclamation, "Are you sure?")
Case vbYes
Case vbNo
Exit Sub
End Select
Set RnSrc = Worksheets("Invoice").Range("ListSale")
RnSrc.Copy
RnSales.PasteSpecial xlPasteValues
'second sheet
Worksheets("Invoice").Select
'empty clipboard
Application.CutCopyMode = False
'confirmation message
MsgBox "Your invoice has been sent to Invoice Summary" _
& vbCrLf & "and the totals have been sent to Accounts"
'clear the invoicce
Clear_Invoice
End If
Exit Sub
Excell_Invoice:
MsgBox " Error Occurred In Program"
End Sub
Sub Clear_Invoice()
On Error Resume Next
Dim Bersih As Range
Set Bersih = Worksheets("Invoice").Range("Clear_Invoice")
Bersih.Value = ""
'Automatic number Invoice range ("F21")
With Worksheets("Invoice")
If Worksheets("Sales").Range(“D5”).Value=”” Then
Worksheets("Invoice").Range("F21").Value = "INV/"
& Format(Date,”YMMDD”)&"001"
else
Worksheets("Invoice").Range("F21").Value = "INV/"
& Format(Date,”YMMDD”)&"00" &
Right(Worksheets("Sales").Cells(Rows.Count, 4).End(xlUp).Value, 2) +
1
End if
End With
End Sub
Sub PrintInvoice_PDF()
Application.ScreenUpdating = False
Opendialog = Application.GetSaveAsFilename("",
filefilter:="PDF Files (*.pdf), *.pdf", _
Title:="Your Invoice")
If Opendialog = False Then
MsgBox "not successful"
Exit Sub
End If
Set myrange = Worksheets("Invoice").Range("C8:M52")
Worksheets("Invoice").PageSetup.PrintArea = "C8:M52"
On Error Resume Next
myrange.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=Opendialog, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
On Error GoTo 0
ActiveSheet.DisplayPageBreaks = False
Application.ScreenUpdating = False
End Sub
No comments:
Post a Comment