Macro que resta números e inserta una fila debajo, y además mantiene la coherencia con las fechas y horas
Necesito crear una macro en excel (VBA) que me permita comparar dos fechas de dos columnas diferentes y haga lo siguiente, por ejemplo:
Si la fecha de la celda F3 es igual a la fecha de la celda E4, entonces la macro pasa a la celda F4. Aquí compara, si la fecha de la celda F4 es distinta de la fecha de la celda E6, entonces me inserta una fila entre medio de la F4 y la E6 (sería la fila F5, o simplemente podría ser una fila al final de la tabla). Y continúa comparando, si la fecha de la celda F6 es distinta de la fecha de la celda E8, entonces vuelve a insertar una fila entre medio de ambas o simplemente al final.
Esto debe realizarse para el rango total de la tabla, y sucesivamente; la idea es que no queden "vacíos" de fecha y tampoco se sobrepongan o incluyan, por ejemplo: la celda F1163 tiene la fecha y hora -> 03-01-2016 08:59, y la celda E1164 tiene la fecha y hora -> 03-01-2016 08:58, por ende la fecha y hora de E1164 está contenida en la fecha y hora de F1163. Lo correcto sería que en la celda F1163 la fecha y hora fuera 03-01-2016 08:58, para así continuar con la celda E1164 con fecha y hora 03-01-2016 08:58. Cabe decir que en la columna llamada "EO" (columna C) hay índices, siendo el N el de menor prioridad, por ende, como en este caso la fila 1163 posee el índice N, entonces a ésta se le modifica la fecha, dado que manda la fila 1164 con índice distinto de N (DP).
Además de esto, sucede un caso particular, en la fila 1167 posee índice N, por lo que se le modifica la fecha de fin (la de inicio no se modifica dado que no se encuentra incluida o superpuesta en la fecha de inicio del índice distinto de N (DP) en la fila 1168). La fila 1167 debería quedar con fecha y hora de fin -> 19-02-216 14:09; pero luego, entre las filas 1168 y 1169 debiera crearse una nueva fila con fecha y hora de inicio: 19-02-2016 14:25, y con fecha y hora de fin: 16-04-2016 12:41 (de las 12:41 a las 12:59 del 16-04-2016 está incluido en el índice distinto de N, y como manda dicho índice (DP), se corta la hora de fin del estado N cuando DP comienza).
Espero se entienda lo que deseo hacer, y puedan ayudarme por favor. A continuación adjunto 2 fotos, la primera es la tabla con los "vacíos y superposiciones" de fecha y hora, la segunda foto es parte de la tabla sin dichos vacíos y superposiciones, es decir, con la fila insertada y las fechas y horas como corresponde (cabe decir que las filas seleccionadas en la foto son las que inserté manualmente a modo de explicación).
1 respuesta
Prueba así a ver si te sirve:
Dim h As Worksheet
Set h = Hoja1
a = Range("E" & Rows.Count).End(xlUp).Row
If a < 3 Then Exit Sub
For B = 3 To a
If Cells(B, 6) = Cells(B + 1, 5) Then
ElseIf Cells(B, 6) > Cells(B + 1, 5) Then
'superpuesto
vi = Cells(B, 3)
vf = Cells(B + 1, 3)
If vi = "N" Then
h.Cells(B, 6).Value = h.Cells(B + 1, 5).Value
ElseIf vf = "N" Then
h.Cells(B + 1, 5).Value = h.Cells(B, 6).Value
Else
'caso que no haya ninguna "N" actualizamos el primer registro
h.Cells(B, 6).Value = h.Cells(B + 1, 5).Value
End If
ElseIf Cells(B, 6) < Cells(B + 1, 5) Then
'falta
h.Cells(B + 1, 5).EntireRow.Insert
h.Cells(B + 1, 5).Value = h.Cells(B, 6).Value
h.Cells(B + 1, 6).Value = Cells(B + 2, 5).Value
B = B + 1
End If
Next B
Hasta el B=7 funciona bien, pero cuando me crea una nueva fila en B=7, la fecha y hora de inicio está correcta, pero la fecha y hora de fin que coloca la macro es: 29-01-2016 0:00; y debiera ser 27-01-2016 23:59, dado que hasta ahí abarcaba el estado "N" que estamos recortando. Quedo atento a sus comentarios y desde ya le agradezco enormemente su ayuda.
Discúlpame, por favor, pero la verdad es que no lo estoy entendiendo.. no leo correctamente los datos de tus ejemplos, no veo de dónde sale el 27-01...
No te disculpes, te explico: primera foto celda F1165 con EO "N". Dicho EO tiene fecha-hora de fin 27-01-2016 23:59. En la segunda foto está la fila insertada, y ahí aparece cómo debe quedar. En dicha fila insertada no puede exceder en fecha-hora de las 27-01-2016 23:59, dado que el estado "N" que estamos analizando sólo llega hasta ahí, por ende no puede pasarse a las 29-01-2016 0:00 como lo hace la macro, dado que de 27-01-2016 23:59 a 29-01-2016 0:00 no existe ningún EO, por lo que no se puede considerar un estado "N" en ese lapsus de tiempo. Espero haya quedado más claro, ante cualquier consulta sólo dime. Muchas gracias de ante mano!
Será por la hora que estoy algo espeso, pero sigo sin entender. El problema lo tenemos en las inserciones que se realizan, ¿verdad? ¿Lo demás lo hace bien la macro? En cuanto a las inserciones, ¿entonces entiendo que no tienen por qué cubrir todo el espacio entre la hora fin del registro anterior y la inicial del siguiente... entonces qué criterio debe seguir?
Estaré un rato ausente, respondo más tarde si me aclaras este punto.
Mire le explico, EO es una columna muy importante, corresponde a los estados operativos de una central. N significa que estuvo NORMAL, cualquier otro estado es una FALLA. Por ende, y como este informe sirve para hacer cobros a las centrales, siempre mandará el estado distinto de N. Por ejemplo, se tiene un estado N que comprende de las 20-01-2016 13:00 a las 25-01-2016 17:00; pero luego se tiene un estado diferente de N (llamémosle DP, pero puede ser cualquiera distinto de N) que comprende de las 22-01-2016 01:00 a las 23-01-2016 15:00. Este intervalo de tiempo que tiene el estado DP es el que manda, por lo que debe recortarse al estado N, quedando de la siguiente manera:
N: 20-01-2016 13:00 hasta 22-01-2016 01:00
DP: 22-01-2016 01:00 hasta 23-01-2016 15:00 (MANDA)
N: 23-01-2016 15:00 hasta 25-01-2016 17:00
Si se da cuenta, el estado DP siempre se mantuvo fijo, no varía debido a que manda por sobre el estado N. Sin embargo, el estado N fue "recortado" debido a que incluía en su intervalo al estado DP, y como DP manda, ese intervalo superpuesto dejó de llamarse N y simplemente fue reemplazado por el estado que manda, es decir, DP.
Ahora bien, volviendo a las fotos, poseo los siguientes estados (los que están en conflicto con la macro):
N (fila 1165): 03-01-2016 15:00 hasta 27-01-2016 23:59
DP (fila 1166): 14-01-2016 09:52 hasta 14-01-2016 10:28
N (fila 1167): 29-01-2016 0:00 hasta 16-04-2016 12:59
El estado N fila 1165 comprende el intervalo de tiempo del 03-01-2016 15:00 hasta el
27-01-2016 23:59, pero dentro de dicho intervalo, aparece el estado DP (el cual manda) que comprende el intervalo de tiempo del 14-01-2016 09:52 hasta el 14-01-2016 10:28. Por lo tanto, y como el estado DP no puede ser "borrado" por el estado N, sino todo lo contrario, debería quedar de la siguiente manera:
N (fila 1165*): 03-01-2016 15:00 hasta 14-01-2016 09:52 (* significa que fue modificada)
DP (fila 1166): 14-01-2016 09:52 hasta 14-01-2016 10:28
N (nueva fila insertada): 14-01-2016 10:28 hasta 27-01-2016 23:59
HE AQUÍ EL PROBLEMA!!!!!
La macro, en la nueva fila insertada con negrita, me pone el siguiente intervalo de tiempo:
14-01-2016 10:28 (perfecto) hasta 29-01-2016 0:00 (error)
EL ERROR ES: se puso como fecha-hora FIN en la nueva fila insertada la fecha-hora inicio de la fila 1167, y no se respetó la fecha-hora FIN original del estado N de la fila 1165, dando un salto que no corresponde de las 27-01-2016 23:59 a las 29-01-2016 0:00.
Si queda alguna duda, consultar. Quedo atento a sus comentarios. Saludos y muchas gracias!
Ahora sí queda claro.
A ver si damos con la tecla correcta:
Dim h As Worksheet
Set h = Hoja1
a = Range("E" & Rows.Count).End(xlUp).Row
If a < 3 Then Exit Sub
VFALT = 3
For B = 3 To a
'tomamos valor final si es N
If h.Cells(B, 3) = "N" Then
v = h.Cells(B, 6)
End If
'comparamos final con siguiente inicial
If Cells(B, 6) = Cells(B + 1, 5) Then
'caso iguales no se hace nada
ElseIf Cells(B, 6) > Cells(B + 1, 5) Then
'caso final mayor que siguiente inicial
vi = h.Cells(B, 3)
vf = h.Cells(B + 1, 3)
If vi = "N" Then
vii = h.Cells(B, 6).Value
h.Cells(B, 6).Value = h.Cells(B + 1, 5).Value
'verificamos si final de siguiente es menor que final
If h.Cells(B + 1, 6).Value < vii And h.Cells(B + 1, 3) <> "N" Then
VFALT = v
Else
VFALT = 3
End If
ElseIf vf = "N" Then
h.Cells(B + 1, 5).Value = h.Cells(B, 6).Value
Else
'caso que no haya ninguna "N" actualizamos el primer registro
h.Cells(B, 6).Value = h.Cells(B + 1, 5).Value
End If
ElseIf Cells(B, 6) < Cells(B + 1, 5) Then
'caso final menor que inicial
h.Cells(B + 1, 5).EntireRow.Insert
h.Cells(B + 1, 5).Value = h.Cells(B, 6).Value
h.Cells(B + 1, 6).Value = h.Cells(B + 2, 5).Value
If VFALT <> 3 Then
h.Cells(B + 1, 6).Value = VFALT
VFALT = 3
End If
B = B + 1
End If
Next B
Funciona bien, con una salvedad, entre dos estados diferentes de N, se crean nuevas filas con intervalos entre estos, por ejemplo:
N (fila1): 20-01-2016 12:00 hasta 23-01-2016 13:00
DP (fila2): 21-01-2016 15:00 hasta 24-01-2016 17:00
DP (fila3): 24-01-2016 18:00 hasta 25-01-2016 01:00
DP(fila4) 25-01-2016 17:00 hasta 25-01-2016 19:30
La macro hace lo siguiente:
N (fila1*): 20-01-2016 12:00 hasta 21-01-2016 15:00 (correcto) (*significa que fue modificada)
DP (fila2): 21-01-2016 15:00 hasta 24-01-2016 17:00 (correcto)
DP (nueva fila): 24-01-2016 17:00 hasta 24-01-2016 18:00 (incorrecto, no existe estado definido por cubrir en este intervalo de tiempo)
DP (fila3): 24-01-2016 18:00 hasta 25-01-2016 01:00 (correcto)
DP (nueva fila): 25-01-2016 01:00 hasta 25-01-2016 17:00 (incorrecto, no existe estado definido por cubrir en este intervalo de tiempo)
DP (fila4) 25-01-2016 17:00 hasta 25-01-2016 19:30 (correcto)
En resumen, lo que la macro debería hacer es:
N (fila1*): 20-01-2016 12:00 hasta 21-01-2016 15:00 (*significa que fue modificada)
DP (fila2): 21-01-2016 15:00 hasta 24-01-2016 17:00
DP (fila3): 24-01-2016 18:00 hasta 25-01-2016 01:00
DP (fila4) 25-01-2016 17:00 hasta 25-01-2016 19:30
No se deben crear intervalos de tiempo que no están definidos en algún estado operativo. Espero se entienda bien, trato de explicarme lo mejor que puedo. Muchísimas gracias por todo lo que me ha ayudado hasta ahora, estoy más que pagado, de verdad muchas gracias. Saludos y disculpe por explotarlo con tanto detalle, sé por lo demás que la macro es bastante compleja.
Pues vaya.. explicando todo de entrada habríamos ahorrado tanto rodeo..
A ver si así, caso contrario hasta mañana no podré corregirlo:
Dim h As Worksheet
Set h = Hoja1
a = Range("E" & Rows.Count).End(xlUp).Row
If a < 3 Then Exit Sub
VFALT = 3
For B = 3 To a
'tomamos valor final si es N
If h.Cells(B, 3) = "N" Then
v = h.Cells(B, 6)
End If
'comparamos final con siguiente inicial
If Cells(B, 6) = Cells(B + 1, 5) Then
'caso iguales no se hace nada
ElseIf Cells(B, 6) > Cells(B + 1, 5) Then
'caso final mayor que siguiente inicial
vi = h.Cells(B, 3)
vf = h.Cells(B + 1, 3)
If vi = "N" Then
vii = h.Cells(B, 6).Value
h.Cells(B, 6).Value = h.Cells(B + 1, 5).Value
'verificamos si final de siguiente es menor que final
If h.Cells(B + 1, 6).Value < vii And h.Cells(B + 1, 3) <> "N" Then
VFALT = v
VVV = VFALT
Else
VFALT = 3
End If
ElseIf vf = "N" Then
h.Cells(B + 1, 5).Value = h.Cells(B, 6).Value
Else
'caso que no haya ninguna "N" actualizamos el primer registro
h.Cells(B, 6).Value = h.Cells(B + 1, 5).Value
End If
ElseIf Cells(B, 6) < Cells(B + 1, 5) Then
'caso final menor que inicial
If h.Cells(B, 6) > VVV Then
Else
h.Cells(B + 1, 5).EntireRow.Insert
h.Cells(B + 1, 5).Value = h.Cells(B, 6).Value
h.Cells(B + 1, 6).Value = h.Cells(B + 2, 5).Value
If VFALT <> 3 Then
h.Cells(B + 1, 6).Value = VFALT
VVV = VFALT
VFALT = 3
End If
B = B + 1
End If
End If
Next B
Está casi perfecta la macro, sólo queda el siguiente detalle (adjunto foto):
Las celdas marcadas con ROJO deberían ser 08-01-2016 04:29 y 08-01-2016 19:59 respectivamente. La primera celda en rojo me pone directamente a la fecha final del estado N "recortado", lo que implica que lo convierte en un estado que se superpone a los demás. La segunda celda en rojo debería ser la fecha final del estado N "recortado", sin embargo se alarga hasta las 08-01-2016 20:12, y el intervalo entre las 08-01-2016 19:59 y las 08-01-2016 20:12 no es ocupado por ningún estado operativo, por lo que no debe existir.
Desde ya admiro su paciencia y sabiduría respecto a este tema. Ayúdeme cuando pueda no más, no se preocupe, entiendo que tiene cosas que hacer aparte de ayudar y, por lo demás, hay más gente que también necesita su ayuda. Si puede corregirme esos detallitos, genial, y si no, no se preocupe y muchas gracias de verdad, me ha sido muy útil en la creación de esta macro. Saludos!
Mañana por la mañana lo reviso Nelson, a ver si lo dejamos perfecto.
Gracias Víctor, lo espero mañana por la mañana entonces, no se preocupe. De verdad muchas gracias por su buena voluntad. Saludos, que esté bien!
Nelson, prueba con esta versión:
Dim h As Worksheet
Set h = Hoja1
a = h.Range("E" & Rows.Count).End(xlUp).Row
If a < 3 Then Exit Sub
VFALT = 3
For B = 3 To a
'tomamos valor final si es N
If h.Cells(B, 3) = "N" Then
v = h.Cells(B, 6)
End If
'comparamos final con siguiente inicial
If h.Cells(B, 6) = h.Cells(B + 1, 5) Then
'caso iguales no se hace nada
ElseIf h.Cells(B, 6) > h.Cells(B + 1, 5) Then
'caso final mayor que siguiente inicial
vi = h.Cells(B, 3)
vf = h.Cells(B + 1, 3)
If vi = "N" Then
vii = h.Cells(B, 6).Value
h.Cells(B, 6).Value = h.Cells(B + 1, 5).Value
'verificamos si final de siguiente es menor que final
If h.Cells(B + 1, 6).Value < vii And h.Cells(B + 1, 3) <> "N" Then
'nos guardamos el valor final
VFALT = v
VVV = VFALT
Else
VFALT = 3
End If
ElseIf vf = "N" Then
h.Cells(B + 1, 5).Value = h.Cells(B, 6).Value
Else
'caso que no haya ninguna "N" actualizamos el primer registro
h.Cells(B, 6).Value = h.Cells(B + 1, 5).Value
End If
ElseIf h.Cells(B, 6) < h.Cells(B + 1, 5) Then
'caso final menor que inicial
If h.Cells(B, 6) > VVV Then
Else
h.Cells(B + 1, 5).EntireRow.Insert
h.Cells(B + 1, 5).Value = h.Cells(B, 6).Value
h.Cells(B + 1, 6).Value = h.Cells(B + 2, 5).Value
If VFALT <> 3 Then
If VFALT > h.Cells(B + 2, 5).Value Then
h.Cells(B + 1, 6).Value = h.Cells(B + 2, 5).Value
Else
h.Cells(B + 1, 6).Value = VFALT
VVV = VFALT
VFALT = 3
End If
End If
B = B + 1
End If
End If
Next B
Hola estimado Víctor, su macro está perfecta!!!!!!!!! Está absolutamente perfecta!!! Quiero darle las gracias por la ayuda, la paciencia, su tiempo y dedicación en este trabajo. Me queda sólo una pregunta, cómo podría definir un rango específico que recorra dicha macro? qué le agrego antes de los ciclos? Muchas gracias de verdad, puntearé esta pregunta de inmediato! Gracias totales!
Por ejemplo, si quiero utilizar la macro desde la fila 50 a la fila 100, porque ahí se encuentran los datos de la central que quiero analizar, en B tendría que poner 50 en vez de 3, y en A tendría que poner 100 en vez del rango completo de la columna E, verdad?
Ojalá todo el mundo fuera tan agradecido como tú Nelson, lo agradezco. Por lo demás ya te dije que para mí es un placer poder ayudar y en el proceso no dejo de aprender.
Lo que comentas es exacto, el bucle se ejecuta desde B=3 hasta el número que representa la última fila con datos de la columna "E". Puedes modificar esos valores directamente en el código como sospechas.
Una manera algo más elegante de hacerlo y que te ahorraría modificar el código cada vez, sería mediante imputbox, algo así:
Dim h As Worksheet
Set h = Hoja1
A = Range("E" & Rows.Count).End(xlUp).Row
VM = MsgBox("Quieres personalizar el rango en el que se va a ejecutar la macro?", vbInformation + vbYesNo)
If VM = vbYes Then
B = InputBox("Introduce el número de fila desde el que quieres ejecutar la macro.")
A = InputBox("Introduce el número de fila hasta el que quieres ejecutar la macro.")
If Val(B) > Val(A) Then
MsgBox "El valor desde no puede ser mayor al valor hasta."
Exit Sub
End If
Else
B = 3
End If
If A < 3 Then Exit Sub
For B = B To A
Aquí el resto
.
.
.
.Puedes insertar un botón en la hoja que ejecute la macro, de este modo no tienes que andar entrando en el código.
¡Gracias! Se pasó Víctor, de verdad muchas gracias, no pensé que podrían ayudarme tanto acá, me despido enormemente agradecido y satisfecho por la plataforma y su calidad de persona. Espero le vaya muy bien en todo, se lo merece de sobremanera. Nuevamente muchas gracias, estamos en contacto. Saludos!!
