• No results found

Sub OPT() j = 1 k = 1 m = 1 n = 1 o = 1 g = 1 h = 1

N/A
N/A
Protected

Academic year: 2021

Share "Sub OPT() j = 1 k = 1 m = 1 n = 1 o = 1 g = 1 h = 1"

Copied!
5
0
0

Loading.... (view fulltext now)

Full text

(1)

Sub OPT()

Dim minpc As Integer Dim max(100) As Double

Dim row_max, fday, lday As Integer Dim aux, aux2 As String

Dim frow(1000) As Double Dim lrow(1000) As Double Dim max_it(1000) As Double Dim sumpc(1000) As Double Dim day_row As Double

Dim fecha, date_ini, date_fin As Date Dim inicio, final, fin As Integer Dim pc_ini, pc_opt As Integer Dim sum_ini, falso As Double

Dim sum_opt, starts_ini, starts_opt As Double Dim pc1(1000), pcfin(1000) As Double

Dim vacio As String

j = 1 k = 1 m = 1 n = 1 o = 1 g = 1 h = 1

'Almaceno la condición de horas PC Sheets("eficiencias").Select

minpc = ActiveSheet.Cells(32, 8).Value Sheets("opt").Select

'Fechas de inicio y fin

date_ini = Cells(3, 24).Value date_fin = Cells(3, 25).Value

'####Almacenar el rango a optimizar#### 'vector para buscar las fechas

Range("C4").Select

Selection.End(xlDown).Select fin = ActiveCell.Row

For t = 3 To fin

Cells(t, 3).Select

If (Cells(t, 3) = date_ini - 1) Then inicio = Cells(t + 1, 3).Row End If

If (Cells(t, 3) = date_fin + 1) Then final = Cells(t - 1, 3).Row Exit For

End If Next t

'Borro las optimizaciones anteriores

Range(Cells(4, 19), Cells(fin, 22)).Select Selection.ClearContents

'Almaceno el rango de funcionamiento Range("M4").Select

For i = inicio To final Cells(i, 13).Activate aux = Cells(i, 13).Value aux2 = Cells(i + 1, 13).Value

If ((aux = "STOP") * (aux2 = "PC")) Then frow(j) = Cells(i + 1, 13).Row

j = j + 1 End If

If ((aux = "PC") * (aux2 = "STOP")) Then lrow(k) = Cells(i, 13).Row

(2)

End If Next i

'Copio el régimen inicial para compararlo con los resultados Range(Cells(inicio, 15), Cells(final + 1, 15)).Select

Selection.Copy

Cells(inicio, 19).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False

Application.CutCopyMode = False

Range(Cells(inicio, 17), Cells(final + 1, 17)).Select Selection.Copy

Cells(inicio, 21).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False

Application.CutCopyMode = False

'###Almaceno las filas de los tramos correspondientes al funcionamiento For f = inicio To final + 1

Cells(f, 19).Select

aux3 = Cells(f - 1, 19).Value aux4 = Cells(f, 19).Value

If ((aux3 = "START2") * (aux4 = "PC")) Then pc1(g) = Cells(f, 19).Row

g = g + 1 End If

If ((aux3 = "PC") * (aux4 = "DS")) Then pcfin(h) = Cells(f - 1, 19).Row h = h + 1 End If Next f If (Cells(inicio, 20) = "DS") Then Cells(inicio, 22) = 0 End If h = 1 If (Cells(inicio, 19) = "DS") Then Cells(inicio, 21) = 0 End If

'Busco el máximo de cada tramo k = 1 For j = 1 To 1000 If (frow(j) = 0) Then Exit For End If h = j

'Almacenamiento del máximo del primer caso For l = (frow(j) - 2) To (lrow(k) + 1) max(m) = max(m) + Cells(l, 17) sumpc(n) = sumpc(n) + Cells(l, 18) Next l

m = m + 1 k = k + 1

'Almacenamiento de la fila del primer máximo row_max = frow(j)

'###Condición si sumpc(n) es menor que minpc para la planta parar y copiar el régimen If (sumpc(n) < minpc) Then

falso = 1

Range(Cells(row_max, 13), Cells(pcfin(h), 13)) = "STOP" Cells(row_max, 3).Select

day_row = ActiveCell.Row fecha = ActiveCell.Value For p = 1 To 20

If (Cells(day_row - p, 3) = fecha) Then fday = Cells(day_row - p, 3).Row End If

If (Cells(day_row + p, 3) = fecha) Then lday = Cells(day_row + p, 3).Row End If

(3)

Next p

'Selecciona las celdas del régimen y las pega Range(Cells(fday, 15), Cells(lday + 1, 15)).Select Selection.Copy

Cells(fday, 20).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False

Application.CutCopyMode = False

'Selecciona el margen recibido optimizado y lo pega Range(Cells(fday, 17), Cells(lday + 1, 17)).Select Selection.Copy

Cells(fday, 22).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False If (Cells(inicio, 20) = "DS") Then Cells(inicio, 22) = 0 End If If (Cells(inicio, 19) = "DS") Then Cells(inicio, 20) = "STOP" End If sumpc(n) = 0 End If

'Iteración para buscar el máximo

n = 1

Do While (sumpc(n) > minpc) If (falso = 1) Then

Exit Do End If

Cells(frow(j) + n - 1, 13) = "STOP" sumpc(n) = 0

For o = (frow(j) - 2) + n To (lrow(k - 1) + 1) max_it(n) = max_it(n) + Cells(o, 17)

sumpc(n) = sumpc(n) + Cells(o, 18) Next o

If (max_it(n) > max(m - 1)) Then max(m - 1) = max_it(n)

falso = 2

'Búsqueda del máximo coincidente con pc If (Cells(frow(j) + n, 13) = "PC") Then row_max = frow(j) + n

End If

If (Cells(frow(j) + n, 13) = "STOP") Then For p = 1 To 6 Cells(frow(j) + n + p, 13).Select If (Cells(frow(j) + n + p, 13) = "PC") Then row_max = frow(j) + n + p Exit For End If Next p End If End If n = n + 1

If (sumpc(n - 1) = sumpc(n - 2)) Then Exit Do

End If

sumpc(n) = sumpc(n - 1) Loop

m = 1

'#####Modifico el régimen en función del máximo If (falso > 1) Then Cells(row_max, 13) = "PC" Range(Cells(row_max, 13), Cells(pcfin(h), 13)) = "PC" End If

'Copia del régimen de funcionamiento óptimo en una nueva columna, así como los resultados econó micos del cambio

'durante el día entero

(4)

Cells(row_max, 3).Select day_row = ActiveCell.Row fecha = ActiveCell.Value For p = 1 To 20

If (Cells(day_row - p, 3) = fecha) Then fday = Cells(day_row - p, 3).Row End If

If (Cells(day_row + p, 3) = fecha) Then lday = Cells(day_row + p, 3).Row End If

Next p

'Selecciona las celdas del régimen y las pega Range(Cells(fday, 15), Cells(lday + 1, 15)).Select Selection.Copy

Cells(fday, 20).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False

Application.CutCopyMode = False

'Selecciona el margen recibido optimizado y lo pega Range(Cells(fday, 17), Cells(lday + 1, 17)).Select Selection.Copy

Cells(fday, 22).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False If (Cells(inicio, 20) = "DS") Then Cells(inicio, 22) = 0 End If If (Cells(inicio, 19) = "DS") Then Cells(inicio, 20) = "STOP" End If sumpc(n) = 0 falso = 0 Next j

'Rellenar filas vacías con Stop For i = inicio To final

Cells(i, 20).Select vacio = Cells(i, 20) If (vacio = "") Then Cells(i, 20).Select Range(Selection, Selection.End(xlDown)).Select Selection.FormulaR1C1 = "STOP" Cells(i, 22).Select Range(Selection, Selection.End(xlDown)).Select Selection.FormulaR1C1 = 0 End If Next i

Range(Cells(final + 2, 19), Cells(final + 2, 22)).Select Range(Selection, Selection.End(xlDown)).Select

Selection.ClearContents

'Devolver las fórmulas cambiadas a su estado original Range("M4").Select

Selection.AutoFill Destination:=Range("M4:M" & final) '#####Presentación de los resultados#####

sum_ini = 0 sum_opt = 0 pc_ini = 0 pc_opt = 0 starts_ini = 0 starts_opt = 0

For i = inicio To final

sum_ini = sum_ini + Cells(i, 21) sum_opt = sum_opt + Cells(i, 22) If (Cells(i, 19) = "PC") Then pc_ini = pc_ini + 1 End If If (Cells(i, 20) = "PC") Then pc_opt = pc_opt + 1 End If

(5)

starts_ini = starts_ini + 1 End If

If (Cells(i, 20) = "START1") Then starts_opt = starts_opt + 1 End If

Next i

'Presentación de las diferencias Sheets("resultados").Select Range("B9") = sum_ini Range("D9") = sum_opt Range("B12") = pc_ini Range("D12") = pc_opt Range("B18") = starts_ini Range("D18") = starts_opt If (Range("D15") <= 0) Then

vbanswer = MsgBox("El beneficio neto es nulo o negativo" & Chr(13) & "La planta debe parar su p roducción", vbOK)

End If

Range("A1").Select

vbanswer = MsgBox("Optimización realizada", vbOK)

References

Related documents

College Mathematics (3 Credits) Biology (6 Credits) Arts and Humanities 3 Arts and Humanities 3 TOTAL 35 20 8.00 **Total up RED # ** Excess credits 0.00 8.00 Analyzing and

Most students support the involvement of an online instructor in the online discussion, and faculty members involved in these discussions function as helpers in the development

No.3 IP Fixed Mobile All-IP based FMC Single Platform Box Module Site or Central Office One Cabinet One Site 9KW 3×3KW Smart modularized power management 2KW

Specifically, we presented a P2P streaming protocol used by a participating peer to request a media file from the system; a cluster-based dispersion al- gorithm, which

Each runs a single Petal server, which is a user-level process that accesses the physical disks using the Unix raw disk interface, and the network using UDP/IP Unix sockets..

Motivation Problem statement Simulation scenario Results of performance prediction ConclusionsB. A study on machine learning and regression based models for performance

Please Note: If you book a Royal Caribbean International holiday in conjunction with other services (such as flights, on-shore accommodation and/or ground transfers) which

The liability of the Carrier, his servants and or agents shall, subject to any deductibles, be limited by virtue of the Athens Convention in respect of death and/or personal injury