API tools faq
paste
Advertisement
Guest User

Husky

a guest
Jun 14th, 2019
149
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
VisualBasic 11.35 KB | None | 0 0
raw download clone embed print report
  1. Sub SEMACO()
  2.  
  3. Dim x As Integer
  4. Dim row As Integer
  5. Dim typ As String
  6. Dim specificType As String
  7. Dim price As Double
  8. Dim hours As String
  9. Dim totalPrice As Double
  10. Dim totalHours As Double
  11. Dim visited As Boolean
  12. Dim tableStart As Integer
  13. Dim tableWidth As Integer
  14. Dim tableEnd As Integer
  15. Dim highlightedRows(10) As Integer
  16. Dim i As Integer
  17. Dim j As Integer
  18. Dim sum1 As Double
  19. Dim sum2 As Double
  20. Dim sum3 As Double
  21. Dim sum4 As Double
  22. Dim sum5 As Double
  23. Dim shipPrice As Double
  24. Dim packPrice As Double
  25.  
  26.  
  27. x = 2
  28. row = 7
  29. price = 0
  30. hours = 0
  31. totalPrice = 0
  32. totalHours = 0
  33. visited = False
  34. tableStart = row - 2
  35. tableWidth = 6
  36. i = 0
  37. j = 0
  38. sum1 = 0
  39. sum2 = 0
  40. sum3 = 0
  41. sum4 = 0
  42. sum5 = 0
  43. shipPrice = 0
  44. packPrice = 0
  45.  
  46. '-------Predvyroba
  47. typ = Mid(GetValue("M000447", x), 2, 1)
  48.  
  49. While typ = "1"
  50.     specificType = GetValue("M000447", x)
  51.    
  52.     Do While GetValue("M000447", x) = specificType
  53.         price = price + CDbl(GetValue("M000162", x))
  54.         hours = hours + CDbl(GetValue("M000161", x) / 60)
  55.        
  56.         x = x + 1
  57.     Loop
  58.    
  59.     Sheets(1).Cells(row, 1) = specificType
  60.     Sheets(1).Cells(row, 2) = hours
  61.     Sheets(1).Cells(row, 3) = price
  62.    
  63.     totalPrice = totalPrice + price
  64.     totalHours = totalHours + hours
  65.    
  66.     price = 0
  67.     hours = 0
  68.  
  69.     typ = Mid(GetValue("M000447", x), 2, 1)
  70.     row = row + 1
  71.     visited = True
  72.  
  73. Wend
  74.  
  75. If visited Then
  76.     Sheets(1).Cells(row, 1) = "Předvýroba"
  77.     Sheets(1).Cells(row, 2) = totalHours
  78.     Sheets(1).Cells(row, 3) = totalPrice
  79.     highlightedRows(i) = row
  80.     i = i + 1
  81.     BoldRow (row)
  82.     visited = False
  83.     row = row + 1
  84. End If
  85.  
  86. sum1 = sum1 + totalHours
  87. sum2 = sum2 + totalPrice
  88. totalHours = 0
  89. totalPrice = 0
  90.  
  91. '-------Vyroba
  92. While typ = "2"
  93.     specificType = GetValue("M000447", x)
  94.    
  95.     Do While GetValue("M000447", x) = specificType
  96.         price = price + CDbl(GetValue("M000162", x))
  97.         hours = hours + CDbl(GetValue("M000161", x) / 60)
  98.        
  99.         x = x + 1
  100.     Loop
  101.    
  102.     Sheets(1).Cells(row, 1) = specificType
  103.     Sheets(1).Cells(row, 2) = hours
  104.     Sheets(1).Cells(row, 3) = price
  105.    
  106.     totalPrice = totalPrice + price
  107.     totalHours = totalHours + hours
  108.    
  109.     price = 0
  110.     hours = 0
  111.  
  112.     typ = Mid(GetValue("M000447", x), 2, 1)
  113.     row = row + 1
  114.     visited = True
  115.  
  116. Wend
  117.  
  118. If visited Then
  119.     Sheets(1).Cells(row, 1) = "Výroba"
  120.     Sheets(1).Cells(row, 2) = totalHours
  121.     Sheets(1).Cells(row, 3) = totalPrice
  122.     highlightedRows(i) = row
  123.     i = i + 1
  124.     BoldRow (row)
  125.     visited = False
  126.     row = row + 1
  127. End If
  128.  
  129. sum1 = sum1 + totalHours
  130. sum2 = sum2 + totalPrice
  131. totalHours = 0
  132. totalPrice = 0
  133.  
  134. '--------Manufacturing
  135. While typ = "3"
  136.     specificType = GetValue("M000447", x)
  137.    
  138.     Do While GetValue("M000447", x) = specificType
  139.         price = price + CDbl(GetValue("M000162", x))
  140.         hours = hours + CDbl(GetValue("M000161", x) / 60)
  141.        
  142.         x = x + 1
  143.     Loop
  144.    
  145.     Sheets(1).Cells(row, 1) = specificType
  146.     Sheets(1).Cells(row, 2) = hours
  147.     Sheets(1).Cells(row, 3) = price
  148.    
  149.     totalPrice = totalPrice + price
  150.     totalHours = totalHours + hours
  151.    
  152.     price = 0
  153.     hours = 0
  154.  
  155.     typ = Mid(GetValue("M000447", x), 2, 1)
  156.     row = row + 1
  157.     visited = True
  158.  
  159. Wend
  160.  
  161. If visited Then
  162.     Sheets(1).Cells(row, 1) = "Manufacturing"
  163.     Sheets(1).Cells(row, 2) = totalHours
  164.     Sheets(1).Cells(row, 3) = totalPrice
  165.  
  166.     BoldRow (row)
  167.     highlightedRows(i) = row
  168.     i = i + 1
  169.     visited = False
  170.     row = row + 1
  171. End If
  172.  
  173. sum1 = sum1 + totalHours
  174. sum2 = sum2 + totalPrice
  175. totalHours = 0
  176. totalPrice = 0
  177.  
  178. '--------Testing mold (neni vyzkouseno)
  179. While typ = "4"
  180.     specificType = GetValue("M000447", x)
  181.    
  182.     Do While GetValue("M000447", x) = specificType
  183.         price = price + CDbl(GetValue("M000162", x))
  184.         hours = hours + CDbl(GetValue("M000161", x) / 60)
  185.        
  186.         x = x + 1
  187.     Loop
  188.    
  189.     Sheets(1).Cells(row, 1) = specificType
  190.     Sheets(1).Cells(row, 2) = hours
  191.     Sheets(1).Cells(row, 3) = price
  192.    
  193.     totalPrice = totalPrice + price
  194.     totalHours = totalHours + hours
  195.    
  196.     price = 0
  197.     hours = 0
  198.  
  199.     typ = Mid(GetValue("M000447", x), 2, 1)
  200.     row = row + 1
  201.     visited = True
  202.  
  203. Wend
  204.  
  205. If visited Then
  206.     Sheets(1).Cells(row, 1) = "Manufacturing"
  207.     Sheets(1).Cells(row, 2) = totalHours
  208.     Sheets(1).Cells(row, 3) = totalPrice
  209.  
  210.     BoldRow (row)
  211.     highlightedRows(i) = row
  212.     i = i + 1
  213.     visited = False
  214.     row = row + 1
  215. End If
  216.  
  217. sum1 = sum1 + totalHours
  218. sum2 = sum2 + totalPrice
  219. totalHours = 0
  220. totalPrice = 0
  221.  
  222. '-------Hruby material
  223. typ = GetValue("M000449", x)
  224.  
  225. While typ = "2"
  226.     specificType = GetValue("M000447", x)
  227.    
  228.     Do While GetValue("M000447", x) = specificType
  229.         If GetValue("M004725", x) <> "" Then
  230.             price = price + CDbl(GetValue("M004725", x))
  231.         ElseIf GetValue("M000162", x) <> "" Then
  232.             price = price + CDbl(GetValue("M000162", x))
  233.         Else
  234.             price = price + 0
  235.         End If
  236.  
  237.         x = x + 1
  238.     Loop
  239.    
  240.     Sheets(1).Cells(row, 1) = specificType
  241.     Sheets(1).Cells(row, 4) = price
  242.    
  243.     totalPrice = totalPrice + price
  244.    
  245.     price = 0
  246.  
  247.     typ = GetValue("M000449", x)
  248.     row = row + 1
  249.     visited = True
  250.  
  251. Wend
  252.  
  253. If visited Then
  254.     Sheets(1).Cells(row, 1) = "Hrubý materiál"
  255.     Sheets(1).Cells(row, 4) = totalPrice
  256.  
  257.     highlightedRows(i) = row
  258.     i = i + 1
  259.     BoldRow (row)
  260.     visited = False
  261.     row = row + 1
  262. End If
  263.  
  264. sum3 = sum3 + totalPrice
  265. totalHours = 0
  266. totalPrice = 0
  267.  
  268. '-------Nakupovany material
  269.  
  270. While typ = "3"
  271.     specificType = GetValue("M000447", x)
  272.    
  273.     Do While GetValue("M000447", x) = specificType
  274.         If GetValue("M004725", x) <> "" Then
  275.             price = price + CDbl(GetValue("M004725", x))
  276.         ElseIf GetValue("M000162", x) <> "" Then
  277.             price = price + CDbl(GetValue("M000162", x))
  278.         Else
  279.             price = price + 0
  280.         End If
  281.  
  282.         x = x + 1
  283.     Loop
  284.    
  285.     Sheets(1).Cells(row, 1) = specificType
  286.     Sheets(1).Cells(row, 4) = price
  287.    
  288.     totalPrice = totalPrice + price
  289.    
  290.     price = 0
  291.  
  292.     typ = GetValue("M000449", x)
  293.     row = row + 1
  294.     visited = True
  295.  
  296. Wend
  297.  
  298. If visited Then
  299.     Sheets(1).Cells(row, 1) = "Nakupovaný materiál"
  300.     Sheets(1).Cells(row, 4) = totalPrice
  301.    
  302.     highlightedRows(i) = row
  303.     i = i + 1
  304.     BoldRow (row)
  305.     visited = False
  306.     row = row + 1
  307. End If
  308.  
  309. sum3 = sum3 + totalPrice
  310. totalHours = 0
  311. totalPrice = 0
  312.  
  313. '-------Kooperace
  314.  
  315. While typ = "4"
  316.     specificType = GetValue("M000447", x)
  317.    
  318.     If specificType = "ship" Then
  319.         If GetValue("M004725", x) <> "" Then
  320.             shipPrice = shipPrice + CDbl(GetValue("M004725", x))
  321.         ElseIf GetValue("M000162", x) <> "" Then
  322.             shipPrice = shipPrice + CDbl(GetValue("M000162", x))
  323.         Else
  324.             shipPrice = shipPrice + 0
  325.         End If
  326.        
  327.         x = x + 1
  328.        
  329.     ElseIf specificType = "pack" Then
  330.         If GetValue("M004725", x) <> "" Then
  331.             packPrice = packPrice + CDbl(GetValue("M004725", x))
  332.         ElseIf GetValue("M000162", x) <> "" Then
  333.             packPrice = packPrice + CDbl(GetValue("M000162", x))
  334.         Else
  335.             packPrice = packPrice + 0
  336.         End If
  337.        
  338.         x = x + 1
  339.        
  340.     Else
  341.         Do While GetValue("M000447", x) = specificType
  342.             If GetValue("M004725", x) <> "" Then
  343.                 price = price + CDbl(GetValue("M004725", x))
  344.             ElseIf GetValue("M000162", x) <> "" Then
  345.                 price = price + CDbl(GetValue("M000162", x))
  346.             Else
  347.                 price = price + 0
  348.             End If
  349.            
  350.             hours = hours + CDbl(GetValue("M000161", x) / 60)
  351.             x = x + 1
  352.         Loop
  353.        
  354.         Sheets(1).Cells(row, 1) = specificType
  355.         Sheets(1).Cells(row, 5) = hours
  356.         Sheets(1).Cells(row, 6) = price
  357.        
  358.         totalPrice = totalPrice + price
  359.         totalHours = totalHours + hours
  360.        
  361.         row = row + 1
  362.         visited = True
  363.        
  364.     End If
  365.        
  366.     price = 0
  367.     hours = 0
  368.    
  369.     typ = GetValue("M000449", x)
  370.  
  371. Wend
  372.  
  373. If visited Then
  374.     Sheets(1).Cells(row, 1) = "Kooperace"
  375.     Sheets(1).Cells(row, 5) = totalHours
  376.     Sheets(1).Cells(row, 6) = totalPrice
  377.    
  378.     highlightedRows(i) = row
  379.     i = i + 1
  380.     BoldRow (row)
  381.     visited = False
  382.     row = row + 1
  383. End If
  384.    
  385. sum4 = sum4 + totalHours
  386. sum5 = sum5 + totalPrice
  387. totalHours = 0
  388. totalPrice = 0
  389.    
  390. '-------Ostatni
  391. If packPrice <> 0 Then
  392.     Sheets(1).Cells(row, 1) = "pack"
  393.     Sheets(1).Cells(row, 6) = packPrice
  394.     row = row + 1
  395.     visited = True
  396. End If
  397.  
  398. If shipPrice <> 0 Then
  399.     Sheets(1).Cells(row, 1) = "ship"
  400.     Sheets(1).Cells(row, 6) = shipPrice
  401.     row = row + 1
  402.     visited = True
  403. End If
  404.  
  405. If visited Then
  406.     Sheets(1).Cells(row, 1) = "Ostatní"
  407.     Sheets(1).Cells(row, 6) = packPrice + shipPrice
  408.    
  409.     highlightedRows(i) = row
  410.     i = i + 1
  411.     BoldRow (row)
  412.     visited = False
  413. End If
  414.  
  415. sum5 = sum5 + packPrice + shipPrice
  416. row = row + 1
  417. tableEnd = row
  418.  
  419. Sheets(1).Cells(row, 1) = "Total sum"
  420. Sheets(1).Cells(row, 2) = sum1
  421. Sheets(1).Cells(row, 3) = sum2
  422. Sheets(1).Cells(row, 4) = sum3
  423. Sheets(1).Cells(row, 5) = sum4
  424. Sheets(1).Cells(row, 6) = sum5
  425. BoldRow (row)
  426. row = row + 1
  427.  
  428. Sheets(1).Cells(row, 1) = "Total Costs"
  429. Sheets(1).Cells(row, 2) = sum3 + sum5
  430. BoldRow (row)
  431. row = row + 1
  432.  
  433. Sheets(1).Cells(row, 1) = "Forecast Costs"
  434. Sheets(1).Cells(row, 2) = sum2
  435. BoldRow (row)
  436.  
  437.  
  438. '-------Kresleni ohraniceni
  439. For i = 0 To tableWidth - 1
  440.     Call MakeFrame(tableStart, i + 1, tableEnd, i + 1)
  441. Next i
  442.  
  443. Call MakeFrame(tableStart, 1, tableStart + 1, tableWidth)
  444.  
  445. For i = LBound(highlightedRows) To UBound(highlightedRows)
  446.     If highlightedRows(i) <> 0 Then
  447.         For j = 0 To tableWidth - 1
  448.             Call RemoveFrame(highlightedRows(i), j + 1, highlightedRows(i), j + 2)
  449.         Next j
  450.         Call MakeFrame(highlightedRows(i), 1, highlightedRows(i), tableWidth)
  451.     End If
  452. Next i
  453.  
  454.  
  455.  
  456. End Sub
  457.  
  458. Function BoldRow(row As Integer)
  459.     Sheets(1).Cells(row, 1).EntireRow.Font.Bold = True
  460. End Function
  461.  
  462. Function MakeFrame(row1 As Integer, col1 As Integer, row2 As Integer, col2 As Integer)
  463.     Sheets(1).Range(Sheets(1).Cells(row1, col1), Sheets(1).Cells(row2, col2)).Borders(xlEdgeBottom).LineStyle = xlContinuous
  464.     Sheets(1).Range(Sheets(1).Cells(row1, col1), Sheets(1).Cells(row2, col2)).Borders(xlEdgeRight).LineStyle = xlContinuous
  465.     Sheets(1).Range(Sheets(1).Cells(row1, col1), Sheets(1).Cells(row2, col2)).Borders(xlEdgeTop).LineStyle = xlContinuous
  466.     Sheets(1).Range(Sheets(1).Cells(row1, col1), Sheets(1).Cells(row2, col2)).Borders(xlEdgeLeft).LineStyle = xlContinuous
  467. End Function
  468.  
  469. Function RemoveFrame(row1 As Integer, col1 As Integer, row2 As Integer, col2 As Integer)
  470.     Sheets(1).Range(Sheets(1).Cells(row1, col1), Sheets(1).Cells(row2, col2)).Borders(xlEdgeBottom).LineStyle = xlNone
  471.     Sheets(1).Range(Sheets(1).Cells(row1, col1), Sheets(1).Cells(row2, col2)).Borders(xlEdgeRight).LineStyle = xlNone
  472.     Sheets(1).Range(Sheets(1).Cells(row1, col1), Sheets(1).Cells(row2, col2)).Borders(xlEdgeTop).LineStyle = xlNone
  473.     Sheets(1).Range(Sheets(1).Cells(row1, col1), Sheets(1).Cells(row2, col2)).Borders(xlEdgeLeft).LineStyle = xlNone
  474. End Function
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!