API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jun 24th, 2019
83
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
VisualBasic 20.27 KB | None | 0 0
raw download clone embed print report
  1. Sub Button3_Click()
  2. ActiveSheet.Unprotect Password:="sherpadoc1"
  3. 'Align Colums
  4. Columns("A").ColumnWidth = 0.94
  5. Columns("B").ColumnWidth = 0.94
  6. Columns("C").ColumnWidth = 0.94
  7. Columns("D").ColumnWidth = 0.94
  8. Columns("E").ColumnWidth = 0.94
  9. Columns("F").ColumnWidth = 0.94
  10. Columns("G").ColumnWidth = 0.94
  11. Columns("H").ColumnWidth = 0.94
  12. Columns("I").ColumnWidth = 0.94
  13. Columns("J").ColumnWidth = 0.94
  14. Columns("K").ColumnWidth = 0.94
  15. Columns("L").ColumnWidth = 0.94
  16. Columns("M").ColumnWidth = 0.94
  17. Columns("N").ColumnWidth = 0.94
  18. Columns("O").ColumnWidth = 0.94
  19. Columns("P").ColumnWidth = 0.94
  20. Columns("Q").ColumnWidth = 0.94
  21. Columns("R").ColumnWidth = 0.94
  22. Columns("S").ColumnWidth = 0.94
  23. Columns("T").ColumnWidth = 0.94
  24. Columns("U").ColumnWidth = 0.94
  25. Columns("V").ColumnWidth = 0.94
  26. Columns("W").ColumnWidth = 0.94
  27. Columns("X").ColumnWidth = 0.94
  28. Columns("Y").ColumnWidth = 0.94
  29. Columns("Z").ColumnWidth = 0.94
  30. Columns("AA").ColumnWidth = 0.94
  31. Columns("AB").ColumnWidth = 0.94
  32. Columns("AC").ColumnWidth = 0.94
  33. Columns("AD").ColumnWidth = 4.33
  34. Columns("AE").ColumnWidth = 0.94
  35. Columns("AF").ColumnWidth = 0.94
  36. Columns("AG").ColumnWidth = 0.94
  37. Columns("AH").ColumnWidth = 0.94
  38. Columns("AI").ColumnWidth = 0.94
  39. Columns("AJ").ColumnWidth = 0.94
  40. Columns("AK").ColumnWidth = 0.94
  41. Columns("AL").ColumnWidth = 0.94
  42. Columns("AM").ColumnWidth = 0.94
  43. Columns("AN").ColumnWidth = 0.94
  44. Columns("AO").ColumnWidth = 0.94
  45. Columns("AP").ColumnWidth = 0.94
  46. Columns("AQ").ColumnWidth = 0.94
  47. Columns("AR").ColumnWidth = 0.94
  48. Columns("AS").ColumnWidth = 0.94
  49. Columns("AT").ColumnWidth = 0.94
  50. Columns("AU").ColumnWidth = 0.94
  51. Columns("AV").ColumnWidth = 0.94
  52. Columns("AW").ColumnWidth = 0.94
  53. Columns("AX").ColumnWidth = 0.94
  54. Columns("AY").ColumnWidth = 0.94
  55. Columns("AZ").ColumnWidth = 0.94
  56. Columns("BA").ColumnWidth = 0.94
  57. Columns("BB").ColumnWidth = 0.94
  58. Columns("BC").ColumnWidth = 0.94
  59. Columns("BD").ColumnWidth = 0.94
  60. Columns("BE").ColumnWidth = 0.94
  61. Columns("BF").ColumnWidth = 0.94
  62. Columns("BG").ColumnWidth = 0.94
  63.  
  64. 'Font Settings
  65. Range(Cells(14, 1), Cells(moduleStart + 40, 58)).Font.Size = 11
  66. Range(Cells(14, 1), Cells(moduleStart + 40, 58)).Font.Name = "Times New Roman"
  67. 'Air % font
  68. Range(Cells(moduleStart + 23, 34), Cells(moduleStart + 23, 34)).Font.Size = 8
  69.  
  70. Cells(moduleStart + 4, 18).Value = "Delivery Charge"
  71. Range(Cells(moduleStart + 4, 18), Cells(moduleStart + 14, 18)).Font.Size = 10
  72. Range(Cells(moduleStart + 4, 18), Cells(moduleStart + 14, 18)).Font.Name = "Arial"
  73.  
  74. moduleStart = 16
  75. standardSheets = 16
  76. mySheets = Worksheets.Count
  77. leaseSheet = "Lease Price Model 2.0"
  78. leaseRate = Sheets(3).Cells(13, 4).Value
  79.  
  80. For i = standardSheets To mySheets
  81.  
  82.     'Align Rows
  83.    Rows(moduleStart).RowHeight = 12
  84.     Rows(moduleStart + 1).RowHeight = 6.6
  85.    
  86.     'Thick Side Border
  87.    With Worksheets(leaseSheet).Range(Cells(moduleStart, 58), Cells(moduleStart, 58)).Borders(xlEdgeRight)
  88.        .LineStyle = xlContinuous
  89.        .Weight = xlMedium
  90.     End With
  91.      
  92.     With Worksheets(leaseSheet).Range(Cells(moduleStart - 1, 58), Cells(moduleStart - 1, 58)).Borders(xlEdgeRight)
  93.        .LineStyle = xlContinuous
  94.        .Weight = xlMedium
  95.     End With
  96.    
  97.     Range(Cells(moduleStart, 2), Cells(moduleStart, 13)).Merge
  98.     Range(Cells(moduleStart, 15), Cells(moduleStart, 24)).Merge
  99.     Range(Cells(moduleStart, 30), Cells(moduleStart, 39)).Merge
  100.     Range(Cells(moduleStart, 45), Cells(moduleStart, 54)).Merge
  101.    
  102.     'Mapp Price
  103.    Cells(moduleStart, 30).Value = mappPrice
  104.    
  105.    
  106.     'Get mainframe name
  107.    Cells(moduleStart, 2).Value = Sheets(i).Cells(16, 2).Value
  108.    
  109.    
  110.     moduleStart = moduleStart + 2
  111.    
  112. Next i
  113.    
  114.    
  115. 'Underline
  116. With Worksheets(leaseSheet).Range(Cells(moduleStart - 2, 15), Cells(moduleStart - 2, 24)).Borders(xlEdgeBottom)
  117.     .LineStyle = xlContinuous
  118.     .Weight = xlThin
  119. End With
  120.  
  121. With Worksheets(leaseSheet).Range(Cells(moduleStart - 2, 30), Cells(moduleStart - 2, 39)).Borders(xlEdgeBottom)
  122.     .LineStyle = xlContinuous
  123.     .Weight = xlThin
  124. End With
  125.  
  126. With Worksheets(leaseSheet).Range(Cells(moduleStart - 2, 45), Cells(moduleStart - 2, 54)).Borders(xlEdgeBottom)
  127.     .LineStyle = xlContinuous
  128.     .Weight = xlThin
  129. End With
  130.  
  131.  
  132. 'Align Footer Row above totals
  133. Rows(moduleStart - 1).RowHeight = 6.6
  134.  
  135. 'Merging totals
  136. Range(Cells(moduleStart, 15), Cells(moduleStart, 24)).Merge
  137. Range(Cells(moduleStart, 30), Cells(moduleStart, 39)).Merge
  138. Range(Cells(moduleStart, 45), Cells(moduleStart, 54)).Merge
  139.  
  140. 'Borders for totals
  141. Range(Cells(moduleStart, 15), Cells(moduleStart, 24)).BorderAround ColorIndex:=1, Weight:=xlThin
  142. Range(Cells(moduleStart, 30), Cells(moduleStart, 39)).BorderAround ColorIndex:=1, Weight:=xlThin
  143. Range(Cells(moduleStart, 45), Cells(moduleStart, 54)).BorderAround ColorIndex:=1, Weight:=xlThin
  144.  
  145. 'Add Footer
  146. Cells(moduleStart, 13).Value = "Totals"
  147. Cells(moduleStart, 13).Font.Bold = True
  148. Range(Cells(moduleStart, 13), Cells(moduleStart, 13)).HorizontalAlignment = xlRight
  149.  
  150. 'Equipment Sub Total
  151. Cells(moduleStart + 2, 13).Value = "Equipment Sub-Total"
  152. Cells(moduleStart + 2, 13).Font.Bold = True
  153. Range(Cells(moduleStart + 2, 13), Cells(moduleStart + 2, 13)).HorizontalAlignment = xlRight
  154. Range(Cells(moduleStart + 2, 26), Cells(moduleStart + 2, 35)).Merge
  155. Range(Cells(moduleStart + 2, 26), Cells(moduleStart + 2, 35)).BorderAround ColorIndex:=1, Weight:=xlThin
  156. Range(Cells(moduleStart + 2, 46), Cells(moduleStart + 2, 55)).Merge
  157. Range(Cells(moduleStart + 2, 46), Cells(moduleStart + 2, 55)).BorderAround ColorIndex:=1, Weight:=xlThin
  158.  
  159. 'Delivery Charge
  160. Cells(moduleStart + 4, 18).Value = "Delivery Charge"
  161. Range(Cells(moduleStart + 4, 27), Cells(moduleStart + 4, 35)).Merge
  162. Cells(moduleStart + 4, 27).Value = 0
  163. Range(Cells(moduleStart + 4, 18), Cells(moduleStart + 4, 18)).HorizontalAlignment = xlRight
  164.  
  165. 'Removal Charge
  166. Cells(moduleStart + 5, 18).Value = "Removal Charge"
  167. Range(Cells(moduleStart + 5, 18), Cells(moduleStart + 5, 18)).HorizontalAlignment = xlRight
  168. Range(Cells(moduleStart + 5, 27), Cells(moduleStart + 5, 35)).Merge
  169. Cells(moduleStart + 5, 27).Value = 0
  170. Range(Cells(moduleStart + 5, 27), Cells(moduleStart + 5, 35)).Interior.ColorIndex = 6
  171.  
  172. 'Service Allocation
  173. Cells(moduleStart + 6, 18).Value = "Service Allocation"
  174. Range(Cells(moduleStart + 6, 18), Cells(moduleStart + 6, 18)).HorizontalAlignment = xlRight
  175. Range(Cells(moduleStart + 6, 27), Cells(moduleStart + 6, 35)).Merge
  176. Cells(moduleStart + 6, 27).Value = 0
  177. Range(Cells(moduleStart + 6, 27), Cells(moduleStart + 6, 35)).Interior.ColorIndex = 6
  178.  
  179. 'Marketing Promotion
  180. Cells(moduleStart + 7, 18).Value = "Marketing Promotion"
  181. Range(Cells(moduleStart + 7, 18), Cells(moduleStart + 7, 18)).HorizontalAlignment = xlRight
  182. Range(Cells(moduleStart + 7, 27), Cells(moduleStart + 7, 35)).Merge
  183. Cells(moduleStart + 7, 27).Value = 0
  184. Range(Cells(moduleStart + 7, 27), Cells(moduleStart + 7, 35)).Interior.ColorIndex = 6
  185.  
  186. 'Trade-In Amount (Discount)
  187. Cells(moduleStart + 8, 18).Value = "Trade-In Amount (Discount)"
  188. Range(Cells(moduleStart + 8, 18), Cells(moduleStart + 8, 18)).HorizontalAlignment = xlRight
  189. Range(Cells(moduleStart + 8, 27), Cells(moduleStart + 8, 35)).Merge
  190. Cells(moduleStart + 8, 27).Value = 0
  191. Range(Cells(moduleStart + 8, 27), Cells(moduleStart + 8, 35)).Interior.ColorIndex = 6
  192.  
  193. 'Provincial Environmental Levy
  194. Cells(moduleStart + 9, 18).Value = "Provincial Environmental Levy"
  195. Range(Cells(moduleStart + 9, 18), Cells(moduleStart + 9, 18)).HorizontalAlignment = xlRight
  196. Range(Cells(moduleStart + 9, 27), Cells(moduleStart + 9, 35)).Merge
  197. Cells(moduleStart + 9, 27).Value = 0
  198.  
  199. 'Net Equipment Value
  200. Range(Cells(moduleStart + 10, 27), Cells(moduleStart + 10, 35)).Merge
  201. Cells(moduleStart + 10, 18).Value = "Net Equipment Value"
  202. Cells(moduleStart + 10, 18).Font.Bold = True
  203. Range(Cells(moduleStart + 10, 27), Cells(moduleStart + 10, 35)).BorderAround ColorIndex:=1, Weight:=xlThin
  204. Range(Cells(moduleStart + 10, 18), Cells(moduleStart + 10, 18)).HorizontalAlignment = xlRight
  205.  
  206. 'Settlement Amount
  207. Cells(moduleStart + 11, 18).Value = "Settlement Amount"
  208. Range(Cells(moduleStart + 11, 18), Cells(moduleStart + 11, 18)).HorizontalAlignment = xlRight
  209. Range(Cells(moduleStart + 11, 27), Cells(moduleStart + 11, 35)).Merge
  210.  
  211. 'Discretionary Items
  212. Cells(moduleStart + 12, 18).Value = "Discretionary Items"
  213. Range(Cells(moduleStart + 12, 18), Cells(moduleStart + 12, 18)).HorizontalAlignment = xlRight
  214. Range(Cells(moduleStart + 12, 27), Cells(moduleStart + 12, 35)).Merge
  215. Cells(moduleStart + 12, 27).Value = 0
  216. Range(Cells(moduleStart + 12, 27), Cells(moduleStart + 12, 35)).Interior.ColorIndex = 6
  217.  
  218. 'Invoice Price
  219. Range(Cells(moduleStart + 14, 27), Cells(moduleStart + 14, 35)).Merge
  220. Cells(moduleStart + 14, 18).Value = "Invoice Price"
  221. Cells(moduleStart + 14, 18).Font.Bold = True
  222. Range(Cells(moduleStart + 14, 18), Cells(moduleStart + 14, 18)).HorizontalAlignment = xlRight
  223. Range(Cells(moduleStart + 14, 27), Cells(moduleStart + 14, 35)).BorderAround ColorIndex:=1, Weight:=xlThin
  224.  
  225. 'Lease Pricing Model (Ricoh Non-Note)
  226. Range(Cells(moduleStart + 16, 1), Cells(moduleStart + 16, 58)).Merge
  227. Cells(moduleStart + 16, 1).Value = "B) Lease Pricing Model (Ricoh Non-Note)"
  228. Cells(moduleStart + 16, 1).Font.Bold = True
  229. Range(Cells(moduleStart + 16, 1), Cells(moduleStart + 16, 58)).Interior.ColorIndex = 6
  230. Range(Cells(moduleStart + 16, 1), Cells(moduleStart + 16, 58)).BorderAround ColorIndex:=1, Weight:=xlThin
  231.  
  232. 'Please enter amounts in green boxes
  233. Cells(moduleStart + 17, 2).Value = "Please enter amounts in green boxes"
  234.  
  235. 'Equipment Value
  236. Cells(moduleStart + 18, 22).Value = "Equipment Value"
  237.  
  238. 'New Equipment
  239. Cells(moduleStart + 19, 2).Value = "New Equipment"
  240. Range(Cells(moduleStart + 19, 20), Cells(moduleStart + 19, 32)).Merge
  241. Range(Cells(moduleStart + 19, 20), Cells(moduleStart + 19, 32)).BorderAround ColorIndex:=1, Weight:=xlThin
  242.  
  243. 'Used/Refinance Equipment
  244. Cells(moduleStart + 21, 2).Value = "Used/Refinance Equipment"
  245. Range(Cells(moduleStart + 21, 20), Cells(moduleStart + 21, 32)).Merge
  246. Range(Cells(moduleStart + 21, 20), Cells(moduleStart + 21, 32)).BorderAround ColorIndex:=1, Weight:=xlThin
  247.  
  248. 'Settlement
  249. Cells(moduleStart + 23, 2).Value = "Settlement"
  250. Range(Cells(moduleStart + 23, 20), Cells(moduleStart + 23, 32)).Merge
  251. Range(Cells(moduleStart + 23, 20), Cells(moduleStart + 23, 32)).BorderAround ColorIndex:=1, Weight:=xlThin
  252.  
  253. 'Soft Costs
  254. Cells(moduleStart + 25, 2).Value = "Soft Costs"
  255. Range(Cells(moduleStart + 25, 20), Cells(moduleStart + 25, 32)).Merge
  256. Range(Cells(moduleStart + 25, 20), Cells(moduleStart + 25, 32)).BorderAround ColorIndex:=1, Weight:=xlThin
  257.  
  258. 'Air
  259. Cells(moduleStart + 18, 34).Value = "% Air"
  260.  
  261. 'New Equipment Rate
  262. Cells(moduleStart + 18, 40).Value = "Rate"
  263. Range(Cells(moduleStart + 19, 40), Cells(moduleStart + 19, 44)).Merge
  264. Range(Cells(moduleStart + 19, 40), Cells(moduleStart + 19, 44)).BorderAround ColorIndex:=1, Weight:=xlThin
  265. Cells(moduleStart + 19, 40).Value = leaseRate
  266.  
  267. 'Used/Refinance Rate
  268. Range(Cells(moduleStart + 21, 40), Cells(moduleStart + 21, 44)).Merge
  269. Range(Cells(moduleStart + 21, 40), Cells(moduleStart + 21, 44)).BorderAround ColorIndex:=1, Weight:=xlThin
  270. Cells(moduleStart + 21, 40).Value = leaseRate
  271.  
  272. 'Settlement Rate
  273. Range(Cells(moduleStart + 23, 40), Cells(moduleStart + 23, 44)).Merge
  274. Range(Cells(moduleStart + 23, 40), Cells(moduleStart + 23, 44)).BorderAround ColorIndex:=1, Weight:=xlThin
  275. Cells(moduleStart + 23, 40).Value = leaseRate
  276.  
  277. ' % of Air
  278. Range(Cells(moduleStart + 23, 34), Cells(moduleStart + 23, 36)).Merge
  279. Range(Cells(moduleStart + 23, 34), Cells(moduleStart + 23, 36)).BorderAround ColorIndex:=1, Weight:=xlThin
  280.  
  281. 'Soft Costs Rate
  282. Range(Cells(moduleStart + 25, 40), Cells(moduleStart + 25, 44)).Merge
  283. Range(Cells(moduleStart + 25, 40), Cells(moduleStart + 25, 44)).BorderAround ColorIndex:=1, Weight:=xlThin
  284. Cells(moduleStart + 25, 40).Value = leaseRate
  285.  
  286. 'Lease Payment
  287. Cells(moduleStart + 18, 49).Value = "Lease Payment"
  288. Cells(moduleStart + 19, 49).Value = Cells(moduleStart + 19, 40).Value * Cells(moduleStart + 19, 20).Value
  289. Range(Cells(moduleStart + 19, 49), Cells(moduleStart + 19, 49)).NumberFormat = "$0.00"
  290.  
  291. 'New Eqp Lease Payment
  292. Range(Cells(moduleStart + 19, 49), Cells(moduleStart + 19, 56)).Merge
  293. Range(Cells(moduleStart + 19, 49), Cells(moduleStart + 19, 56)).BorderAround ColorIndex:=1, Weight:=xlThin
  294.  
  295. 'Used / Refinanced EQP Lease payment
  296. Range(Cells(moduleStart + 21, 49), Cells(moduleStart + 21, 56)).Merge
  297. Range(Cells(moduleStart + 21, 49), Cells(moduleStart + 21, 56)).BorderAround ColorIndex:=1, Weight:=xlThin
  298. Range(Cells(moduleStart + 21, 49), Cells(moduleStart + 21, 49)).NumberFormat = "$0.00"
  299. Cells(moduleStart + 21, 49).Value = Cells(moduleStart + 21, 40).Value * Cells(moduleStart + 21, 20).Value
  300.  
  301. 'Settlement Lease Payment
  302. Range(Cells(moduleStart + 23, 49), Cells(moduleStart + 23, 56)).Merge
  303. Range(Cells(moduleStart + 23, 49), Cells(moduleStart + 23, 56)).BorderAround ColorIndex:=1, Weight:=xlThin
  304.  
  305. 'Soft Costs Lease Payment
  306. Range(Cells(moduleStart + 25, 49), Cells(moduleStart + 25, 56)).Merge
  307. Range(Cells(moduleStart + 25, 49), Cells(moduleStart + 25, 56)).BorderAround ColorIndex:=1, Weight:=xlThin
  308.  
  309. 'Rate Total
  310. Range(Cells(moduleStart + 31, 40), Cells(moduleStart + 31, 44)).Merge
  311. Range(Cells(moduleStart + 31, 40), Cells(moduleStart + 31, 44)).BorderAround ColorIndex:=1, Weight:=xlThin
  312.  
  313. 'Lease Paymment
  314. Range(Cells(moduleStart + 31, 49), Cells(moduleStart + 31, 56)).Merge
  315. Range(Cells(moduleStart + 31, 49), Cells(moduleStart + 31, 56)).BorderAround ColorIndex:=1, Weight:=xlThin
  316.  
  317. 'Total
  318. Cells(moduleStart + 27, 2).Value = "Total"
  319. Range(Cells(moduleStart + 27, 20), Cells(moduleStart + 27, 32)).Merge
  320. Range(Cells(moduleStart + 27, 20), Cells(moduleStart + 27, 32)).BorderAround ColorIndex:=1, Weight:=xlThin
  321.  
  322. 'Total Amount Financed
  323. Cells(moduleStart + 31, 2).Value = "Total Amount Financed"
  324. Cells(moduleStart + 31, 40).Value = leaseRate
  325. Range(Cells(moduleStart + 31, 20), Cells(moduleStart + 31, 32)).Merge
  326. Range(Cells(moduleStart + 31, 20), Cells(moduleStart + 31, 32)).BorderAround ColorIndex:=1, Weight:=xlThin
  327.  
  328. 'Misc
  329. Cells(moduleStart + 35, 58).Value = "*Please indicate any other pertinent information necessary"
  330. Cells(moduleStart + 35, 58).HorizontalAlignment = xlRight
  331. Range(Cells(moduleStart + 35, 1), Cells(moduleStart + 35, 58)).Interior.ColorIndex = 15
  332. Cells(moduleStart + 35, 1).Value = "E) Miscellaneous"
  333. Cells(moduleStart + 35, 1).Font.Bold = True
  334. Cells(moduleStart + 35, 2).HorizontalAlignment = xlLeft
  335. Range(Cells(moduleStart + 35, 1), Cells(moduleStart + 35, 58)).BorderAround ColorIndex:=1, Weight:=xlThin
  336. Rows(moduleStart + 36).RowHeight = 6.6
  337.  
  338. 'Misc Section
  339. Range(Cells(moduleStart + 37, 1), Cells(moduleStart + 39, 58)).Merge
  340. Range(Cells(moduleStart + 37, 1), Cells(moduleStart + 39, 58)).BorderAround ColorIndex:=1, Weight:=xlThin
  341. Rows(moduleStart + 40).RowHeight = 6.6
  342.  
  343.  
  344. 'Footer thick side border
  345.    With Worksheets(leaseSheet).Range(Cells(moduleStart - 1, 58), Cells(moduleStart + 40, 58)).Borders(xlEdgeRight)
  346.        .LineStyle = xlContinuous
  347.        .Weight = xlMedium
  348.     End With
  349.  
  350.     With Worksheets(leaseSheet).Range(Cells(moduleStart + 40, 1), Cells(moduleStart + 40, 58)).Borders(xlEdgeBottom)
  351.        .LineStyle = xlContinuous
  352.        .Weight = xlMedium
  353.     End With
  354.  
  355.  
  356. ' //////////////////////////////
  357. ' //Fill Movement Forms Script//
  358. ' //////////////////////////////
  359. currentWs = 16
  360. currentItem = 16
  361. lpmItem = 16
  362. standardSheetNumber = 16
  363. mySheets = Worksheets.Count
  364. firstProduct = 0
  365. StandardRowsPCList = 15
  366. StandardRowsInSheet = 15
  367. equipmentSheet = 2
  368. productListEnd = Sheets(2).Range("E16").End(xlDown).Row - StandardRowsPCList
  369. itemsInSheet = 0
  370. filledItems = 0
  371. lpmSheet = 7
  372.  
  373. For i = standardSheetNumber To mySheets
  374.    
  375.     Sheets(currentWs).Activate
  376.     itemsInSheet = Sheets(currentWs).Range("A16").End(xlDown).Row - StandardRowsInSheet
  377.    
  378.     For j = 0 To productListEnd - 1
  379.    
  380.         ProductCode = Sheets(equipmentSheet).Cells(currentItem + j, 5).Value
  381.         productQty = Sheets(equipmentSheet).Cells(currentItem + j, 1).Value
  382.         productCost = Sheets(equipmentSheet).Cells(currentItem + j, 10).Value
  383.         mappCost = Sheets(equipmentSheet).Cells(currentItem + j, 12).Value
  384.        
  385.         For k = firstProduct To itemsInSheet
  386.             If Sheets(currentWs).Cells(currentItem + k, 4).Value <> 1 Then
  387.                 pcToCheck = Sheets(currentWs).Cells(currentItem + k, 1).Value
  388.  
  389.                 If pcToCheck = ProductCode Then
  390.                     Sheets(currentWs).Cells(currentItem + k, 6).Value = productCost
  391.                     Sheets(currentWs).Cells(currentItem + k, 7).Value = mappCost
  392.                     Sheets(currentWs).Cells(currentItem + k, 4).Value = Sheets(currentWs).Cells(currentItem + k, 4).Value + 1
  393.                     Exit For
  394.                 End If
  395.            
  396.             End If
  397.         Next k
  398.        
  399.         filledItems = WorksheetFunction.Sum(Sheets(currentWs).Range(Cells(16, 4), Cells(16 + itemsInSheet, 4)))
  400.        
  401.         If filledItems = itemsInSheet Then
  402.             totalPrice = WorksheetFunction.Sum(Sheets(currentWs).Range(Cells(16, 6), Cells(16 + itemsInSheet, 6)))
  403.             totalMapp = WorksheetFunction.Sum(Sheets(currentWs).Range(Cells(16, 7), Cells(16 + itemsInSheet, 7)))
  404.  
  405.             Sheets(lpmSheet).Cells(lpmItem, 15).Value = totalPrice
  406.             Sheets(lpmSheet).Cells(lpmItem, 30).Value = totalMapp
  407.            
  408.             'Diff from MAPP
  409.            Sheets(lpmSheet).Cells(lpmItem, 45).Value = Cells(lpmItem, 30) - Cells(lpmItem, 15).Value
  410.            
  411.             'Totals
  412.            Sheets(lpmSheet).Activate
  413.             sellTotal = WorksheetFunction.Sum(Sheets(lpmSheet).Range(Cells(16, 15), Cells(16 + (mySheets - standardSheetNumber), 15)))
  414.             mappTotal = WorksheetFunction.Sum(Sheets(lpmSheet).Range(Cells(16, 30), Cells(16 + (mySheets - standardSheetNumber), 30)))
  415.             diffTotal = WorksheetFunction.Sum(Sheets(lpmSheet).Range(Cells(16, 45), Cells(16 + (mySheets - standardSheetNumber), 45)))
  416.  
  417.             Sheets(lpmSheet).Cells(moduleStart, 15).Value = sellTotal
  418.             Sheets(lpmSheet).Cells(moduleStart, 30).Value = mappTotal
  419.             Sheets(lpmSheet).Cells(moduleStart, 45).Value = diffTotal
  420.  
  421.             lpmItem = lpmItem + 2
  422.             Exit For
  423.         End If
  424.  
  425.     Next j
  426.    
  427.     If currentWs < mySheets Then
  428.         currentWs = currentWs + 1
  429.     Else
  430.         Sheets(lpmSheet).Activate
  431.     End If
  432.  
  433.  
  434. Next i
  435.  
  436.  
  437. Dim thisSettlement As Long
  438.  
  439. RowCount = Sheets(2).Range("C" & Rows.Count).End(xlUp).Row
  440.  
  441. For i = 16 To RowCount
  442.     testForSettlement = Sheets(2).Cells(i, 3).Value
  443.    
  444.     If testForSettlement = "SETTLEMENT" Then
  445.         thisSettlement = Sheets(2).Cells(i, 10).Value
  446.         Cells(35, 27).Value = Cells(35, 27).Value + thisSettlement
  447.     End If
  448. Next i
  449.  
  450. 'Net Equipment Value
  451. Cells(moduleStart + 10, 27).Value = Cells(moduleStart, 15).Value
  452.  
  453. 'New Equipment Value
  454. Cells(moduleStart + 19, 20).Value = Cells(moduleStart + 10, 27).Value
  455.  
  456. 'Invoice price
  457. formulaString = "=SUM(AA"
  458. formulaString = formulaString + CStr(moduleStart + 4)
  459. formulaString = formulaString + ":"
  460. formulaString = formulaString + "AA"
  461. formulaString = formulaString + CStr(moduleStart + 12)
  462. formulaString = formulaString + ")"
  463. formulaString = CStr(formulaString)
  464. Range(Cells(moduleStart + 14, 27), Cells(moduleStart + 14, 27)).Formula = formulaString
  465.  
  466. 'Settlement
  467. settlementFormulastring = "=AA"
  468. settlementFormulastring = settlementFormulastring + CStr(moduleStart + 11)
  469. Range(Cells(moduleStart + 23, 20), Cells(moduleStart + 23, 20)).Formula = settlementFormulastring
  470.  
  471.  
  472. '%of Air
  473. If Cells(moduleStart + 23, 20).Value = 0 Then
  474.     Cells(moduleStart + 23, 34).Value = 0
  475. Else
  476.     Air = Cells(moduleStart + 23, 20).Value / Cells(moduleStart + 14, 27).Value
  477.     Cells(moduleStart + 23, 34).Value = Air
  478. End If
  479.  
  480. 'Number Formats
  481. Range(Cells(moduleStart + 23, 34), Cells(moduleStart + 23, 34)).NumberFormat = "0.00%"
  482. Range(Cells(moduleStart + 4, 27), Cells(moduleStart + 14, 27)).NumberFormat = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* ""-""??_-;_-@_-"
  483.  
  484.  
  485. 'Soft Costs
  486. 'softCosts = WorksheetFunction.Sum(Sheets(lpmSheet).Range(Cells(moduleStart + 4, 27), Cells(moduleStart + 9, 27)))
  487. 'Cells(moduleStart + 25, 20).Value = softCosts
  488.  
  489. 'Lock cells and protect sheet
  490. Range(Cells(moduleStart, 1), Cells(moduleStart + 30, 58)).Locked = False
  491. Range(Cells(1, 1), Cells(moduleStart - 1, 58)).Locked = True
  492. ActiveSheet.Protect Password:="sherpadoc1"
  493.  
  494.  
  495. 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!