Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Sub CreateRooms()
- Dim nb As Integer
- nb = 5
- 'Room
- Set sht = Sheets("Room")
- sht.Cells(16 + nb, 5).Formula = "=Langage!A15"
- sht.Cells(16 + nb, 6).Formula = "=SUM(F16, F" & 15 + nb & ")"
- sht.Cells(16 + nb, 7).Formula = "=SUM(G16, G" & 15 + nb & ")"
- sht.Cells(16 + nb, 8).Formula = "=SUM(H16, H" & 15 + nb & ")"
- sht.Cells(16 + nb, 9).Formula = "=SUM(I16, I" & 15 + nb & ")"
- 'Ventil
- Set sht = Sheets("Ventil")
- sht.Cells(6, 6 + nb).Formula = "=Langage!A15"
- 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) & "))"
- sht.Cells(9, 6).Formula = "=IF(Room!G8=" & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",Room!G8)"
- sht.Cells(9, 6).HorizontalAlignment = xlCenter
- Range("F9:" & Cells(9, 5 + nb).Address(RowAbsolute:=False, ColumnAbsolute:=False)).Merge
- sht.Cells(15, 6).Formula = "=IF(F14=" & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",IF(F14=2,0.75,1))"
- sht.Cells(15, 6).HorizontalAlignment = xlCenter
- Range("F15:" & Cells(15, 5 + nb).Address(RowAbsolute:=False, ColumnAbsolute:=False)).Merge
- sht.Cells(20, 6).Value = "6"
- sht.Cells(20, 6).HorizontalAlignment = xlCenter
- sht.Cells(20, 6).Interior.Color = RGB(255, 212, 176)
- Range("F20:" & Cells(20, 5 + nb).Address(RowAbsolute:=False, ColumnAbsolute:=False)).Merge
- sht.Cells(27, 6).Value = "30"
- sht.Cells(27, 6 + nb).Formula = "=SUM(F27:" & Cells(27, 5 + nb).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
- sht.Cells(28, 6).Value = "60"
- sht.Cells(28, 6 + nb).Formula = "=SUM(F28:" & Cells(28, 5 + nb).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
- sht.Cells(29, 6).Value = "22.0"
- sht.Cells(34, 6).Formula = "=MAX(0," & Cells(27, 6 + nb).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "-" & Cells(28, 6 + nb).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
- sht.Cells(34, 6).HorizontalAlignment = xlCenter
- Range("F34:" & Cells(34, 5 + nb).Address(RowAbsolute:=False, ColumnAbsolute:=False)).Merge
- sht.Cells(42, 6 + nb).Formula = "=IF(F42=" & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",SOMME(F42:" _
- & Cells(42, 5 + nb).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "))"
- For i = 1 To nb
- 'Room
- Set sht = Sheets("Room")
- sht.Cells(15 + i, 2).Value = i
- sht.Cells(15 + i, 3).Interior.Color = RGB(255, 212, 176)
- sht.Cells(15 + i, 5).Interior.Color = RGB(255, 212, 176)
- sht.Cells(15 + i, 6).Interior.Color = RGB(255, 212, 176)
- sht.Cells(15 + i, 7).Interior.Color = RGB(255, 212, 176)
- sht.Cells(15 + i, 8).Interior.Color = RGB(255, 212, 176)
- sht.Cells(15 + i, 9).Interior.Color = RGB(255, 212, 176)
- 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 & ")"
- 'Ventil
- Set sht = Sheets("Ventil")
- sht.Cells(6, 5 + i).Formula = "=IF(Room!C" & 15 + i & "=" & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",Room!C" & 15 + i & ")"
- 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 & ")"
- 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 & ")"
- 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)"
- 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)"
- 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)"
- sht.Cells(23, 5 + i).Formula = "=IF(OR(" & Cells(8, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
- & Chr(34) & Chr(34) & ",$F20=" & Chr(34) & Chr(34) & ")," & Chr(34) & Chr(34) _
- & ",2*" & Cells(8, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "*$F20*" _
- & Cells(21, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "* " & Cells(22, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
- sht.Cells(27, 5 + i).Interior.Color = RGB(255, 212, 176)
- sht.Cells(28, 5 + i).Interior.Color = RGB(255, 212, 176)
- sht.Cells(29, 5 + i).Interior.Color = RGB(255, 212, 176)
- sht.Cells(30, 5 + i).Formula = "=IF(OR(" & Cells(29, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "=" & Chr(34) & Chr(34) & "," & Cells(10, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "=" & Chr(34) & Chr(34) & ")," & Chr(34) & Chr(34) _
- & ",(" & Cells(10, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "-" & Cells(29, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & ")/(" & Cells(10, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "-$F9))"
- sht.Cells(31, 5 + i).Interior.Color = RGB(255, 212, 176)
- sht.Cells(32, 5 + i).Interior.Color = RGB(255, 212, 176)
- sht.Cells(33, 5 + i).Formula = "=IF(OR(" & Cells(32, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "=" & Chr(34) & Chr(34) & "," & Cells(10, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "=" & Chr(34) & Chr(34) & ")," & Chr(34) & Chr(34) _
- & ",(" & Cells(10, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "-" & Cells(32, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & ")/(" & Cells(10, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "-$F9))"
- sht.Cells(35, 5 + i).Formula = "=IF(" & Cells(8, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
- & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",$F34*" & Cells(8, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "/$" & Cells(32, 6 + nb).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
- 'Used the formulas that was before on the right, theses are now below
- sht.Cells(39, 5 + i).Formula = "=IF(" & Cells(16, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "=" & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",IF(" _
- & Cells(45, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ">" _
- & Cells(53, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "," _
- & Cells(52, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "+" _
- & Cells(45, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "-" _
- & Cells(53, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "," _
- & Cells(52, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "))"
- sht.Cells(40, 5 + i).Formula = "=IF(" & Cells(39, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
- & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",0.34*" & Cells(39, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
- sht.Cells(41, 5 + i).Formula = "=IF(" & Cells(10, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
- & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & "," & Cells(10, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "-$F9)"
- sht.Cells(42, 5 + i).Formula = "=IF(" & Cells(41, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
- & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & "," & Cells(40, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "*" & Cells(41, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
- 'Hidden formulas left side
- 'Was on <AD>16
- sht.Cells(45, 5 + i).Formula = "=IF(" & Cells(16, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
- & Chr(34) & Chr(34) & ",0," & Cells(16, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
- 'Was on <AD>23
- sht.Cells(46, 5 + i).Formula = "=IF(" & Cells(23, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
- & Chr(34) & Chr(34) & ",0," & Cells(23, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
- 'Was on <AD>28
- sht.Cells(47, 5 + i).Formula = "=IF(" & Cells(28, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
- & Chr(34) & Chr(34) & ",0," & Cells(28, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
- 'Was on <AD>30
- sht.Cells(48, 5 + i).Formula = "=IF(" & Cells(30, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
- & Chr(34) & Chr(34) & ",1," & Cells(30, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
- 'Was on <AD>31
- sht.Cells(49, 5 + i).Formula = "=IF(" & Cells(31, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
- & Chr(34) & Chr(34) & ",0," & Cells(31, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
- 'Was on <AD>33
- sht.Cells(50, 5 + i).Formula = "=IF(" & Cells(33, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
- & Chr(34) & Chr(34) & ",1," & Cells(33, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
- 'Was on <AD>35
- sht.Cells(51, 5 + i).Formula = "=IF(" & Cells(35, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
- & Chr(34) & Chr(34) & ",0," & Cells(35, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
- 'Was on <AD>39
- sht.Cells(52, 5 + i).Formula = "=" & Cells(46, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "+" & Cells(47, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "*" & Cells(48, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "+" & Cells(49, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "*" & Cells(50, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "+" & Cells(51, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False)
- 'Was on <AD>40
- sht.Cells(53, 5 + i).Formula = "=" & Cells(46, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "+" & Cells(47, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "+" & Cells(49, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "+" & Cells(51, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False)
- 'Was on AU43, can be writed hardcoded in the sheet itself but it's propertly to keep all this formula stuff here
- sht.Cells(54, 6).Formula = "=IF(F41=" _
- & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & "," _
- & "F41)"
- 'Hidden formulas right side
- 'Was on <AZ>16
- sht.Cells(58, 5 + i).Formula = "=IF(" & Cells(45, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
- & Chr(34) & Chr(34) & ",0," & Cells(45, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
- 'Was on <AZ>23
- sht.Cells(59, 5 + i).Formula = "=IF(" & Cells(46, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
- & Chr(34) & Chr(34) & ",0," & Cells(46, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "/2)"
- 'Was on <AZ>28
- sht.Cells(60, 5 + i).Formula = "=IF(" & Cells(47, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
- & Chr(34) & Chr(34) & ",0," & Cells(47, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
- 'Was on <AZ>30
- sht.Cells(61, 5 + i).Formula = "=IF(" & Cells(48, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
- & Chr(34) & Chr(34) & ",1," & Cells(48, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
- 'Was on <AZ>31
- sht.Cells(62, 5 + i).Formula = "=IF(" & Cells(49, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
- & Chr(34) & Chr(34) & ",0," & Cells(49, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
- 'Was on <AZ>33
- sht.Cells(63, 5 + i).Formula = "=IF(" & Cells(50, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
- & Chr(34) & Chr(34) & ",1," & Cells(50, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
- 'Was on <AZ>35
- sht.Cells(64, 5 + i).Formula = "=" & Cells(51, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False)
- 'Was on <AZ>39
- sht.Cells(65, 5 + i).Formula = "=" & Cells(59, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "+" & Cells(60, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "*" & Cells(61, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "+" & Cells(62, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "*" & Cells(63, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "+" & Cells(64, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False)
- 'Was on <AZ>40
- sht.Cells(66, 5 + i).Formula = "=" & Cells(59, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "+" & Cells(60, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "+" & Cells(62, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "+" & Cells(64, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False)
- 'Was on <AZ>41
- sht.Cells(67, 5 + i).Formula = "=IF(" & Cells(16, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "=" & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",SI(" _
- & Cells(58, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & ">" & Cells(66, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "," & Cells(65, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "+" & Cells(58, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "-" & Cells(66, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "," & Cells(65, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "))"
- 'Was on <AZ>42
- sht.Cells(68, 5 + i).Formula = "=IF(" & Cells(67, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "=" & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",0.34*" _
- & Cells(67, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
- 'Was on <AZ>43
- sht.Cells(69, 5 + i).Formula = "=IF(" & Cells(41, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "=" _
- & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) _
- & "," & Cells(41, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
- 'Was on <AZ>44
- sht.Cells(70, 5 + i).Formula = "=IF(" & Cells(68, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "=" & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & "," _
- & Cells(68, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "*" _
- & Cells(69, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
- 'Was on <AZ>46
- sht.Cells(71, 5 + i).Formula = "=IF(" & Cells(70, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) _
- & "<0,0," & Cells(70, 5 + i).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
- 'Was on AY46 same as AU43
- sht.Cells(72, 6).Formula = "=SUM(F71," & Cells(71, 5 + nb).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
- 'Create Templates Heat load must to be heat up ?
- 'Sheets("T").Copy before:=Sheets("Heat load")
- 'Set sht = Sheets(Sheets("Heat load").Index - 1)
- 'sht.Name = "T" & i
- 'sht.Visible = True
- 'Heat up
- 'Heat load
- Next i
- End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement