API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jun 18th, 2019
120
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
VisualBasic 15.04 KB | None | 0 0
raw download clone embed print report
  1.  
  2. 'Align Colums
  3. Columns("A").ColumnWidth = 0.94
  4. Columns("B").ColumnWidth = 0.94
  5. Columns("C").ColumnWidth = 0.94
  6. Columns("D").ColumnWidth = 0.94
  7. Columns("E").ColumnWidth = 0.94
  8. Columns("F").ColumnWidth = 0.94
  9. Columns("G").ColumnWidth = 0.94
  10. Columns("H").ColumnWidth = 0.94
  11. Columns("I").ColumnWidth = 0.94
  12. Columns("J").ColumnWidth = 0.94
  13. Columns("K").ColumnWidth = 0.94
  14. Columns("L").ColumnWidth = 0.94
  15. Columns("M").ColumnWidth = 0.94
  16. Columns("N").ColumnWidth = 0.94
  17. Columns("O").ColumnWidth = 0.94
  18. Columns("P").ColumnWidth = 0.94
  19. Columns("Q").ColumnWidth = 0.94
  20. Columns("R").ColumnWidth = 0.94
  21. Columns("S").ColumnWidth = 0.94
  22. Columns("T").ColumnWidth = 0.94
  23. Columns("U").ColumnWidth = 0.94
  24. Columns("V").ColumnWidth = 0.94
  25. Columns("W").ColumnWidth = 0.94
  26. Columns("X").ColumnWidth = 0.94
  27. Columns("Y").ColumnWidth = 0.94
  28. Columns("Z").ColumnWidth = 0.94
  29. Columns("AA").ColumnWidth = 0.94
  30. Columns("AB").ColumnWidth = 0.94
  31. Columns("AC").ColumnWidth = 0.94
  32. Columns("AD").ColumnWidth = 4.33
  33. Columns("AE").ColumnWidth = 0.94
  34. Columns("AF").ColumnWidth = 0.94
  35. Columns("AG").ColumnWidth = 0.94
  36. Columns("AH").ColumnWidth = 0.94
  37. Columns("AI").ColumnWidth = 0.94
  38. Columns("AJ").ColumnWidth = 0.94
  39. Columns("AK").ColumnWidth = 0.94
  40. Columns("AL").ColumnWidth = 0.94
  41. Columns("AM").ColumnWidth = 0.94
  42. Columns("AN").ColumnWidth = 0.94
  43. Columns("AO").ColumnWidth = 0.94
  44. Columns("AP").ColumnWidth = 0.94
  45. Columns("AQ").ColumnWidth = 0.94
  46. Columns("AR").ColumnWidth = 0.94
  47. Columns("AS").ColumnWidth = 0.94
  48. Columns("AT").ColumnWidth = 0.94
  49. Columns("AU").ColumnWidth = 0.94
  50. Columns("AV").ColumnWidth = 0.94
  51. Columns("AW").ColumnWidth = 0.94
  52. Columns("AX").ColumnWidth = 0.94
  53. Columns("AY").ColumnWidth = 0.94
  54. Columns("AZ").ColumnWidth = 0.94
  55. Columns("BA").ColumnWidth = 0.94
  56. Columns("BB").ColumnWidth = 0.94
  57. Columns("BC").ColumnWidth = 0.94
  58. Columns("BD").ColumnWidth = 0.94
  59. Columns("BE").ColumnWidth = 0.94
  60. Columns("BF").ColumnWidth = 0.94
  61. Columns("BG").ColumnWidth = 0.94
  62.  
  63. 'Font Settings
  64. Range(Cells(14, 1), Cells(moduleStart + 40, 58)).Font.Size = 11
  65. Range(Cells(14, 1), Cells(moduleStart + 40, 58)).Font.Name = "Times New Roman"
  66.  
  67. Cells(moduleStart + 4, 18).Value = "Delivery Charge"
  68. Range(Cells(moduleStart + 4, 18), Cells(moduleStart + 14, 18)).Font.Size = 10
  69. Range(Cells(moduleStart + 4, 18), Cells(moduleStart + 14, 18)).Font.Name = "Arial"
  70.  
  71. moduleStart = 16
  72. standardSheets = 16
  73. mySheets = Worksheets.Count
  74. leaseSheet = "Lease Price Model 2.0"
  75.  
  76.  
  77. For i = standardSheets To mySheets
  78.    
  79.     'Align Rows
  80.    Rows(moduleStart).RowHeight = 12
  81.     Rows(moduleStart + 1).RowHeight = 6.6
  82.    
  83.     'Thick Side Border
  84.    With Worksheets(leaseSheet).Range(Cells(moduleStart, 58), Cells(moduleStart, 58)).Borders(xlEdgeRight)
  85.        .LineStyle = xlContinuous
  86.        .Weight = xlMedium
  87.     End With
  88.      
  89.     With Worksheets(leaseSheet).Range(Cells(moduleStart - 1, 58), Cells(moduleStart - 1, 58)).Borders(xlEdgeRight)
  90.        .LineStyle = xlContinuous
  91.        .Weight = xlMedium
  92.     End With
  93.    
  94.    
  95.     Range(Cells(moduleStart, 2), Cells(moduleStart, 13)).Merge
  96.     Range(Cells(moduleStart, 15), Cells(moduleStart, 24)).Merge
  97.     Range(Cells(moduleStart, 30), Cells(moduleStart, 39)).Merge
  98.     Range(Cells(moduleStart, 45), Cells(moduleStart, 54)).Merge
  99.  
  100.     moduleStart = moduleStart + 2
  101.  
  102. Next i
  103.    
  104.    
  105. 'Underline
  106. With Worksheets(leaseSheet).Range(Cells(moduleStart - 2, 15), Cells(moduleStart - 2, 24)).Borders(xlEdgeBottom)
  107.     .LineStyle = xlContinuous
  108.     .Weight = xlThin
  109. End With
  110.  
  111. With Worksheets(leaseSheet).Range(Cells(moduleStart - 2, 30), Cells(moduleStart - 2, 39)).Borders(xlEdgeBottom)
  112.     .LineStyle = xlContinuous
  113.     .Weight = xlThin
  114. End With
  115.  
  116. With Worksheets(leaseSheet).Range(Cells(moduleStart - 2, 45), Cells(moduleStart - 2, 54)).Borders(xlEdgeBottom)
  117.     .LineStyle = xlContinuous
  118.     .Weight = xlThin
  119. End With
  120.  
  121.  
  122. 'Align Footer Row above totals
  123. Rows(moduleStart - 1).RowHeight = 6.6
  124.  
  125. 'Merging totals
  126. Range(Cells(moduleStart, 15), Cells(moduleStart, 24)).Merge
  127. Range(Cells(moduleStart, 30), Cells(moduleStart, 39)).Merge
  128. Range(Cells(moduleStart, 45), Cells(moduleStart, 54)).Merge
  129.  
  130. 'Borders for totals
  131. Range(Cells(moduleStart, 15), Cells(moduleStart, 24)).BorderAround ColorIndex:=1, Weight:=xlThin
  132. Range(Cells(moduleStart, 30), Cells(moduleStart, 39)).BorderAround ColorIndex:=1, Weight:=xlThin
  133. Range(Cells(moduleStart, 45), Cells(moduleStart, 54)).BorderAround ColorIndex:=1, Weight:=xlThin
  134.  
  135. 'Add Footer
  136. Cells(moduleStart, 13).Value = "Totals"
  137. Cells(moduleStart, 13).Font.Bold = True
  138. Range(Cells(moduleStart, 13), Cells(moduleStart, 13)).HorizontalAlignment = xlRight
  139.  
  140. 'Equipment Sub Total
  141. Cells(moduleStart + 2, 13).Value = "Equipment Sub-Total"
  142. Cells(moduleStart + 2, 13).Font.Bold = True
  143. Range(Cells(moduleStart + 2, 13), Cells(moduleStart + 2, 13)).HorizontalAlignment = xlRight
  144. Range(Cells(moduleStart + 2, 26), Cells(moduleStart + 2, 35)).Merge
  145. Range(Cells(moduleStart + 2, 26), Cells(moduleStart + 2, 35)).BorderAround ColorIndex:=1, Weight:=xlThin
  146. Range(Cells(moduleStart + 2, 46), Cells(moduleStart + 2, 55)).Merge
  147. Range(Cells(moduleStart + 2, 46), Cells(moduleStart + 2, 55)).BorderAround ColorIndex:=1, Weight:=xlThin
  148.  
  149. 'Delivery Charge
  150. Cells(moduleStart + 4, 18).Value = "Delivery Charge"
  151. Range(Cells(moduleStart + 4, 27), Cells(moduleStart + 4, 35)).Merge
  152. Range(Cells(moduleStart + 4, 18), Cells(moduleStart + 4, 18)).HorizontalAlignment = xlRight
  153. Range(Cells(moduleStart + 4, 27), Cells(moduleStart + 4, 35)).BorderAround ColorIndex:=1, Weight:=xlThin
  154.  
  155. 'Removal Charge
  156. Cells(moduleStart + 5, 18).Value = "Removal Charge"
  157. Range(Cells(moduleStart + 5, 18), Cells(moduleStart + 5, 18)).HorizontalAlignment = xlRight
  158. Range(Cells(moduleStart + 4, 27), Cells(moduleStart + 4, 35)).Merge
  159.  
  160. 'Service Allocation
  161. Cells(moduleStart + 6, 18).Value = "Service Allocation"
  162. Range(Cells(moduleStart + 6, 18), Cells(moduleStart + 6, 18)).HorizontalAlignment = xlRight
  163.  
  164. 'Marketing Promotion
  165. Cells(moduleStart + 7, 18).Value = "Marketing Promotion"
  166. Range(Cells(moduleStart + 7, 18), Cells(moduleStart + 7, 18)).HorizontalAlignment = xlRight
  167.  
  168. 'Trade-In Amount (Discount)
  169. Cells(moduleStart + 8, 18).Value = "Trade-In Amount (Discount)"
  170. Range(Cells(moduleStart + 8, 18), Cells(moduleStart + 8, 18)).HorizontalAlignment = xlRight
  171.  
  172. 'Provincial Environmental Levy
  173. Cells(moduleStart + 9, 18).Value = "Provincial Environmental Levy"
  174. Range(Cells(moduleStart + 9, 18), Cells(moduleStart + 9, 18)).HorizontalAlignment = xlRight
  175.  
  176. 'Net Equipment Value
  177. Range(Cells(moduleStart + 10, 27), Cells(moduleStart + 10, 35)).Merge
  178. Cells(moduleStart + 10, 18).Value = "Net Equipment Value"
  179. Cells(moduleStart + 10, 18).Font.Bold = True
  180. Range(Cells(moduleStart + 10, 27), Cells(moduleStart + 10, 35)).BorderAround ColorIndex:=1, Weight:=xlThin
  181. Range(Cells(moduleStart + 10, 18), Cells(moduleStart + 10, 18)).HorizontalAlignment = xlRight
  182.  
  183. 'Settlement Amount
  184. Cells(moduleStart + 11, 18).Value = "Settlement Amount"
  185. Range(Cells(moduleStart + 11, 18), Cells(moduleStart + 11, 18)).HorizontalAlignment = xlRight
  186.  
  187. 'Discretionary Items
  188. Cells(moduleStart + 12, 18).Value = "Discretionary Items"
  189. Range(Cells(moduleStart + 12, 18), Cells(moduleStart + 12, 18)).HorizontalAlignment = xlRight
  190.  
  191. 'Invoice Price
  192. Range(Cells(moduleStart + 14, 27), Cells(moduleStart + 14, 35)).Merge
  193. Cells(moduleStart + 14, 18).Value = "Invoice Price"
  194. Cells(moduleStart + 14, 18).Font.Bold = True
  195. Range(Cells(moduleStart + 14, 18), Cells(moduleStart + 14, 18)).HorizontalAlignment = xlRight
  196. Range(Cells(moduleStart + 14, 27), Cells(moduleStart + 14, 35)).BorderAround ColorIndex:=1, Weight:=xlThin
  197.  
  198. 'Lease Pricing Model (Ricoh Non-Note)
  199. Range(Cells(moduleStart + 16, 1), Cells(moduleStart + 16, 58)).Merge
  200. Range(Cells(moduleStart + 16, 1), Cells(moduleStart + 16, 58)).Interior.ColorIndex = 6
  201. Range(Cells(moduleStart + 16, 1), Cells(moduleStart + 16, 58)).BorderAround ColorIndex:=1, Weight:=xlThin
  202.  
  203. 'Please enter amounts in green boxes
  204. Cells(moduleStart + 17, 2).Value = "Please enter amounts in green boxes"
  205.  
  206. 'Equipment Value
  207. Cells(moduleStart + 18, 22).Value = "Equipment Value"
  208.  
  209. 'New Equipment
  210. Cells(moduleStart + 19, 2).Value = "New Equipment"
  211. Range(Cells(moduleStart + 19, 20), Cells(moduleStart + 19, 32)).Merge
  212. Range(Cells(moduleStart + 19, 20), Cells(moduleStart + 19, 32)).BorderAround ColorIndex:=1, Weight:=xlThin
  213.  
  214. 'Used/Refinance Equipment
  215. Cells(moduleStart + 21, 2).Value = "Used/Refinance Equipment"
  216. Range(Cells(moduleStart + 21, 20), Cells(moduleStart + 21, 32)).Merge
  217. Range(Cells(moduleStart + 21, 20), Cells(moduleStart + 21, 32)).BorderAround ColorIndex:=1, Weight:=xlThin
  218.  
  219. 'Settlement
  220. Cells(moduleStart + 23, 2).Value = "Settlement"
  221. Range(Cells(moduleStart + 23, 20), Cells(moduleStart + 23, 32)).Merge
  222. Range(Cells(moduleStart + 23, 20), Cells(moduleStart + 23, 32)).BorderAround ColorIndex:=1, Weight:=xlThin
  223.  
  224. 'Soft Costs
  225. Cells(moduleStart + 25, 2).Value = "Soft Costs"
  226. Range(Cells(moduleStart + 25, 20), Cells(moduleStart + 25, 32)).Merge
  227. Range(Cells(moduleStart + 25, 20), Cells(moduleStart + 25, 32)).BorderAround ColorIndex:=1, Weight:=xlThin
  228.  
  229.  
  230. 'Air
  231. Cells(moduleStart + 18, 34).Value = "% Air"
  232.  
  233. 'New Equipment Rate
  234. Cells(moduleStart + 18, 40).Value = "Rate"
  235. Range(Cells(moduleStart + 19, 40), Cells(moduleStart + 19, 44)).Merge
  236. Range(Cells(moduleStart + 19, 40), Cells(moduleStart + 19, 44)).BorderAround ColorIndex:=1, Weight:=xlThin
  237.  
  238. 'Used/Refinance Rate
  239. Range(Cells(moduleStart + 21, 40), Cells(moduleStart + 21, 44)).Merge
  240. Range(Cells(moduleStart + 21, 40), Cells(moduleStart + 21, 44)).BorderAround ColorIndex:=1, Weight:=xlThin
  241.  
  242. 'Settlement Rate
  243. Range(Cells(moduleStart + 23, 40), Cells(moduleStart + 23, 44)).Merge
  244. Range(Cells(moduleStart + 23, 40), Cells(moduleStart + 23, 44)).BorderAround ColorIndex:=1, Weight:=xlThin
  245.  
  246. ' % of Air
  247. Range(Cells(moduleStart + 23, 34), Cells(moduleStart + 23, 36)).Merge
  248. Range(Cells(moduleStart + 23, 34), Cells(moduleStart + 23, 36)).BorderAround ColorIndex:=1, Weight:=xlThin
  249.  
  250. 'Soft Costs Rate
  251. Range(Cells(moduleStart + 25, 40), Cells(moduleStart + 25, 44)).Merge
  252. Range(Cells(moduleStart + 25, 40), Cells(moduleStart + 25, 44)).BorderAround ColorIndex:=1, Weight:=xlThin
  253.  
  254. 'Lease Payment
  255. Cells(moduleStart + 18, 49).Value = "Lease Payment"
  256.  
  257. 'New Eqp Lease Payment
  258. Range(Cells(moduleStart + 19, 49), Cells(moduleStart + 19, 56)).Merge
  259. Range(Cells(moduleStart + 19, 49), Cells(moduleStart + 19, 56)).BorderAround ColorIndex:=1, Weight:=xlThin
  260.  
  261. 'Used / Refinanced EQP Lease payment
  262. Range(Cells(moduleStart + 21, 49), Cells(moduleStart + 21, 56)).Merge
  263. Range(Cells(moduleStart + 21, 49), Cells(moduleStart + 21, 56)).BorderAround ColorIndex:=1, Weight:=xlThin
  264.  
  265. 'Settlement Lease Payment
  266. Range(Cells(moduleStart + 23, 49), Cells(moduleStart + 23, 56)).Merge
  267. Range(Cells(moduleStart + 23, 49), Cells(moduleStart + 23, 56)).BorderAround ColorIndex:=1, Weight:=xlThin
  268.  
  269. 'Soft Costs Lease Payment
  270. Range(Cells(moduleStart + 25, 49), Cells(moduleStart + 25, 56)).Merge
  271. Range(Cells(moduleStart + 25, 49), Cells(moduleStart + 25, 56)).BorderAround ColorIndex:=1, Weight:=xlThin
  272.  
  273.  
  274. 'Rate Total
  275. Range(Cells(moduleStart + 31, 40), Cells(moduleStart + 31, 44)).Merge
  276. Range(Cells(moduleStart + 31, 40), Cells(moduleStart + 31, 44)).BorderAround ColorIndex:=1, Weight:=xlThin
  277.  
  278.  
  279. 'Lease Paymment
  280. Range(Cells(moduleStart + 31, 49), Cells(moduleStart + 31, 56)).Merge
  281. Range(Cells(moduleStart + 31, 49), Cells(moduleStart + 31, 56)).BorderAround ColorIndex:=1, Weight:=xlThin
  282.  
  283. 'Total
  284. Cells(moduleStart + 27, 2).Value = "Total"
  285. Range(Cells(moduleStart + 27, 20), Cells(moduleStart + 27, 32)).Merge
  286. Range(Cells(moduleStart + 27, 20), Cells(moduleStart + 27, 32)).BorderAround ColorIndex:=1, Weight:=xlThin
  287.  
  288. 'Total Amount Financed
  289. Cells(moduleStart + 31, 2).Value = "Total Amount Financed"
  290. Range(Cells(moduleStart + 31, 20), Cells(moduleStart + 31, 32)).Merge
  291. Range(Cells(moduleStart + 31, 20), Cells(moduleStart + 31, 32)).BorderAround ColorIndex:=1, Weight:=xlThin
  292.  
  293. 'Misc
  294. Cells(moduleStart + 35, 58).Value = "*Please indicate any other pertinent information necessary"
  295. Cells(moduleStart + 35, 58).HorizontalAlignment = xlRight
  296. Range(Cells(moduleStart + 35, 1), Cells(moduleStart + 35, 58)).Interior.ColorIndex = 15
  297. Cells(moduleStart + 35, 1).Value = "E) Miscellaneous"
  298. Cells(moduleStart + 35, 1).Font.Bold = True
  299. Cells(moduleStart + 35, 2).HorizontalAlignment = xlLeft
  300. Range(Cells(moduleStart + 35, 1), Cells(moduleStart + 35, 58)).BorderAround ColorIndex:=1, Weight:=xlThin
  301. Rows(moduleStart + 36).RowHeight = 6.6
  302.  
  303. 'Misc Section
  304. Range(Cells(moduleStart + 37, 1), Cells(moduleStart + 39, 58)).Merge
  305. Range(Cells(moduleStart + 37, 1), Cells(moduleStart + 39, 58)).BorderAround ColorIndex:=1, Weight:=xlThin
  306. Rows(moduleStart + 40).RowHeight = 6.6
  307.  
  308.  
  309. 'Footer thick side border
  310.    With Worksheets(leaseSheet).Range(Cells(moduleStart - 1, 58), Cells(moduleStart + 40, 58)).Borders(xlEdgeRight)
  311.        .LineStyle = xlContinuous
  312.        .Weight = xlMedium
  313.     End With
  314.  
  315.     With Worksheets(leaseSheet).Range(Cells(moduleStart + 40, 1), Cells(moduleStart + 40, 58)).Borders(xlEdgeBottom)
  316.        .LineStyle = xlContinuous
  317.        .Weight = xlMedium
  318.     End With
  319.  
  320.  
  321. ' //////////////////////////////
  322. ' //Fill Movement Forms Script//
  323. ' //////////////////////////////
  324.  
  325.  
  326. currentWs = 16
  327. currentItem = 16
  328. lpmItem = 16
  329. standardSheetNumber = 16
  330. mySheets = Worksheets.Count
  331. firstProduct = 0
  332. lastProduct = 14
  333. StandardRowsPCList = 15
  334. StandardRowsInSheet = 15
  335. equipmentSheet = 2
  336. productListEnd = Sheets(2).Range("E16").End(xlDown).Row - StandardRowsPCList
  337. itemsInSheet = 0
  338. filledItems = 0
  339. lpmSheet = 7
  340.  
  341. For i = standardSheetNumber To mySheets
  342.    
  343.     Sheets(currentWs).Activate
  344.     itemsInSheet = Sheets(currentWs).Range("A16").End(xlDown).Row - StandardRowsInSheet
  345.    
  346.     For j = 0 To productListEnd - 1
  347.    
  348.         ProductCode = Sheets(equipmentSheet).Cells(currentItem + j, 5).Value
  349.         productQty = Sheets(equipmentSheet).Cells(currentItem + j, 1).Value
  350.         productCost = Sheets(equipmentSheet).Cells(currentItem + j, 10).Value
  351.        
  352.         For k = firstProduct To itemsInSheet
  353.             If Sheets(currentWs).Cells(currentItem + k, 4).Value <> 1 Then
  354.                 pcToCheck = Sheets(currentWs).Cells(currentItem + k, 1).Value
  355.  
  356.                 If pcToCheck = ProductCode Then
  357.                     Sheets(currentWs).Cells(currentItem + k, 6).Value = productCost
  358.                     Sheets(currentWs).Cells(currentItem + k, 4).Value = Sheets(currentWs).Cells(currentItem + k, 4).Value + 1
  359.                     Exit For
  360.                 End If
  361.            
  362.             End If
  363.         Next k
  364.        
  365.         filledItems = WorksheetFunction.Sum(Sheets(currentWs).Range(Cells(16, 4), Cells(16 + itemsInSheet, 4)))
  366.        
  367.         If filledItems = itemsInSheet Then
  368.             totalPrice = WorksheetFunction.Sum(Sheets(currentWs).Range(Cells(16, 6), Cells(16 + itemsInSheet, 6)))
  369.             Sheets(lpmSheet).Cells(lpmItem, 15).Value = totalPrice
  370.             lpmItem = lpmItem + 2
  371.             Exit For
  372.         End If
  373.  
  374.     Next j
  375.    
  376.     If currentWs < mySheets Then
  377.         currentWs = currentWs + 1
  378.     Else
  379.         Sheets(lpmSheet).Activate
  380.     End If
  381.  
  382.  
  383. Next i
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!