API tools faq
paste
Advertisement
Guest User

Untitled

a guest
May 22nd, 2019
90
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
VisualBasic 14.65 KB | None | 0 0
raw download clone embed print report
  1. 'Align columns
  2. Columns("A").ColumnWidth = 1.67
  3. Columns("B").ColumnWidth = 3.11
  4. Columns("C").ColumnWidth = 13.67
  5. Columns("D").ColumnWidth = 6.11
  6. Columns("E").ColumnWidth = 32.89
  7. Columns("F").ColumnWidth = 6.56
  8. Columns("G").ColumnWidth = 15.33
  9. Columns("H").ColumnWidth = 11.78
  10. Columns("I").ColumnWidth = 1.56
  11.  
  12. 'Align Header Rows
  13. Rows(1).RowHeight = 11.4
  14. Rows(2).RowHeight = 10.8
  15. Rows(3).RowHeight = 19.2
  16. Rows(4).RowHeight = 21
  17. Rows(5).RowHeight = 14.4
  18. Rows(6).RowHeight = 14.4
  19. Rows(7).RowHeight = 12
  20. Rows(8).RowHeight = 12
  21. Rows(9).RowHeight = 12
  22. Rows(10).RowHeight = 12
  23. Rows(11).RowHeight = 12
  24. Rows(12).RowHeight = 27
  25. Rows(13).RowHeight = 14.4
  26. Rows(14).RowHeight = 14.4
  27.  
  28. 'Font Settings
  29. Range(Cells(12, 1), Cells(moduleStart + 100, 9)).Font.Name = "Arial"
  30. Range(Cells(12, 1), Cells(moduleStart + 100, 9)).Font.Size = 8
  31.  
  32. 'Get Account info for Header
  33. acctName = Sheets(1).Cells(21, 2).Value
  34. acctAddy = Sheets(1).Cells(22, 2).Value
  35. acctCity = Sheets(1).Cells(24, 2).Value
  36. acctProv = Sheets(1).Cells(26, 2).Value
  37. acctPostal = Sheets(1).Cells(27, 2).Value
  38. acctContact = Sheets(1).Cells(30, 4).Value
  39. acctPhone = Sheets(1).Cells(28, 4).Value
  40. acctFax = Sheets(1).Cells(29, 4).Value
  41. acctEmail = Sheets(1).Cells(31, 4).Value
  42. acctRep = Sheets(1).Cells(12, 2).Value
  43. acctBilling = Sheets(1).Cells(22, 4).Value
  44. acctPO = Sheets(1).Cells(18, 2).Value
  45.  
  46. 'Inject account info into Header
  47. Cells(5, 4).Value = acctRep
  48. Cells(7, 4).Value = acctName
  49. Cells(8, 4).Value = acctContact
  50. Cells(9, 4).Value = acctBilling
  51.  
  52.  
  53. If acctPO = "" Then
  54.     Cells(5, 7).Value = "No Purchase Order provided"
  55. Else
  56.     Cells(5, 7).Value = acctPO
  57. End If
  58.  
  59.  
  60. If acctFax = "" Then
  61.     Cells(8, 7).Value = "No Fax number provided"
  62. Else
  63.     Cells(8, 7).Value = acctFax
  64. End If
  65.  
  66.  
  67. Cells(4, 7).Value = Date
  68. Range(Cells(4, 7), Cells(4, 7)).NumberFormat = "mmm dd, yyyy"
  69. Range(Cells(4, 7), Cells(4, 7)).HorizontalAlignment = xlCenter
  70. Cells(6, 7).Value = acctContact
  71. Cells(7, 7).Value = acctPhone
  72. Cells(9, 7).Value = acctEmail
  73.  
  74.  
  75. Dim thisModel As String
  76. Dim lastModel As String
  77. Dim lastLocation As String
  78. Dim thisTax As Double
  79. Dim onTax As Double
  80. Dim bcTax As Double
  81. Dim mbTax As Double
  82. Dim nfTax As Double
  83. Dim ntTax As Double
  84. Dim nsTax As Double
  85. Dim nuTax As Double
  86. Dim peTax As Double
  87. Dim qcTax As Double
  88. Dim skTax As Double
  89. Dim ykTax As Double
  90. Dim appText As String
  91.  
  92. appText = "APPLICATION: " & Chr(10)
  93. appText = appText & "You agree to purchase the equipment, software licenses and/or software maintenance and support products listed above in accordance with the terms and conditions stated above and set out overleaf. You agree to pay to us the payments set forth above. You agree that all information set out herein is correct and that all particulars were complete when this Agreement was signed by you. You acknowledge having read the terms and conditions of this Agreement set forth on this page and overleaf, and agree that no other terms and conditions, express or implied, are part of this Agreement unless they appear above or in a schedule or addendum, and in either event are initialed by both of us to indicate they form part of this Agreement." & Chr(10)
  94. appText = appText & "RETURNS:" & Chr(10)
  95. appText = appText & "No equipment, software or software maintenance and support products may be returned without Document Direction Limited’s prior written consent, which may be withheld at our sole discretion. Merchandise returned without written authorization may not be accepted at the receiving dock and remains your sole responsibility. On returns authorized by us in advance, you agree to pay a restocking charge determined by us. All claims for damaged equipment shall be deemed waived unless made in writing and delivered to us within five days after your receipt of the applicable equipment" & Chr(10)
  96. appText = appText & "PAYMENT:" & Chr(10)
  97. appText = appText & "The purchase price for equipment and software is invoiced upon Document Direction Limited’s shipment of the applicable product to you, notwithstanding date of installation. Software is deemed to be shipped at the time the applicable license key is downloaded. Software maintenance and support is invoiced immediately following execution of this Agreement. You agree to pay invoices in accordance with their terms." & Chr(10)
  98. appText = appText & "SIGNATURE:"
  99.  
  100. 'Sales tax Rates
  101. abTax = 0.05
  102. bcTax = 0.12
  103. mbTax = 0.13
  104. nbTax = 0.15
  105. nfTax = 0.15
  106. ntTax = 0.05
  107. nsTax = 0.15
  108. nuTax = 0.05
  109. onTax = 0.13
  110. peTax = 0.13
  111. qcTax = 0.14975
  112. skTax = 0.11
  113. ykTax = 0.05
  114.  
  115. machineIndex = 16
  116. modelQty = Sheets(2).Cells(machineIndex, 27).Value
  117. moduleStart = 13
  118. modelsToCheckStart = 33
  119. standardSheetNumber = 16
  120. mySheets = Worksheets.Count
  121. statndardRowsInModelDesc = 15
  122. modelTypes = Sheets(2).Cells(Rows.Count, "AB").End(xlUp).Row - statndardRowsInModelDesc
  123.  
  124.  
  125. For i = standardSheetNumber To mySheets
  126.        
  127.     'Get
  128.    thisLocation = Sheets(i).Cells(8, 2).Value
  129.     thisProv = Sheets(i).Cells(10, 2).Value
  130.     thisModel = Sheets(i).Cells(16, 2).Value
  131.  
  132.  
  133.     'if address of this machine matches address of the last machine, check if the machine is the same model
  134.    If thisLocation <> lastLocation Or thisModel <> lastModel Then
  135.    
  136.         'Set Quantity
  137.        Cells(moduleStart, 2).Value = 1
  138.    
  139.         'Set Location
  140.        Cells(moduleStart, 5).Value = thisLocation & " - " & thisProv
  141.        
  142.         'Set Province
  143.        Cells(moduleStart, 6).Value = thisProv
  144.  
  145.         'Set Model
  146.        Cells(moduleStart, 3).Value = thisModel
  147.        
  148.         'Set Tax
  149.        Select Case thisProv
  150.             Case "ON"
  151.                 thisTax = onTax
  152.             Case "BC"
  153.                 thisTax = bcTax
  154.             Case "MB"
  155.                 thisTax = mbTax
  156.             Case "NB"
  157.                 thisTax = nbTax
  158.             Case "NF"
  159.                 thisTax = nfTax
  160.             Case "NT"
  161.                 thisTax = ntTax
  162.             Case "NS"
  163.                 thisTax = nsTax
  164.             Case "NU"
  165.                 thisTax = nuTax
  166.             Case "PE"
  167.                 thisTax = peTax
  168.             Case "AB"
  169.                 thisTax = abTax
  170.             Case "QC"
  171.                 thisTax = qcTax
  172.             Case "SK"
  173.                 thisTax = skTax
  174.             Case "YK"
  175.                 thisTax = ykTax
  176.         End Select
  177.            
  178.         'if quantity < 1 then add 1 to machineIndex
  179.        If modelQty <= 1 Then
  180.             machineIndex = machineIndex + 1
  181.         Else
  182.             modelQty = modelQty - 1
  183.         End If
  184.  
  185.         'Increment line pointer
  186.        moduleStart = moduleStart + 1
  187.        
  188.         lastLocation = thisLocation
  189.         lastModel = thisModel
  190.  
  191.     Else
  192.         'increment Qty and move to the next iteration
  193.       Cells(moduleStart - 1, 2).Value = Cells(moduleStart - 1, 2).Value + 1
  194.     End If
  195.    
  196.    
  197.     For j = 0 To modelTypes - 1
  198.         modelToCheck = Sheets(2).Cells(modelsToCheckStart + j, 18).Value
  199.        
  200.         If thisModel = modelToCheck Then
  201.             ' set amount
  202.            Cells(moduleStart - 1, 7).Value = Sheets(2).Cells(modelsToCheckStart + j, 20).Value / Sheets(2).Cells(modelsToCheckStart + j, 16).Value
  203.             Cells(moduleStart - 1, 8).Value = Cells(moduleStart - 1, 7).Value * thisTax
  204.             Exit For
  205.         End If
  206.     Next j
  207.  
  208.     'Multiply amount by quantity
  209.    Cells(moduleStart - 1, 7).Value = Cells(moduleStart - 1, 7).Value * Cells(moduleStart - 1, 2).Value
  210.     Cells(moduleStart - 1, 8).Value = Cells(moduleStart - 1, 8).Value * Cells(moduleStart - 1, 2).Value
  211.    
  212.     'Add in borders for layout
  213.    Range(Cells(moduleStart - 1, 9), Cells(moduleStart - 1, 9)).Borders(xlEdgeRight).LineStyle = xlContinuous
  214.     Range(Cells(moduleStart - 1, 8), Cells(moduleStart - 1, 8)).Borders(xlEdgeRight).LineStyle = xlContinuous
  215.     Range(Cells(moduleStart - 1, 2), Cells(moduleStart - 1, 2)).Borders(xlEdgeLeft).LineStyle = xlContinuous
  216.     Range("G13:H100").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
  217.    
  218.     'Align text
  219.    Range(Cells(moduleStart - 1, 2), Cells(moduleStart - 1, 6)).HorizontalAlignment = xlCenter
  220.    
  221. Next i
  222.  
  223. 'Insert Footer
  224. Range(Cells(moduleStart - 1, 2), Cells(moduleStart - 1, 8)).Borders(xlEdgeBottom).LineStyle = xlContinuous
  225. Range(Cells(moduleStart + 33, 1), Cells(moduleStart + 33, 9)).Borders(xlEdgeBottom).LineStyle = xlContinuous
  226. Range(Cells(moduleStart - 1, 9), Cells(moduleStart + 33, 9)).Borders(xlEdgeRight).LineStyle = xlContinuous
  227. Range(Cells(moduleStart - 5, 1), Cells(moduleStart + 33, 1)).Borders(xlEdgeLeft).LineStyle = xlContinuous
  228.  
  229.  
  230. For i = 4 To 10
  231.     Range(Cells(moduleStart + i, 6), Cells(moduleStart + i, 7)).Merge
  232. Next i
  233.  
  234. Range(Cells(moduleStart + 4, 6), Cells(moduleStart + 10, 7)).HorizontalAlignment = xlRight
  235.  
  236. 'Settlement Details
  237. Cells(moduleStart + 3, 2).Value = "Settlement Details:"
  238. Cells(moduleStart + 3, 2).Font.Bold = True
  239. Range(Cells(moduleStart + 4, 2), Cells(moduleStart + 4, 5)).Merge
  240. Range(Cells(moduleStart + 4, 2), Cells(moduleStart + 4, 5)).Borders(xlEdgeBottom).LineStyle = xlContinuous
  241. Range(Cells(moduleStart + 5, 2), Cells(moduleStart + 5, 5)).Merge
  242. Range(Cells(moduleStart + 5, 2), Cells(moduleStart + 5, 5)).Borders(xlEdgeBottom).LineStyle = xlContinuous
  243. Range(Cells(moduleStart + 6, 2), Cells(moduleStart + 6, 5)).Merge
  244. Range(Cells(moduleStart + 6, 2), Cells(moduleStart + 6, 5)).Borders(xlEdgeBottom).LineStyle = xlContinuous
  245. Range(Cells(moduleStart + 7, 2), Cells(moduleStart + 7, 5)).Merge
  246. Range(Cells(moduleStart + 7, 2), Cells(moduleStart + 7, 5)).Borders(xlEdgeBottom).LineStyle = xlContinuous
  247. Range(Cells(moduleStart + 8, 2), Cells(moduleStart + 8, 5)).Merge
  248. Range(Cells(moduleStart + 8, 2), Cells(moduleStart + 8, 5)).Borders(xlEdgeBottom).LineStyle = xlContinuous
  249. Range(Cells(moduleStart + 9, 2), Cells(moduleStart + 9, 5)).Merge
  250. Range(Cells(moduleStart + 9, 2), Cells(moduleStart + 9, 5)).Borders(xlEdgeBottom).LineStyle = xlContinuous
  251. Range(Cells(moduleStart + 10, 2), Cells(moduleStart + 10, 5)).Merge
  252.  
  253. 'Net Value before Tax
  254. Cells(moduleStart + 4, 6).Value = "Net Value Before Tax:"
  255. Cells(moduleStart + 4, 6).Font.Bold = True
  256. Cells(moduleStart + 4, 8).Font.Italic = True
  257. Cells(moduleStart + 4, 8).Borders(xlEdgeBottom).LineStyle = xlContinuous
  258. Cells(moduleStart + 4, 8).Value = Application.Sum(Range(Cells(12, 7), Cells(moduleStart, 7)))
  259.  
  260. 'Total Taxes
  261. Cells(moduleStart + 6, 6).Value = "Total Taxes:"
  262. Cells(moduleStart + 6, 6).Font.Bold = True
  263. Cells(moduleStart + 6, 8).Font.Italic = True
  264. Cells(moduleStart + 6, 8).Borders(xlEdgeBottom).LineStyle = xlContinuous
  265. Cells(moduleStart + 6, 8).Value = Application.Sum(Range(Cells(12, 8), Cells(moduleStart, 8)))
  266.  
  267. 'TOTAL
  268. Cells(moduleStart + 8, 6).Value = "TOTAL:"
  269. Cells(moduleStart + 8, 6).Font.Bold = True
  270. Cells(moduleStart + 8, 8).Font.Italic = True
  271. Cells(moduleStart + 8, 8).Borders(xlEdgeBottom).LineStyle = xlContinuous
  272. Cells(moduleStart + 8, 8).Value = Application.Sum(Range(Cells(moduleStart + 4, 8), Cells(moduleStart + 6, 8)))
  273.  
  274. 'Special Provisions
  275. Cells(moduleStart + 11, 2).Value = "Special Provisions:"
  276. Cells(moduleStart + 11, 2).VerticalAlignment = xlVAlignTop
  277. Cells(moduleStart + 11, 2).Font.Size = 8
  278. Rows(moduleStart + 11).RowHeight = 27
  279. Rows(moduleStart + 12).RowHeight = 6
  280.  
  281. 'Customer Initial
  282. Cells(moduleStart + 11, 8).Value = "Customer" & Chr(10) & " Initial:"
  283. Cells(moduleStart + 11, 8).VerticalAlignment = xlVAlignTop
  284. Cells(moduleStart + 11, 8).Font.Size = 8
  285. Range(Cells(moduleStart + 11, 2), Cells(moduleStart + 11, 8)).BorderAround ColorIndex:=1
  286. Range(Cells(moduleStart + 11, 2), Cells(moduleStart + 11, 8)).IndentLevel = 0
  287. Range(Cells(moduleStart + 11, 2), Cells(moduleStart + 11, 6)).Merge
  288.  
  289. 'Application Text
  290. Range(Cells(moduleStart + 12, 2), Cells(moduleStart + 25, 8)).Merge
  291. Cells(moduleStart + 12, 2).Value = appText
  292. Rows(moduleStart + 12).RowHeight = 10
  293. Cells(moduleStart + 12, 2).Characters(1, 15).Font.Bold = True
  294. Cells(moduleStart + 12, 2).Characters(15, 748).Font.Bold = False
  295. Cells(moduleStart + 12, 2).Characters(749, 757).Font.Bold = True
  296. Cells(moduleStart + 12, 2).Characters(758, 1334).Font.Bold = False
  297. Cells(moduleStart + 12, 2).Characters(1335, 1345).Font.Bold = True
  298. Cells(moduleStart + 12, 2).Characters(1346, 1759).Font.Bold = False
  299. Cells(moduleStart + 12, 2).Characters(1760, 1780).Font.Bold = True
  300.  
  301. 'Signature
  302. Range(Cells(moduleStart + 26, 2), Cells(moduleStart + 26, 6)).Merge
  303. Range(Cells(moduleStart + 26, 2), Cells(moduleStart + 27, 6)).HorizontalAlignment = xlCenter
  304. Range(Cells(moduleStart + 26, 2), Cells(moduleStart + 26, 6)).BorderAround ColorIndex:=1
  305. Cells(moduleStart + 26, 2).Value = "Signature(s) of Customer(s)"
  306.  
  307. 'Acceptance
  308. Range(Cells(moduleStart + 26, 7), Cells(moduleStart + 26, 8)).Merge
  309. Range(Cells(moduleStart + 26, 7), Cells(moduleStart + 26, 8)).BorderAround ColorIndex:=1
  310. Cells(moduleStart + 26, 7).Value = "Acceptance by Document Direction Limited"
  311. Cells(moduleStart + 26, 7).Font.Size = 7
  312. Rows(moduleStart + 26).RowHeight = 14
  313.  
  314. 'Signature (2)
  315. Range(Cells(moduleStart + 27, 2), Cells(moduleStart + 27, 4)).Merge
  316. Range(Cells(moduleStart + 27, 2), Cells(moduleStart + 27, 4)).BorderAround ColorIndex:=1
  317. Range(Cells(moduleStart + 27, 2), Cells(moduleStart + 27, 8)).Font.Size = 8
  318. Cells(moduleStart + 27, 2).Value = "Signature"
  319.  
  320. 'Print name and Position
  321. Cells(moduleStart + 27, 5).Value = "Print Name and Position"
  322. Cells(moduleStart + 27, 5).BorderAround ColorIndex:=1
  323.  
  324. 'Date Signed
  325. Cells(moduleStart + 27, 6).Value = "Date" & Chr(10) & "Signed"
  326. Cells(moduleStart + 27, 6).VerticalAlignment = xlCenter
  327. Cells(moduleStart + 27, 6).BorderAround ColorIndex:=1
  328.  
  329. 'DDL Signature
  330. Cells(moduleStart + 27, 7).Value = "Signature of Document Direction Limited"
  331. Range(Cells(moduleStart + 27, 7), Cells(moduleStart + 27, 8)).Merge
  332. Range(Cells(moduleStart + 27, 7), Cells(moduleStart + 27, 8)).BorderAround ColorIndex:=1
  333.  
  334. 'bind to customer
  335. Range(Cells(moduleStart + 28, 2), Cells(moduleStart + 32, 4)).Merge
  336. Range(Cells(moduleStart + 28, 2), Cells(moduleStart + 32, 4)).WrapText = True
  337. Range(Cells(moduleStart + 28, 2), Cells(moduleStart + 32, 4)).BorderAround ColorIndex:=1
  338. Cells(moduleStart + 28, 2).Value = "I have the authority to bind the Customer"
  339. Cells(moduleStart + 28, 2).HorizontalAlignment = xlCenter
  340. Cells(moduleStart + 28, 2).VerticalAlignment = xlBottom
  341.  
  342. 'Name Slot
  343. Range(Cells(moduleStart + 28, 5), Cells(moduleStart + 32, 5)).Merge
  344. Range(Cells(moduleStart + 28, 5), Cells(moduleStart + 32, 5)).BorderAround ColorIndex:=1
  345.  
  346. 'Date Signed Slot
  347. Range(Cells(moduleStart + 28, 6), Cells(moduleStart + 32, 6)).Merge
  348. Range(Cells(moduleStart + 28, 6), Cells(moduleStart + 32, 6)).BorderAround ColorIndex:=1
  349.  
  350. 'Signature Slot
  351. Range(Cells(moduleStart + 28, 7), Cells(moduleStart + 32, 8)).Merge
  352. Range(Cells(moduleStart + 28, 7), Cells(moduleStart + 32, 8)).BorderAround ColorIndex:=1
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!