API tools faq
paste
Advertisement
Guest User

Untitled

a guest
May 22nd, 2019
86
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
VisualBasic 15.31 KB | None | 0 0
raw download clone embed print report
  1. Sub Bos()
  2. 'Align columns
  3. Columns("A").ColumnWidth = 1.67
  4. Columns("B").ColumnWidth = 3.11
  5. Columns("C").ColumnWidth = 13.67
  6. Columns("D").ColumnWidth = 6.11
  7. Columns("E").ColumnWidth = 32.89
  8. Columns("F").ColumnWidth = 6.56
  9. Columns("G").ColumnWidth = 15.33
  10. Columns("H").ColumnWidth = 11.78
  11. Columns("I").ColumnWidth = 1.56
  12.  
  13. 'Align Header Rows
  14. Rows(1).RowHeight = 11.4
  15. Rows(2).RowHeight = 10.8
  16. Rows(3).RowHeight = 19.2
  17. Rows(4).RowHeight = 21
  18. Rows(5).RowHeight = 14.4
  19. Rows(6).RowHeight = 14.4
  20. Rows(7).RowHeight = 12
  21. Rows(8).RowHeight = 12
  22. Rows(9).RowHeight = 12
  23. Rows(10).RowHeight = 12
  24. Rows(11).RowHeight = 12
  25. Rows(12).RowHeight = 27
  26. Rows(13).RowHeight = 14.4
  27. Rows(14).RowHeight = 14.4
  28.  
  29. 'Font Settings
  30. Range(Cells(12, 1), Cells(moduleStart + 100, 9)).Font.Name = "Arial"
  31. Range(Cells(12, 1), Cells(moduleStart + 100, 9)).Font.Size = 8
  32.  
  33. 'Get Account info for Header
  34. acctName = Sheets(1).Cells(21, 2).Value
  35. acctAddy = Sheets(1).Cells(22, 2).Value
  36. acctCity = Sheets(1).Cells(24, 2).Value
  37. acctProv = Sheets(1).Cells(26, 2).Value
  38. acctPostal = Sheets(1).Cells(27, 2).Value
  39. acctContact = Sheets(1).Cells(30, 4).Value
  40. acctPhone = Sheets(1).Cells(28, 4).Value
  41. acctFax = Sheets(1).Cells(29, 4).Value
  42. acctEmail = Sheets(1).Cells(31, 4).Value
  43. acctRep = Sheets(1).Cells(12, 2).Value
  44. acctBilling = Sheets(1).Cells(22, 4).Value
  45. acctPO = Sheets(1).Cells(18, 2).Value
  46.  
  47. 'Inject account info into Header
  48.  
  49.  
  50. If acctRep = "" Then
  51.     Cells(5, 4).Value = "No Rep assigned"
  52. Else
  53.     Cells(5, 4).Value = acctRep
  54. End If
  55.  
  56.  
  57. If acctName = "" Then
  58.     Cells(7, 4).Value = "No Account Name provided"
  59. Else
  60.     Cells(7, 4).Value = acctName
  61. End If
  62.  
  63.  
  64. If acctBilling = "" Then
  65.     Cells(9, 4).Value = "No Billing Contact provided"
  66. Else
  67.     Cells(9, 4).Value = acctBilling
  68. End If
  69.  
  70.  
  71. If acctPO = "" Then
  72.     Cells(5, 7).Value = "No Purchase Order provided"
  73. Else
  74.     Cells(5, 7).Value = acctPO
  75. End If
  76.  
  77.  
  78. If acctFax = "" Then
  79.     Cells(8, 7).Value = "No Fax number provided"
  80. Else
  81.     Cells(8, 7).Value = acctFax
  82. End If
  83.  
  84.  
  85. If acctContact = "" Then
  86.     Cells(8, 4).Value = "No Account Contact provided"
  87.     Cells(6, 7).Value = "No Account Contact provided"
  88. Else
  89.     Cells(8, 4).Value = acctContact
  90.     Cells(6, 7).Value = acctContact
  91. End If
  92.  
  93.  
  94. If acctPhone = "" Then
  95.     Cells(7, 7).Value = "No Phone Number provided"
  96. Else
  97.     Cells(7, 7).Value = acctPhone
  98. End If
  99.  
  100.  
  101. If acctEmail = "" Then
  102.     Cells(9, 7).Value = "No Email contact provided"
  103. Else
  104.     Cells(9, 7).Value = acctEmail
  105. End If
  106.  
  107.  
  108.  
  109. Cells(4, 7).Value = Date
  110. Range(Cells(4, 7), Cells(4, 7)).NumberFormat = "mmm dd, yyyy"
  111. Range(Cells(4, 7), Cells(4, 7)).HorizontalAlignment = xlCenter
  112.  
  113.  
  114.  
  115.  
  116.  
  117. Dim thisModel As String
  118. Dim lastModel As String
  119. Dim lastLocation As String
  120. Dim thisTax As Double
  121. Dim onTax As Double
  122. Dim bcTax As Double
  123. Dim mbTax As Double
  124. Dim nfTax As Double
  125. Dim ntTax As Double
  126. Dim nsTax As Double
  127. Dim nuTax As Double
  128. Dim peTax As Double
  129. Dim qcTax As Double
  130. Dim skTax As Double
  131. Dim ykTax As Double
  132. Dim appText As String
  133.  
  134. appText = "APPLICATION: " & Chr(10)
  135. 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)
  136. appText = appText & "RETURNS:" & Chr(10)
  137. 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)
  138. appText = appText & "PAYMENT:" & Chr(10)
  139. 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)
  140. appText = appText & "SIGNATURE:"
  141.  
  142. 'Sales tax Rates
  143. abTax = 0.05
  144. bcTax = 0.12
  145. mbTax = 0.13
  146. nbTax = 0.15
  147. nfTax = 0.15
  148. ntTax = 0.05
  149. nsTax = 0.15
  150. nuTax = 0.05
  151. onTax = 0.13
  152. peTax = 0.13
  153. qcTax = 0.14975
  154. skTax = 0.11
  155. ykTax = 0.05
  156.  
  157. machineIndex = 16
  158. modelQty = Sheets(2).Cells(machineIndex, 27).Value
  159. moduleStart = 13
  160. modelsToCheckStart = 33
  161. standardSheetNumber = 16
  162. mySheets = Worksheets.Count
  163. statndardRowsInModelDesc = 15
  164. modelTypes = Sheets(2).Cells(Rows.Count, "AB").End(xlUp).Row - statndardRowsInModelDesc
  165.  
  166.  
  167. For i = standardSheetNumber To mySheets
  168.        
  169.     'Get
  170.    thisLocation = Sheets(i).Cells(8, 2).Value
  171.     thisProv = Sheets(i).Cells(10, 2).Value
  172.     thisModel = Sheets(i).Cells(16, 2).Value
  173.  
  174.  
  175.     'if address of this machine matches address of the last machine, check if the machine is the same model
  176.    If thisLocation <> lastLocation Or thisModel <> lastModel Then
  177.    
  178.         'Set Quantity
  179.        Cells(moduleStart, 2).Value = 1
  180.    
  181.         'Set Location
  182.        Cells(moduleStart, 5).Value = thisLocation & " - " & thisProv
  183.        
  184.         'Set Province
  185.        Cells(moduleStart, 6).Value = thisProv
  186.  
  187.         'Set Model
  188.        Cells(moduleStart, 3).Value = thisModel
  189.        
  190.         'Set Tax
  191.        Select Case thisProv
  192.             Case "ON"
  193.                 thisTax = onTax
  194.             Case "BC"
  195.                 thisTax = bcTax
  196.             Case "MB"
  197.                 thisTax = mbTax
  198.             Case "NB"
  199.                 thisTax = nbTax
  200.             Case "NF"
  201.                 thisTax = nfTax
  202.             Case "NT"
  203.                 thisTax = ntTax
  204.             Case "NS"
  205.                 thisTax = nsTax
  206.             Case "NU"
  207.                 thisTax = nuTax
  208.             Case "PE"
  209.                 thisTax = peTax
  210.             Case "AB"
  211.                 thisTax = abTax
  212.             Case "QC"
  213.                 thisTax = qcTax
  214.             Case "SK"
  215.                 thisTax = skTax
  216.             Case "YK"
  217.                 thisTax = ykTax
  218.         End Select
  219.            
  220.         'if quantity < 1 then add 1 to machineIndex
  221.        If modelQty <= 1 Then
  222.             machineIndex = machineIndex + 1
  223.         Else
  224.             modelQty = modelQty - 1
  225.         End If
  226.  
  227.         'Increment line pointer
  228.        moduleStart = moduleStart + 1
  229.        
  230.         lastLocation = thisLocation
  231.         lastModel = thisModel
  232.  
  233.     Else
  234.         'increment Qty and move to the next iteration
  235.       Cells(moduleStart - 1, 2).Value = Cells(moduleStart - 1, 2).Value + 1
  236.     End If
  237.    
  238.    
  239.     For j = 0 To modelTypes - 1
  240.         modelToCheck = Sheets(2).Cells(modelsToCheckStart + j, 18).Value
  241.        
  242.         If thisModel = modelToCheck Then
  243.             ' set amount
  244.            Cells(moduleStart - 1, 7).Value = Sheets(2).Cells(modelsToCheckStart + j, 20).Value / Sheets(2).Cells(modelsToCheckStart + j, 16).Value
  245.             Cells(moduleStart - 1, 8).Value = Cells(moduleStart - 1, 7).Value * thisTax
  246.             Exit For
  247.         End If
  248.     Next j
  249.  
  250.     'Multiply amount by quantity
  251.    Cells(moduleStart - 1, 7).Value = Cells(moduleStart - 1, 7).Value * Cells(moduleStart - 1, 2).Value
  252.     Cells(moduleStart - 1, 8).Value = Cells(moduleStart - 1, 8).Value * Cells(moduleStart - 1, 2).Value
  253.    
  254.     'Add in borders for layout
  255.    Range(Cells(moduleStart - 1, 9), Cells(moduleStart - 1, 9)).Borders(xlEdgeRight).LineStyle = xlContinuous
  256.     Range(Cells(moduleStart - 1, 8), Cells(moduleStart - 1, 8)).Borders(xlEdgeRight).LineStyle = xlContinuous
  257.     Range(Cells(moduleStart - 1, 2), Cells(moduleStart - 1, 2)).Borders(xlEdgeLeft).LineStyle = xlContinuous
  258.     Range("G13:H100").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
  259.    
  260.     'Align text
  261.    Range(Cells(moduleStart - 1, 2), Cells(moduleStart - 1, 6)).HorizontalAlignment = xlCenter
  262.    
  263. Next i
  264.  
  265. 'Insert Footer
  266. Range(Cells(moduleStart - 1, 2), Cells(moduleStart - 1, 8)).Borders(xlEdgeBottom).LineStyle = xlContinuous
  267. Range(Cells(moduleStart + 33, 1), Cells(moduleStart + 33, 9)).Borders(xlEdgeBottom).LineStyle = xlContinuous
  268. Range(Cells(moduleStart - 1, 9), Cells(moduleStart + 33, 9)).Borders(xlEdgeRight).LineStyle = xlContinuous
  269. Range(Cells(moduleStart - 5, 1), Cells(moduleStart + 33, 1)).Borders(xlEdgeLeft).LineStyle = xlContinuous
  270.  
  271.  
  272. For i = 4 To 10
  273.     Range(Cells(moduleStart + i, 6), Cells(moduleStart + i, 7)).Merge
  274. Next i
  275.  
  276. Range(Cells(moduleStart + 4, 6), Cells(moduleStart + 10, 7)).HorizontalAlignment = xlRight
  277.  
  278. 'Settlement Details
  279. Cells(moduleStart + 3, 2).Value = "Settlement Details:"
  280. Cells(moduleStart + 3, 2).Font.Bold = True
  281. Range(Cells(moduleStart + 4, 2), Cells(moduleStart + 4, 5)).Merge
  282. Range(Cells(moduleStart + 4, 2), Cells(moduleStart + 4, 5)).Borders(xlEdgeBottom).LineStyle = xlContinuous
  283. Range(Cells(moduleStart + 5, 2), Cells(moduleStart + 5, 5)).Merge
  284. Range(Cells(moduleStart + 5, 2), Cells(moduleStart + 5, 5)).Borders(xlEdgeBottom).LineStyle = xlContinuous
  285. Range(Cells(moduleStart + 6, 2), Cells(moduleStart + 6, 5)).Merge
  286. Range(Cells(moduleStart + 6, 2), Cells(moduleStart + 6, 5)).Borders(xlEdgeBottom).LineStyle = xlContinuous
  287. Range(Cells(moduleStart + 7, 2), Cells(moduleStart + 7, 5)).Merge
  288. Range(Cells(moduleStart + 7, 2), Cells(moduleStart + 7, 5)).Borders(xlEdgeBottom).LineStyle = xlContinuous
  289. Range(Cells(moduleStart + 8, 2), Cells(moduleStart + 8, 5)).Merge
  290. Range(Cells(moduleStart + 8, 2), Cells(moduleStart + 8, 5)).Borders(xlEdgeBottom).LineStyle = xlContinuous
  291. Range(Cells(moduleStart + 9, 2), Cells(moduleStart + 9, 5)).Merge
  292. Range(Cells(moduleStart + 9, 2), Cells(moduleStart + 9, 5)).Borders(xlEdgeBottom).LineStyle = xlContinuous
  293. Range(Cells(moduleStart + 10, 2), Cells(moduleStart + 10, 5)).Merge
  294.  
  295. 'Net Value before Tax
  296. Cells(moduleStart + 4, 6).Value = "Net Value Before Tax:"
  297. Cells(moduleStart + 4, 6).Font.Bold = True
  298. Cells(moduleStart + 4, 8).Font.Italic = True
  299. Cells(moduleStart + 4, 8).Borders(xlEdgeBottom).LineStyle = xlContinuous
  300. Cells(moduleStart + 4, 8).Value = Application.Sum(Range(Cells(12, 7), Cells(moduleStart, 7)))
  301.  
  302. 'Total Taxes
  303. Cells(moduleStart + 6, 6).Value = "Total Taxes:"
  304. Cells(moduleStart + 6, 6).Font.Bold = True
  305. Cells(moduleStart + 6, 8).Font.Italic = True
  306. Cells(moduleStart + 6, 8).Borders(xlEdgeBottom).LineStyle = xlContinuous
  307. Cells(moduleStart + 6, 8).Value = Application.Sum(Range(Cells(12, 8), Cells(moduleStart, 8)))
  308.  
  309. 'TOTAL
  310. Cells(moduleStart + 8, 6).Value = "TOTAL:"
  311. Cells(moduleStart + 8, 6).Font.Bold = True
  312. Cells(moduleStart + 8, 8).Font.Italic = True
  313. Cells(moduleStart + 8, 8).Borders(xlEdgeBottom).LineStyle = xlContinuous
  314. Cells(moduleStart + 8, 8).Value = Application.Sum(Range(Cells(moduleStart + 4, 8), Cells(moduleStart + 6, 8)))
  315.  
  316. 'Special Provisions
  317. Cells(moduleStart + 11, 2).Value = "Special Provisions:"
  318. Cells(moduleStart + 11, 2).VerticalAlignment = xlVAlignTop
  319. Cells(moduleStart + 11, 2).Font.Size = 8
  320. Rows(moduleStart + 11).RowHeight = 27
  321. Rows(moduleStart + 12).RowHeight = 6
  322.  
  323. 'Customer Initial
  324. Cells(moduleStart + 11, 8).Value = "Customer" & Chr(10) & " Initial:"
  325. Cells(moduleStart + 11, 8).VerticalAlignment = xlVAlignTop
  326. Cells(moduleStart + 11, 8).Font.Size = 8
  327. Range(Cells(moduleStart + 11, 2), Cells(moduleStart + 11, 8)).BorderAround ColorIndex:=1
  328. Range(Cells(moduleStart + 11, 2), Cells(moduleStart + 11, 8)).IndentLevel = 0
  329. Range(Cells(moduleStart + 11, 2), Cells(moduleStart + 11, 6)).Merge
  330.  
  331. 'Application Text
  332. Range(Cells(moduleStart + 12, 2), Cells(moduleStart + 25, 8)).Merge
  333. Cells(moduleStart + 12, 2).Value = appText
  334. Rows(moduleStart + 12).RowHeight = 10
  335. Cells(moduleStart + 12, 2).Characters(1, 15).Font.Bold = True
  336. Cells(moduleStart + 12, 2).Characters(15, 748).Font.Bold = False
  337. Cells(moduleStart + 12, 2).Characters(749, 757).Font.Bold = True
  338. Cells(moduleStart + 12, 2).Characters(758, 1334).Font.Bold = False
  339. Cells(moduleStart + 12, 2).Characters(1335, 1345).Font.Bold = True
  340. Cells(moduleStart + 12, 2).Characters(1346, 1759).Font.Bold = False
  341. Cells(moduleStart + 12, 2).Characters(1760, 1780).Font.Bold = True
  342.  
  343. 'Signature
  344. Range(Cells(moduleStart + 26, 2), Cells(moduleStart + 26, 6)).Merge
  345. Range(Cells(moduleStart + 26, 2), Cells(moduleStart + 27, 6)).HorizontalAlignment = xlCenter
  346. Range(Cells(moduleStart + 26, 2), Cells(moduleStart + 26, 6)).BorderAround ColorIndex:=1
  347. Cells(moduleStart + 26, 2).Value = "Signature(s) of Customer(s)"
  348.  
  349. 'Acceptance
  350. Range(Cells(moduleStart + 26, 7), Cells(moduleStart + 26, 8)).Merge
  351. Range(Cells(moduleStart + 26, 7), Cells(moduleStart + 26, 8)).BorderAround ColorIndex:=1
  352. Cells(moduleStart + 26, 7).Value = "Acceptance by Document Direction Limited"
  353. Cells(moduleStart + 26, 7).Font.Size = 7
  354. Rows(moduleStart + 26).RowHeight = 14
  355.  
  356. 'Signature (2)
  357. Range(Cells(moduleStart + 27, 2), Cells(moduleStart + 27, 4)).Merge
  358. Range(Cells(moduleStart + 27, 2), Cells(moduleStart + 27, 4)).BorderAround ColorIndex:=1
  359. Range(Cells(moduleStart + 27, 2), Cells(moduleStart + 27, 8)).Font.Size = 8
  360. Cells(moduleStart + 27, 2).Value = "Signature"
  361.  
  362. 'Print name and Position
  363. Cells(moduleStart + 27, 5).Value = "Print Name and Position"
  364. Cells(moduleStart + 27, 5).BorderAround ColorIndex:=1
  365.  
  366. 'Date Signed
  367. Cells(moduleStart + 27, 6).Value = "Date" & Chr(10) & "Signed"
  368. Cells(moduleStart + 27, 6).VerticalAlignment = xlCenter
  369. Cells(moduleStart + 27, 6).BorderAround ColorIndex:=1
  370.  
  371. 'DDL Signature
  372. Cells(moduleStart + 27, 7).Value = "Signature of Document Direction Limited"
  373. Range(Cells(moduleStart + 27, 7), Cells(moduleStart + 27, 8)).Merge
  374. Range(Cells(moduleStart + 27, 7), Cells(moduleStart + 27, 8)).BorderAround ColorIndex:=1
  375.  
  376. 'bind to customer
  377. Range(Cells(moduleStart + 28, 2), Cells(moduleStart + 32, 4)).Merge
  378. Range(Cells(moduleStart + 28, 2), Cells(moduleStart + 32, 4)).WrapText = True
  379. Range(Cells(moduleStart + 28, 2), Cells(moduleStart + 32, 4)).BorderAround ColorIndex:=1
  380. Cells(moduleStart + 28, 2).Value = "I have the authority to bind the Customer"
  381. Cells(moduleStart + 28, 2).HorizontalAlignment = xlCenter
  382. Cells(moduleStart + 28, 2).VerticalAlignment = xlBottom
  383.  
  384. 'Name Slot
  385. Range(Cells(moduleStart + 28, 5), Cells(moduleStart + 32, 5)).Merge
  386. Range(Cells(moduleStart + 28, 5), Cells(moduleStart + 32, 5)).BorderAround ColorIndex:=1
  387.  
  388. 'Date Signed Slot
  389. Range(Cells(moduleStart + 28, 6), Cells(moduleStart + 32, 6)).Merge
  390. Range(Cells(moduleStart + 28, 6), Cells(moduleStart + 32, 6)).BorderAround ColorIndex:=1
  391.  
  392. 'Signature Slot
  393. Range(Cells(moduleStart + 28, 7), Cells(moduleStart + 32, 8)).Merge
  394. Range(Cells(moduleStart + 28, 7), Cells(moduleStart + 32, 8)).BorderAround ColorIndex:=1
  395.  
  396. End Sub
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!