API tools faq
paste
Advertisement
Guest User

FINAL!

a guest
May 26th, 2016
67
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
VisualBasic 39.69 KB | None | 0 0
raw download clone embed print report
  1. Global massiv() As String
  2. Public r As String
  3. Global xxx As Integer
  4.  
  5.  
  6.  
  7.  
  8.  
  9. Sub êîìàíäèðîâêè1()
  10. Application.ScreenUpdating = False
  11. Workbooks("Iskhodnye_dannye.xls").Save
  12. Workbooks("Iskhodnye_dannye.xls").Sheets("Êîìàíäèðîâêè").Range("H2").Select
  13. Range("H2").Select
  14.     ActiveCell.FormulaR1C1 = "=QUOTIENT(QUOTIENT(RC[-1],RC[-4]),RC[-5])"
  15.     Selection.AutoFill Destination:=Range("H2:H300"), Type:=xlFillDefault
  16.  
  17.  
  18. Workbooks("Iskhodnye_dannye").Worksheets("Êîìàíäèðîâêè").Columns(8).Hidden = True
  19. Workbooks.Open Filename:=Workbooks("Iskhodnye_dannye").Path + "\Tseli.xls"
  20. Workbooks.Add
  21.  
  22. r = Format(Now(), "YYYY.MM.DD hh-mm-ss")
  23.     ActiveWorkbook.SaveAs Filename:=Workbooks("Iskhodnye_dannye").Path + "\Ïëàí êîìàíäèðîâîê " + r + ".xlsx", _
  24.     FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
  25.         ReadOnlyRecommended:=False, CreateBackup:=False
  26. k = k + 1
  27. '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  28. '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  29. Dim nom As String 'íîìåð êîìàíäèðîâêè
  30. Dim d As Date 'äàòà êîìàíäèðîâêè
  31. Dim srok_komandirovki As Integer 'ñðîê êîìàíäèðîâêè
  32. Dim min_ruk As Double 'ìèíèìàëüíàÿ çàðïëàòà ðóêîâîäèòåëÿ
  33. Dim min_rab As Double 'ìèíèìàëüíàÿ çàðïëàòà ðàáîòíèêà
  34. Dim fam_ruk As String 'ôàìèëèÿ ðóêîâîäèòåëÿ ñ ìåíüøåé çàðïëàòîé
  35. Dim nomer_ruk As Integer 'íîìåð ÷åëîâåêà ñ ìèíèìàëüíîé çàðïëàòîé ñðåäè ðóêîâîäèòåëåé
  36. Dim nomer_rab As Integer 'íîìåð ÷åëîâåêà ñ ìèíèìàëüíîé çàðïëàòîé ñðåäè ðàáîòíèêîâ
  37. Dim kol_kom As Integer 'êîëè÷åñòâî êîìàíäèðîâàííûõ
  38. Dim ts As String ' öåëü êîìàíäèðîâêè
  39. Dim mesto As String 'ìåñòî êîìàíäèðîâêè
  40. Dim opl_dog As String 'îïëòà ïî äîãîâîðó
  41. Dim mass() As String 'ìàññèâ âñåõ ðàáîòíèêîâ
  42. Dim Masd() As String 'ìàññèâ äëÿ äîëæíîñòåé ðóêîâîäèòåëåé
  43. Dim MasR() As String 'ìàññèâ äëÿ äîëæíîñòåé ðàáîòíèêîâ
  44. Dim rukdol As Integer 'êîëè÷åñòâî äîëæíîñòåé ðóêîâîäèòåëåé
  45. Dim rabdol As Integer 'êîëè÷åñòâî äîëæíîñòåé ðàáîòíèêîâ
  46. Dim Maskom() As String 'ìàññèâ âñåõ ðàáîòíèêîâ êîìàíäèðîâêè
  47. Dim krug As Double 'íîìåð êðóãà
  48. Dim krug2 As Double 'íîìåð êðóãà
  49. Dim vihodnie As Integer 'êîëè÷åñòâî âûõîäíûõ äíåé
  50. Dim z As Integer
  51. Dim zz As Integer
  52. Dim prv As String
  53. Dim prv1 As String
  54. Dim prv2 As String
  55. Dim prv3 As String
  56. Dim Msg As String
  57. Dim i, j, n, rab, har, x, xx, y, first, first_r, nomd As Integer
  58.  
  59. har = 6
  60. i = 2
  61. com = 1
  62. x = 1
  63. xx = 1
  64. ''' çàïîëíèì ìàññèâ ðàáîòíèêîâ
  65. ReDim Preserve mass(7, Workbooks("Iskhodnye_dannye").Worksheets("Ñîòðóäíèêè").Cells.SpecialCells(xlLastCell).Row)
  66. Do While Not IsEmpty(Workbooks("Iskhodnye_dannye").Worksheets("Ñîòðóäíèêè").Cells(x, 1))
  67. mass(1, x) = Workbooks("Iskhodnye_dannye").Worksheets("Ñîòðóäíèêè").Cells(x, 1) '
  68. mass(2, x) = Workbooks("Iskhodnye_dannye").Worksheets("Ñîòðóäíèêè").Cells(x, 2)
  69. mass(3, x) = Workbooks("Iskhodnye_dannye").Worksheets("Ñîòðóäíèêè").Cells(x, 3)
  70. mass(4, x) = Workbooks("Iskhodnye_dannye").Worksheets("Ñîòðóäíèêè").Cells(x, 4)
  71. mass(5, x) = Workbooks("Iskhodnye_dannye").Worksheets("Ñîòðóäíèêè").Cells(x, 5)
  72. mass(6, x) = CStr(Workbooks("Iskhodnye_dannye").Worksheets("Ñîòðóäíèêè").Cells(x, 6))
  73. mass(7, x) = CDate(0)
  74. x = x + 1
  75. Loop
  76. '''''
  77.  
  78.  
  79. Application.CutCopyMode = False
  80. Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Range("a1:g1").Copy
  81. Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Range("a1:g1").PasteSpecial Paste:=xlPasteColumnWidths
  82. Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Range("a1:g1").Copy Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Range("a1:g1")
  83. Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Range("a1").Copy
  84.     With Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Range("f1:k1")
  85.         .PasteSpecial Paste:=xlPasteFormats
  86.         .PasteSpecial Paste:=xlPasteColumnWidths
  87.     End With
  88.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Columns("C:C").ColumnWidth = 15.86
  89.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Columns("D:D").ColumnWidth = 13.43
  90.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Columns("E:E").ColumnWidth = 21.71
  91.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Columns("H:H").ColumnWidth = 30.43
  92.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(1, 8) = "Êîìàíäèðîâàííûå"
  93.  Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(1, 9) = "Òðàíñïîðò"
  94.  Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(1, 10) = "Çàòðàòû"
  95.  Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(1, 11) = "Ñòàòóñ êîìàíäèðîâêè"
  96.  
  97.  Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Rows(2).Select
  98.   With ActiveWindow
  99.           .FreezePanes = True
  100.           .Zoom = 85
  101.  End With
  102.  
  103.  
  104. Do While Not IsEmpty(Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Cells(i, 1))
  105. ReDim Preserve Maskom(0)
  106. nomd = 0
  107. krug = 1
  108. krug2 = 0
  109. min_ruk = 0
  110. min_rab = 0
  111. first = 1
  112. first_r = 1
  113. z = 2
  114. zz = 2
  115. fam_ruk = CStr(0)
  116.  
  117.  
  118. vihodnie = Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Cells(i, 3)
  119.   For c = 0 To vihodnie
  120.     If Weekday(Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Cells(i, 2) + c) = 1 Then
  121.     vihodnie = vihodnie + 1
  122.     End If
  123.     If Weekday(Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Cells(i, 2) + c) = 7 Then
  124.     vihodnie = vihodnie + 1
  125.     End If
  126.   Next
  127.  
  128.  
  129.  Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 2).Value = Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Cells(i, 2).Value
  130.  Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 1) = Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Cells(i, 1)
  131.  Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 3) = Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Cells(i, 3)
  132.  Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 4) = Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Cells(i, 4)
  133.  Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 5) = Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Cells(i, 5)
  134.  Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 6) = Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Cells(i, 6)
  135.  Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 7) = Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Cells(i, 7)
  136.  
  137.  
  138.  'ïðîâåðêà íà êîëè÷åñòâî ðóêîâîäèòåëåé
  139.  
  140.  kol_kom = Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Cells(i, 4)
  141.  
  142.  Dim sh As Integer
  143.  sh = 1
  144.  j = 1
  145.  rukdol = 0
  146.  rabdol = 0
  147.   Do Until sh >= Workbooks("Tseli").Sheets.Count
  148.  
  149.           If Workbooks("Tseli").Sheets(sh).Name = Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Cells(i, 5).Value Then
  150.               Do While Not IsEmpty(Workbooks("Tseli").Worksheets(sh).Cells(j, 1))
  151.                     If Workbooks("Tseli").Worksheets(sh).Cells(j, 2) = 1 Then
  152.                     rukdol = rukdol + 1
  153.                     ReDim Preserve Masd(rukdol)
  154.                     Masd(rukdol) = Workbooks("Tseli").Worksheets(sh).Cells(j, 1).Value
  155.                     End If
  156.                    
  157.                    
  158.                      If Workbooks("Tseli").Worksheets(sh).Cells(j, 3) = 1 Then
  159.                     rabdol = rabdol + 1
  160.                     ReDim Preserve MasR(rabdol)
  161.                     MasR(rabdol) = Workbooks("Tseli").Worksheets(sh).Cells(j, 1).Value
  162.                     End If
  163.               j = j + 1
  164.               Loop
  165.          
  166.               If Workbooks("Tseli").Worksheets(sh).Cells(1, 1) > Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Cells(i, 4) Then
  167.               Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Cells(i, 4) = Workbooks("Tseli").Worksheets(sh).Cells(1, 1)
  168.               kol_kom = Workbooks("Tseli").Worksheets(sh).Cells(1, 1)
  169.               End If
  170.                ReDim Preserve Maskom(kol_kom)
  171.              
  172.              
  173.               For w = 1 To UBound(Masd, 1)
  174.                      For q = 1 To UBound(mass, 2)
  175.                        If Masd(w) = mass(2, q) Then
  176.                           If CDate(mass(7, q)) < CDate(Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Cells(i, 2)) Then  'åñëè ðàáîòíèê ñâîáîäåí
  177.                               If first = 1 Then 'åñëè ïåðâûé ïîäõîäÿùèé
  178.                               min_ruk = mass(6, q)
  179.                                first = first + 1
  180.                                End If
  181.                            If mass(6, q) <= min_ruk Then 'íàõîäèì ìèíèìàëüíóþ çàðïëàòó
  182.                           min_ruk = mass(6, q)
  183.                            nomer_ruk = q
  184.                            End If
  185.                           End If
  186.                        End If
  187.                      
  188.                      Next
  189.          
  190.               Next
  191.         mass(7, nomer_ruk) = CDate(Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Cells(i, 2).Value) + CDate(vihodnie)
  192.      
  193.      Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 8) = mass(1, nomer_ruk) + "-" + mass(2, nomer_ruk)
  194.       Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 10) = mass(6, nomer_ruk) / 20 * Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Cells(i, 3)
  195.      Maskom(0) = mass(1, nomer_ruk)
  196.      
  197.      
  198.      
  199.       If nomer_ruk = 0 Then
  200.       Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 11) = "Êîìàíäèðîâêà îòìåíåíà èç-çà òîãî, ÷òî íåò ñâîáîäíîãî ðóêîâîäèòåëÿ"
  201.       GoTo bbb
  202.       End If
  203.      
  204.      
  205.       If Format(Now(), "yyyy") - Format(CDate(mass(5, nomer_ruk)), "yyyy") < 28 Then
  206.       For w = 1 To UBound(MasR, 1)
  207.        first_r = 1
  208.        nomer_ruk = 0
  209.                      For q = 1 To UBound(mass, 2)
  210.                        If MasR(w) = mass(2, q) Then
  211.                           If CDate(mass(7, q)) < CDate(Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Cells(i, 2)) Then  'åñëè ðàáîòíèê ñâîáîäåí
  212.                          If Format(Now(), "yyyy") - Format(CDate(mass(5, nomer_ruk)), "yyyy") > 50 Then
  213.                                If first = 1 Then 'åñëè ïåðâûé ïîäõîäÿùèé
  214.                               min_rab = mass(6, q)
  215.                                first_r = first_r + 1
  216.                                End If
  217.                            End If
  218.                            If mass(6, q) <= min_rab Then 'íàõîäèì ìèíèìàëüíóþ çàðïëàòó
  219.                           min_rab = mass(6, q)
  220.                            nomer_ruk = q
  221.                            End If
  222.                           End If
  223.                        End If
  224.                      
  225.                      Next
  226.          
  227.               Next
  228.              
  229.               mass(7, nomer_ruk) = CDate(Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Cells(i, 2).Value) + CDate(vihodnie)
  230.      Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 10) = Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 10) + mass(6, nomer_ruk) / 20 * Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Cells(i, 3)
  231.      Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 8) = mass(1, nomer_ruk) + "-" + mass(2, nomer_ruk)
  232.      
  233.      Maskom(0) = mass(1, nomer_ruk)
  234.       End If
  235.      
  236.       min_rab = 0
  237.    
  238.    For h = 1 To kol_kom - 1
  239.          For e = 1 To UBound(MasR, 1)
  240.          first_r = 1
  241.             If CDbl(h) - CDbl(e) = krug2 Then   'åñëè ðàâíî íîìåðó êðóãà
  242.            nomd = e
  243.                     For t = 1 To UBound(mass, 2)
  244.                        If MasR(e) = mass(2, t) Then
  245.                           If CDate(mass(7, t)) < Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Cells(i, 2) Then  'åñëè ðàáîòíèê ñâîáîäåí
  246.                               If first_r = 1 Then 'åñëè ïåðâûé ïîäõîäÿùèé
  247.                               min_rab = mass(6, t)
  248.                                first_r = first_r + 1
  249.                                nomer_rab = t
  250.                                End If
  251.                            If mass(6, t) <= min_rab Then 'íàõîäèì ìèíèìàëüíóþ çàðïëàòó
  252.                           min_rab = mass(6, t)
  253.                            nomer_rab = t
  254.                            End If
  255.                           End If
  256.                        End If
  257.                      
  258.                      Next
  259.                      
  260.             End If
  261.            
  262.          Next
  263.          
  264.          
  265.          If mass(2, nomer_rab) <> MasR(nomd) And krug2 = 0 Then
  266.          Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 11) = "Êîìàíäèðîâêà îòìåíåíà èç-çà òîãî, ÷òî íåò ñâîáîäíîãî " + MasR(nomd) + " äëÿ îñíîâíîãî ñîñòàâà"
  267.            Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 8) = ""
  268.            Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 9) = ""
  269.            Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 10) = ""
  270.            For p = 0 To UBound(Maskom, 1)
  271.            
  272.                For o = 1 To UBound(mass, 2) - 1
  273.                  If Maskom(p) = mass(1, o) Then
  274.                  mass(7, o) = 0
  275.                  Exit For
  276.                 End If
  277.                Next o
  278.            
  279.            
  280.            Next p
  281.            
  282.            
  283.        
  284.            
  285.            
  286.          GoTo bbb
  287.          End If
  288.          
  289.         If mass(2, nomer_rab) <> MasR(nomd) And krug2 <> 0 Then
  290.         kol_kom = kol_kom + 1
  291.         GoTo mn
  292.         End If
  293.                    
  294.       mass(7, nomer_rab) = Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Cells(i, 2).Value + vihodnie
  295.      Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 10) = Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 10) + mass(6, nomer_rab) / 20 * Workbooks("Iskhodnye_dannye.xls").Worksheets("Êîìàíäèðîâêè").Cells(i, 3)
  296.      Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 8) = Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 8) + Chr(10) + mass(1, nomer_rab) + "-" + mass(2, nomer_rab)
  297.    
  298.      Maskom(h) = mass(1, nomer_rab)
  299.      
  300.        
  301. mn:
  302.     If krug = UBound(MasR, 1) Then
  303.     krug2 = krug2 + UBound(MasR, 1)
  304.     krug = 0
  305.     End If
  306.     krug = krug + 1
  307.  
  308.      Next
  309.      
  310.               Exit Do
  311.            End If
  312.              
  313.            
  314.    sh = sh + 1
  315.   Loop
  316.  
  317.   'Âûáîð òðàíñïîðòà*************************************************8
  318.  
  319.  
  320.  
  321. Do While Not IsEmpty(Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 1))
  322.  
  323. If InStr(1, Workbooks("Iskhodnye_dannye").Worksheets("Êîìàíäèðîâêè").Cells(i, 6).Value, Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 1).Value, vbTextCompare) > 0 Then
  324.     If Workbooks("Iskhodnye_dannye").Worksheets("Êîìàíäèðîâêè").Cells(i, 5).Value = "Ñòðîèòåëüñòâî" Then
  325.            If Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 3) < 24 And Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 3) > 12 Then
  326.            prv1 = Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 2) * 2 / 100 * 9 * 35 * (Fix(CDbl(Workbooks("Iskhodnye_dannye").Worksheets("Êîìàíäèðîâêè").Cells(i, 4) / 5)) + 1) + Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 10) / Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 3) * 2
  327.            End If
  328.            If Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 3) <= 12 And Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 3) >= 24 / 5 Then
  329.            prv1 = Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 2) * 2 / 100 * 9 * 35 * (Fix(CDbl(Workbooks("Iskhodnye_dannye").Worksheets("Êîìàíäèðîâêè").Cells(i, 4) / 5)) + 1) + Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 10) / Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 3) * 1
  330.            End If
  331.            If Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 3) < 24 / 5 Then
  332.            prv1 = Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 2) * 2 / 100 * 9 * 35 * (Fix(CDbl(Workbooks("Iskhodnye_dannye").Worksheets("Êîìàíäèðîâêè").Cells(i, 4) / 5)) + 1)
  333.            End If
  334.      Else
  335.            If Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 3) < 24 And Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 3) > 12 Then
  336.            prv1 = Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 2) * 2 / 100 * 9 * 35 * (Fix(CDbl(Workbooks("Iskhodnye_dannye").Worksheets("Êîìàíäèðîâêè").Cells(i, 4) / 4)) + 1) + Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 10) / Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 3) * 2
  337.            End If
  338.            If Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 3) <= 12 And Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 3) >= 24 / 5 Then
  339.            prv1 = Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 2) * 2 / 100 * 9 * 35 * (Fix(CDbl(Workbooks("Iskhodnye_dannye").Worksheets("Êîìàíäèðîâêè").Cells(i, 4) / 4)) + 1) + Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 10) / Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 3) * 1
  340.            End If
  341.            If Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 3) < 24 / 5 Then
  342.            prv1 = Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 2) * 2 / 100 * 9 * 35 * (Fix(CDbl(Workbooks("Iskhodnye_dannye").Worksheets("Êîìàíäèðîâêè").Cells(i, 4) / 4)) + 1)
  343.            End If
  344.      End If
  345.            
  346.            If Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 5) < 24 And Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 3) > 12 Then
  347.            prv2 = Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 4) * 2 * Workbooks("Iskhodnye_dannye").Worksheets("Êîìàíäèðîâêè").Cells(i, 4) + Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 10) / Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 3) * 2
  348.            End If
  349.            If Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 5) <= 12 And Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 3) >= 24 / 5 Then
  350.            prv2 = Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 4) * 2 * Workbooks("Iskhodnye_dannye").Worksheets("Êîìàíäèðîâêè").Cells(i, 4) + Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 10) / Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 3) * 1
  351.            End If
  352.            If Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 5) < 24 / 5 Then
  353.            prv2 = Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 4) * 2 * Workbooks("Iskhodnye_dannye").Worksheets("Êîìàíäèðîâêè").Cells(i, 4)
  354.            End If
  355.            If Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 6) <> 0 Then
  356.            prv3 = Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 6) * 2 * Workbooks("Iskhodnye_dannye").Worksheets("Êîìàíäèðîâêè").Cells(i, 4)
  357.            End If
  358.            
  359.             If CDbl(prv3) = 0 And CDbl(prv1) < CDbl(prv2) Then
  360.              Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 9) = "Àâòî"
  361.              Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 10) = Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 10) + prv1
  362.              End If
  363.               If CDbl(prv3) = 0 And CDbl(prv1) >= CDbl(prv2) Then
  364.               Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 9) = "Ïîåçä"
  365.               Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 10) = Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 10) + prv2
  366.               End If
  367.            
  368.            
  369.            
  370.            If CDbl(prv3) <> 0 Then
  371.             If CDbl(prv1) < CDbl(prv2) And CDbl(prv1) < CDbl(prv3) Then
  372.             Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 9) = "Àâòî"
  373.             Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 10) = Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 10) + prv1
  374.             End If
  375.             If CDbl(prv2) < CDbl(prv1) And CDbl(prv2) < CDbl(prv3) Then
  376.             Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 9) = "Ïîåçä"
  377.             Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 10) = Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 10) + prv2
  378.             End If
  379.             If CDbl(prv3) < CDbl(prv1) And CDbl(prv3) < CDbl(prv2) Then
  380.             Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 9) = "Ñàìîëåò"
  381.             Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 10) = Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 10) + prv3
  382.             End If
  383.           End If
  384.            
  385.            
  386.            End If
  387. z = z + 1
  388. Loop
  389.  
  390.      
  391.    
  392.      If Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 9) = "Àâòî" Then
  393.             Do While Not IsEmpty(Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(zz, 1))
  394.                   If InStr(1, Workbooks("Iskhodnye_dannye").Worksheets("Êîìàíäèðîâêè").Cells(i, 6).Value, Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 1).Value, vbTextCompare) > 0 Then
  395.                        If Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(zz, 3) < 24 And Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 3) > 12 Then
  396.                        For pp = 0 To UBound(Maskom, 1)
  397.                           For oo = 1 To UBound(mass, 2) - 1
  398.                            If Maskom(p) = mass(1, oo) Then
  399.                              mass(7, oo) = mass(7, oo) + 2
  400.                           Exit For
  401.                           End If
  402.                        Next oo
  403.                        Next pp
  404.                        End If
  405.                        
  406.                        If Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(zz, 3) < 12 And Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 3) > 24 / 5 Then
  407.                        For ppp = 0 To UBound(Maskom, 1)
  408.                           For ooo = 1 To UBound(mass, 2) - 1
  409.                            If Maskom(p) = mass(1, oo) Then
  410.                              mass(7, oo) = mass(7, oo) + 1
  411.                            Exit For
  412.                            End If
  413.                           Next ooo
  414.                        Next ppp
  415.                        End If
  416.                        End If
  417.                   zz = zz + 1
  418.                    
  419.             Loop
  420.     End If
  421.    
  422.    
  423.     If Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 9) = "Ïîåçä" Then
  424.             Do While Not IsEmpty(Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(zz, 1))
  425.                   If InStr(1, Workbooks("Iskhodnye_dannye").Worksheets("Êîìàíäèðîâêè").Cells(i, 6).Value, Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 1).Value, vbTextCompare) > 0 Then
  426.                        If Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(zz, 5) < 24 And Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 5) > 12 Then
  427.                        For pp = 0 To UBound(Maskom, 1)
  428.                           For oo = 1 To UBound(mass, 2) - 1
  429.                            If Maskom(p) = mass(1, oo) Then
  430.                              mass(7, oo) = mass(7, oo) + 2
  431.                           Exit For
  432.                           End If
  433.                        Next oo
  434.                        Next pp
  435.                        End If
  436.      
  437.                       If Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(zz, 5) < 12 And Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(z, 5) > 24 / 5 Then
  438.                        For pp = 0 To UBound(Maskom, 1)
  439.                           For oo = 1 To UBound(mass, 2) - 1
  440.                            If Maskom(p) = mass(1, oo) Then
  441.                              mass(7, oo) = mass(7, oo) + 1
  442.                          Exit For
  443.                           End If
  444.                        Next oo
  445.                    Next pp
  446.                   End If
  447.                   End If
  448.                   zz = zz + 1
  449.             Loop
  450.     End If
  451.      
  452.      
  453.      
  454.      
  455.      'ïðîâåðêà íà âûãîäíîñòü*****************************************************
  456.     If CDbl(Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 10)) > CDbl(Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 7)) Then
  457.      Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 11) = "Êîìàíäèðîâêà îòìåíåíåíà èç-çà íåâûãîäíîñòè"
  458.      Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 8) = ""
  459.      Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 9) = ""
  460.      Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 10) = ""
  461.                  For ppp = 0 To UBound(Maskom, 1)
  462.                           For ooo = 1 To UBound(mass, 2) - 1
  463.                            If Maskom(p) = mass(1, ooo) Then
  464.                              mass(7, ooo) = 0
  465.                          Exit For
  466.                           End If
  467.                        Next ooo
  468.                    Next ppp
  469.     End If
  470.      
  471.      
  472.      
  473.      
  474.  
  475. bbb:
  476.      
  477.  i = i + 1
  478.  
  479.  
  480.  
  481. Loop
  482.  
  483.  Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Range(Cells(1, 1), Cells(i + 1, 11)).Select
  484.  Selection.VerticalAlignment = xlTop
  485.  '***********************************************************************************************************************
  486.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Range("A1:J1").Copy
  487.  
  488.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets.Add After:=Sheets(Workbooks("Ïëàí êîìàíäèðîâîê " + r).Sheets.Count)
  489.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets(Workbooks("Ïëàí êîìàíäèðîâîê " + r).Sheets.Count).Name = "Ïëàí êîìàíäèðîâîê"
  490.    
  491.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Sheets("Ïëàí êîìàíäèðîâîê").Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
  492.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Range("A1:J1").Copy Workbooks("Ïëàí êîìàíäèðîâîê " + r).Sheets("Ïëàí êîìàíäèðîâîê").Range("A1")
  493.    
  494.     i = 2
  495.     j = 2
  496.     Do While Not IsEmpty(Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 1))
  497.     If Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 11) = "" Then
  498.    
  499.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Rows(i).Copy Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí êîìàíäèðîâîê").Rows(j)
  500.     j = j + 1
  501.     End If
  502.     i = i + 1
  503.     Loop
  504.    
  505.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí êîìàíäèðîâîê").Columns(8).Select
  506.     Selection.Delete
  507.    
  508.    
  509.     '****************************************************************************************
  510.    
  511.    
  512.    
  513.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Range("A1:J1").Copy
  514.  
  515.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets.Add After:=Sheets(Workbooks("Ïëàí êîìàíäèðîâîê " + r).Sheets.Count)
  516.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets(Workbooks("Ïëàí êîìàíäèðîâîê " + r).Sheets.Count).Name = "Ðåíòàáåëüíîñòü êîìàíäèðîâîê"
  517.    
  518.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Sheets("Ðåíòàáåëüíîñòü êîìàíäèðîâîê").Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
  519.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Range("A1:J1").Copy Workbooks("Ïëàí êîìàíäèðîâîê " + r).Sheets("Ðåíòàáåëüíîñòü êîìàíäèðîâîê").Range("A1")
  520.     i = 2
  521.     j = 2
  522.     Do While Not IsEmpty(Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 1))
  523.     If Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(i, 11) = "" Then
  524.    
  525.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Rows(i).Copy Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ðåíòàáåëüíîñòü êîìàíäèðîâîê").Rows(j)
  526.     j = j + 1
  527.     End If
  528.     i = i + 1
  529.     Loop
  530.    
  531.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Sheets("Ðåíòàáåëüíîñòü êîìàíäèðîâîê").Range("F1") = "Ðåíòàáåëüíîñòü"
  532.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Sheets("Ðåíòàáåëüíîñòü êîìàíäèðîâîê").Range("F2").Select
  533.     ActiveCell.FormulaR1C1 = "=RC[1]/RC[4]"
  534.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Sheets("Ðåíòàáåëüíîñòü êîìàíäèðîâîê").Range("F2").Select
  535.     Selection.AutoFill Destination:=Range("F2:F300"), Type:=xlFillDefault
  536.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ðåíòàáåëüíîñòü êîìàíäèðîâîê").Columns(3).Select
  537.     Selection.Delete
  538.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ðåíòàáåëüíîñòü êîìàíäèðîâîê").Columns(3).Select
  539.     Selection.Delete
  540.    
  541.  
  542.     '**********************************************************************************************************************
  543.    Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets.Add After:=Sheets(Workbooks("Ïëàí êîìàíäèðîâîê " + r).Sheets.Count)
  544.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets(Workbooks("Ïëàí êîìàíäèðîâîê " + r).Sheets.Count).Name = "Ïëàí çàíÿòîñòè"
  545.     For i = 2 To UBound(mass, 2)
  546.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí çàíÿòîñòè").Cells(i - 1, 1) = mass(1, i)
  547.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí çàíÿòîñòè").Cells(i - 1, 1) = Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí çàíÿòîñòè").Cells(i - 1, 1) + "-" + mass(2, i)
  548.    
  549.    
  550.    
  551.    
  552.     x = 2
  553.     j = 2
  554.         Do While Not IsEmpty(Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(j, 1))
  555.          If InStr(1, Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(j, 8).Value, Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí çàíÿòîñòè").Cells(i - 1, 1).Value, vbTextCompare) > 0 Then
  556.           Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí çàíÿòîñòè").Cells(i - 1, x).Value = CStr(Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(j, 2).Value) + "                   " + CStr(Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(j, 1).Value) + "                                     " + CStr(Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Cells(j, 3).Value)
  557.          x = x + 1
  558.          End If
  559.          j = j + 1
  560.         Loop
  561.    
  562.     Next
  563.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí çàíÿòîñòè").Cells(70, 2) = "Êîëè÷åñòâî çàäåéñòâîâàííûõ"
  564.       Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí çàíÿòîñòè").Cells(70, 1) = "Äîëæíîñòü"
  565.      Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí çàíÿòîñòè").Cells(71, 1) = Workbooks("Tseli").Worksheets(1).Cells(2, 1)
  566.       Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí çàíÿòîñòè").Cells(72, 1) = Workbooks("Tseli").Worksheets(1).Cells(3, 1)
  567.       Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí çàíÿòîñòè").Cells(73, 1) = Workbooks("Tseli").Worksheets(1).Cells(4, 1)
  568.       Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí çàíÿòîñòè").Cells(74, 1) = Workbooks("Tseli").Worksheets(1).Cells(5, 1)
  569.       Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí çàíÿòîñòè").Cells(75, 1) = Workbooks("Tseli").Worksheets(1).Cells(6, 1)
  570.       Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí çàíÿòîñòè").Cells(76, 1) = Workbooks("Tseli").Worksheets(1).Cells(7, 1)
  571.       Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí çàíÿòîñòè").Cells(77, 1) = Workbooks("Tseli").Worksheets(1).Cells(8, 1)
  572.       Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí çàíÿòîñòè").Cells(78, 1) = Workbooks("Tseli").Worksheets(1).Cells(9, 1)
  573.          
  574.          
  575.        i = 71
  576.        
  577.       Do While Not IsEmpty(Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí çàíÿòîñòè").Cells(i, 1))
  578.      j = 1
  579.      Do While Not IsEmpty(Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí çàíÿòîñòè").Cells(j, 1))
  580.      y = 2
  581.      
  582.      If InStr(1, Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí çàíÿòîñòè").Cells(j, 1).Value, Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí çàíÿòîñòè").Cells(i, 1).Value, vbTextCompare) > 0 Then
  583.      Do While Not IsEmpty(Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí çàíÿòîñòè").Cells(j, y))
  584.      Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí çàíÿòîñòè").Cells(i, 2) = Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí çàíÿòîñòè").Cells(i, 2) + 1
  585.      y = y + 1
  586.      Loop
  587.      End If
  588.      j = j + 1
  589.      
  590.      Loop
  591.      
  592.      i = i + 1
  593.      Loop
  594.      Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí çàíÿòîñòè").Range("A71:B78").Select
  595.    
  596.    
  597.     ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  598.    Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets.Add After:=Sheets(Workbooks("Ïëàí êîìàíäèðîâîê " + r).Sheets.Count)
  599.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets(Workbooks("Ïëàí êîìàíäèðîâîê " + r).Sheets.Count).Name = "Âûãîäíîñòü îáëàñòåé"
  600.    
  601.     i = 1
  602.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Âûãîäíîñòü îáëàñòåé").Cells(i, 2) = "Îáùàÿ âûãîäà"
  603.    
  604.     Do While Not IsEmpty(Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(i, 1))
  605.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Âûãîäíîñòü îáëàñòåé").Cells(i, 1) = Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(i, 1)
  606.     j = 2
  607.     If i >= 2 Then
  608.      Do While Not IsEmpty(Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí êîìàíäèðîâîê").Cells(j, 1))
  609.      If InStr(1, Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí êîìàíäèðîâîê").Cells(j, 6).Value, Workbooks("Iskhodnye_dannye").Worksheets("Ñïèñîê îáëàñòåé").Cells(i, 1).Value, vbTextCompare) > 0 Then
  610.      Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Âûãîäíîñòü îáëàñòåé").Cells(i, 2) = Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Âûãîäíîñòü îáëàñòåé").Cells(i, 2) + Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí êîìàíäèðîâîê").Cells(j, 7) - Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ïëàí êîìàíäèðîâîê").Cells(j, 9)
  611.      End If
  612.      j = j + 1
  613.      Loop
  614.      End If
  615.     i = i + 1
  616.     Loop
  617.    
  618.      
  619.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Save
  620.    
  621.     Workbooks("Ïëàí êîìàíäèðîâîê " + r).Worksheets("Ëèñò1").Activate
  622.    
  623.     Application.ScreenUpdating = True
  624.     UserForm1.Show
  625. '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  626. '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  627. 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!