Advertisement
Guest User

Untitled

a guest
Apr 16th, 2017
80
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
VBScript 18.29 KB | None | 0 0
  1. Sub CreateRooms()
  2.     Dim nb As Integer
  3.     nb = 5
  4.     'Room
  5.    Set sht = Sheets("Room")
  6.     sht.Cells(16 + nb, 5).Formula = "=Langage!A15"
  7.     sht.Cells(16 + nb, 6).Formula = "=SUM(F16, F" & 15 + nb & ")"
  8.     sht.Cells(16 + nb, 7).Formula = "=SUM(G16, G" & 15 + nb & ")"
  9.     sht.Cells(16 + nb, 8).Formula = "=SUM(H16, H" & 15 + nb & ")"
  10.     sht.Cells(16 + nb, 9).Formula = "=SUM(I16, I" & 15 + nb & ")"
  11.    
  12.     'Ventil
  13.    Set sht = Sheets("Ventil")
  14.     sht.Cells(6, 6 + nb).Formula = "=Langage!A15"
  15.     sht.Cells(8, 6 + nb).Formula = "=IF(F8=" & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",SOMME(F8:" & Cells(8, 5 + nb).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "))"
  16.     sht.Cells(9, 6).Formula = "=IF(Room!G8=" & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",Room!G8)"
  17.     sht.Cells(9, 6).HorizontalAlignment = xlCenter
  18.     Range("F9:" & Cells(9, 5 + nb).Address(RowAbsolute:=False, ColumnAbsolute:=False)).Merge
  19.     sht.Cells(15, 6).Formula = "=IF(F14=" & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",IF(F14=2,0.75,1))"
  20.     sht.Cells(15, 6).HorizontalAlignment = xlCenter
  21.     Range("F15:" & Cells(15, 5 + nb).Address(RowAbsolute:=False, ColumnAbsolute:=False)).Merge
  22.     sht.Cells(20, 6).Value = "6"
  23.     sht.Cells(20, 6).HorizontalAlignment = xlCenter
  24.     sht.Cells(20, 6).Interior.Color = RGB(255, 212, 176)
  25.     Range("F20:" & Cells(20, 5 + nb).Address(RowAbsolute:=False, ColumnAbsolute:=False)).Merge
  26.     sht.Cells(27, 6).Value = "30"
  27.     sht.Cells(27, 6 + nb).Formula = "=SUM(F27:" & Cells(27, 5 + nb).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  28.     sht.Cells(28, 6).Value = "60"
  29.     sht.Cells(28, 6 + nb).Formula = "=SUM(F28:" & Cells(28, 5 + nb).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  30.     sht.Cells(29, 6).Value = "22.0"
  31.     sht.Cells(34, 6).Formula = "=MAX(0," & Cells(27, 6 + nb).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  32.                                 & "-" & Cells(28, 6 + nb).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  33.     sht.Cells(34, 6).HorizontalAlignment = xlCenter
  34.     Range("F34:" & Cells(34, 5 + nb).Address(RowAbsolute:=False, ColumnAbsolute:=False)).Merge
  35.     sht.Cells(42, 6 + nb).Formula = "=IF(F42=" & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",SOMME(F42:" _
  36.                                 & Cells(42, 5 + nb).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "))"
  37.    
  38.    
  39.     For i = 1 To nb
  40.         'Room
  41.        Set sht = Sheets("Room")
  42.         sht.Cells(15 + i, 2).Value = i
  43.         sht.Cells(15 + i, 3).Interior.Color = RGB(255, 212, 176)
  44.         sht.Cells(15 + i, 5).Interior.Color = RGB(255, 212, 176)
  45.         sht.Cells(15 + i, 6).Interior.Color = RGB(255, 212, 176)
  46.         sht.Cells(15 + i, 7).Interior.Color = RGB(255, 212, 176)
  47.         sht.Cells(15 + i, 8).Interior.Color = RGB(255, 212, 176)
  48.         sht.Cells(15 + i, 9).Interior.Color = RGB(255, 212, 176)
  49.         sht.Cells(15 + i, 18).Formula = "=IF(OR(I" & 15 + i & "=" & Chr(34) & Chr(34) & ",I" & 15 + i & "=0)," & Chr(34) & Chr(34) & ",H" & 15 + i & "*2/I" & 15 + i & ")"
  50.        
  51.         'Ventil
  52.        Set sht = Sheets("Ventil")
  53.         sht.Cells(6, 5 + i).Formula = "=IF(Room!C" & 15 + i & "=" & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",Room!C" & 15 + i & ")"
  54.         sht.Cells(8, 5 + i).Formula = "=IF(" & Cells(6, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",Room!G" & 15 + i & ")"
  55.         sht.Cells(10, 5 + i).Formula = "=IF(" & Cells(6, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",Room!E" & 15 + i & ")"
  56.         sht.Cells(16, 5 + i).Formula = "=IF(OR(" & Cells(8, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" & Chr(34) & Chr(34) & ",$F15=" & Chr(34) & Chr(34) & ")," & Chr(34) & Chr(34) & "," & Cells(8, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "*$F15)"
  57.         sht.Cells(21, 5 + i).Formula = "=IF(" & Cells(6, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",0.1)"
  58.         sht.Cells(22, 5 + i).Formula = "=IF(" & Cells(6, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",1)"
  59.         sht.Cells(23, 5 + i).Formula = "=IF(OR(" & Cells(8, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
  60.                                     & Chr(34) & Chr(34) & ",$F20=" & Chr(34) & Chr(34) & ")," & Chr(34) & Chr(34) _
  61.                                     & ",2*" & Cells(8, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  62.                                     & "*$F20*" _
  63.                                     & Cells(21, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "* " & Cells(22, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  64.         sht.Cells(27, 5 + i).Interior.Color = RGB(255, 212, 176)
  65.         sht.Cells(28, 5 + i).Interior.Color = RGB(255, 212, 176)
  66.         sht.Cells(29, 5 + i).Interior.Color = RGB(255, 212, 176)
  67.         sht.Cells(30, 5 + i).Formula = "=IF(OR(" & Cells(29, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  68.                                     & "=" & Chr(34) & Chr(34) & "," & Cells(10, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  69.                                     & "=" & Chr(34) & Chr(34) & ")," & Chr(34) & Chr(34) _
  70.                                     & ",(" & Cells(10, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  71.                                     & "-" & Cells(29, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  72.                                     & ")/(" & Cells(10, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  73.                                     & "-$F9))"
  74.         sht.Cells(31, 5 + i).Interior.Color = RGB(255, 212, 176)
  75.         sht.Cells(32, 5 + i).Interior.Color = RGB(255, 212, 176)
  76.         sht.Cells(33, 5 + i).Formula = "=IF(OR(" & Cells(32, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  77.                                     & "=" & Chr(34) & Chr(34) & "," & Cells(10, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  78.                                     & "=" & Chr(34) & Chr(34) & ")," & Chr(34) & Chr(34) _
  79.                                     & ",(" & Cells(10, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  80.                                     & "-" & Cells(32, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  81.                                     & ")/(" & Cells(10, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  82.                                     & "-$F9))"
  83.         sht.Cells(35, 5 + i).Formula = "=IF(" & Cells(8, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
  84.                                     & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",$F34*" & Cells(8, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  85.                                     & "/$" & Cells(32, 6 + nb).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  86.         'Used the formulas that was before on the right, theses are now below
  87.        sht.Cells(39, 5 + i).Formula = "=IF(" & Cells(16, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  88.                                     & "=" & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",IF(" _
  89.                                     & Cells(45, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ">" _
  90.                                     & Cells(53, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "," _
  91.                                     & Cells(52, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "+" _
  92.                                     & Cells(45, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "-" _
  93.                                     & Cells(53, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "," _
  94.                                     & Cells(52, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "))"
  95.         sht.Cells(40, 5 + i).Formula = "=IF(" & Cells(39, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
  96.                                     & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",0.34*" & Cells(39, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  97.         sht.Cells(41, 5 + i).Formula = "=IF(" & Cells(10, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
  98.                                     & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & "," & Cells(10, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  99.                                     & "-$F9)"
  100.         sht.Cells(42, 5 + i).Formula = "=IF(" & Cells(41, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
  101.                                     & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & "," & Cells(40, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  102.                                     & "*" & Cells(41, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  103.         'Hidden formulas left side
  104.        'Was on <AD>16
  105.        sht.Cells(45, 5 + i).Formula = "=IF(" & Cells(16, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
  106.                                     & Chr(34) & Chr(34) & ",0," & Cells(16, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  107.         'Was on <AD>23
  108.        sht.Cells(46, 5 + i).Formula = "=IF(" & Cells(23, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
  109.                                     & Chr(34) & Chr(34) & ",0," & Cells(23, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  110.         'Was on <AD>28
  111.        sht.Cells(47, 5 + i).Formula = "=IF(" & Cells(28, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
  112.                                     & Chr(34) & Chr(34) & ",0," & Cells(28, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  113.         'Was on <AD>30
  114.        sht.Cells(48, 5 + i).Formula = "=IF(" & Cells(30, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
  115.                                     & Chr(34) & Chr(34) & ",1," & Cells(30, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  116.         'Was on <AD>31
  117.        sht.Cells(49, 5 + i).Formula = "=IF(" & Cells(31, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
  118.                                     & Chr(34) & Chr(34) & ",0," & Cells(31, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  119.         'Was on <AD>33
  120.        sht.Cells(50, 5 + i).Formula = "=IF(" & Cells(33, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
  121.                                     & Chr(34) & Chr(34) & ",1," & Cells(33, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  122.         'Was on <AD>35
  123.        sht.Cells(51, 5 + i).Formula = "=IF(" & Cells(35, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
  124.                                     & Chr(34) & Chr(34) & ",0," & Cells(35, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  125.         'Was on <AD>39
  126.        sht.Cells(52, 5 + i).Formula = "=" & Cells(46, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  127.                                     & "+" & Cells(47, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  128.                                     & "*" & Cells(48, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  129.                                     & "+" & Cells(49, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  130.                                     & "*" & Cells(50, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  131.                                     & "+" & Cells(51, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False)
  132.         'Was on <AD>40
  133.        sht.Cells(53, 5 + i).Formula = "=" & Cells(46, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  134.                                     & "+" & Cells(47, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  135.                                     & "+" & Cells(49, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  136.                                     & "+" & Cells(51, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False)
  137.         'Was on AU43, can be writed hardcoded in the sheet itself but it's propertly to keep all this formula stuff here
  138.        sht.Cells(54, 6).Formula = "=IF(F41=" _
  139.                                     & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & "," _
  140.                                     & "F41)"
  141.         'Hidden formulas right side
  142.        'Was on <AZ>16
  143.        sht.Cells(58, 5 + i).Formula = "=IF(" & Cells(45, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
  144.                                     & Chr(34) & Chr(34) & ",0," & Cells(45, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  145.         'Was on <AZ>23
  146.        sht.Cells(59, 5 + i).Formula = "=IF(" & Cells(46, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
  147.                                     & Chr(34) & Chr(34) & ",0," & Cells(46, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "/2)"
  148.         'Was on <AZ>28
  149.        sht.Cells(60, 5 + i).Formula = "=IF(" & Cells(47, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
  150.                                     & Chr(34) & Chr(34) & ",0," & Cells(47, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  151.         'Was on <AZ>30
  152.        sht.Cells(61, 5 + i).Formula = "=IF(" & Cells(48, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
  153.                                     & Chr(34) & Chr(34) & ",1," & Cells(48, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  154.         'Was on <AZ>31
  155.        sht.Cells(62, 5 + i).Formula = "=IF(" & Cells(49, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
  156.                                     & Chr(34) & Chr(34) & ",0," & Cells(49, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  157.         'Was on <AZ>33
  158.        sht.Cells(63, 5 + i).Formula = "=IF(" & Cells(50, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
  159.                                     & Chr(34) & Chr(34) & ",1," & Cells(50, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  160.         'Was on <AZ>35
  161.        sht.Cells(64, 5 + i).Formula = "=" & Cells(51, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False)
  162.         'Was on <AZ>39
  163.        sht.Cells(65, 5 + i).Formula = "=" & Cells(59, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  164.                                     & "+" & Cells(60, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  165.                                     & "*" & Cells(61, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  166.                                     & "+" & Cells(62, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  167.                                     & "*" & Cells(63, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  168.                                     & "+" & Cells(64, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False)
  169.         'Was on <AZ>40
  170.        sht.Cells(66, 5 + i).Formula = "=" & Cells(59, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  171.                                     & "+" & Cells(60, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  172.                                     & "+" & Cells(62, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  173.                                     & "+" & Cells(64, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False)
  174.         'Was on <AZ>41
  175.        sht.Cells(67, 5 + i).Formula = "=IF(" & Cells(16, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  176.                                     & "=" & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",SI(" _
  177.                                     & Cells(58, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  178.                                     & ">" & Cells(66, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  179.                                     & "," & Cells(65, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  180.                                     & "+" & Cells(58, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  181.                                     & "-" & Cells(66, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  182.                                     & "," & Cells(65, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "))"
  183.         'Was on <AZ>42
  184.        sht.Cells(68, 5 + i).Formula = "=IF(" & Cells(67, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  185.                                     & "=" & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",0.34*" _
  186.                                     & Cells(67, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  187.         'Was on <AZ>43
  188.        sht.Cells(69, 5 + i).Formula = "=IF(" & Cells(41, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
  189.                                     & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) _
  190.                                     & "," & Cells(41, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  191.         'Was on <AZ>44
  192.        sht.Cells(70, 5 + i).Formula = "=IF(" & Cells(68, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  193.                                     & "=" & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & "," _
  194.                                     & Cells(68, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "*" _
  195.                                     & Cells(69, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  196.         'Was on <AZ>46
  197.        sht.Cells(71, 5 + i).Formula = "=IF(" & Cells(70, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
  198.                                     & "<0,0," & Cells(70, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  199.         'Was on AY46 same as AU43
  200.        sht.Cells(72, 6).Formula = "=SUM(F71," & Cells(71, 5 + nb).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  201.        
  202.         'Create Templates Heat load must to be heat up ?
  203.        'Sheets("T").Copy before:=Sheets("Heat load")
  204.        'Set sht = Sheets(Sheets("Heat load").Index - 1)
  205.        'sht.Name = "T" & i
  206.        'sht.Visible = True
  207.        
  208.         'Heat up
  209.        'Heat load
  210.    Next i
  211. End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement