Excel - Ejecutar macro al cambiar valor de una formula

Mensajes realizados anteriormente (No estaban la última vez que entraste)

Ejecutar macro al cambiar valor de una formula
Mau (30/07/2019 04:22:48)839 visitas
9 respuestas

(+1)Ejecutar macro al cambiar valor de una formula
Antoni Masana (30/07/2019 07:12:36)

(+1)Ejecutar macro al cambiar valor de una formula
Antoni Masana (30/07/2019 15:26:47)

Ejecutar macro al cambiar valor de una formula
Mau (30/07/2019 21:53:16)

(+2)Ejecutar macro al cambiar valor de una formula
Antoni Masana (31/07/2019 06:54:32)

Ejecutar macro al cambiar valor de una formula
Mau (01/08/2019 06:43:41)

Ejecutar macro al cambiar valor de una formula
Jacobo (18/05/2020 22:07:53)

(+1)Ejecutar macro al cambiar valor de una formula
Antoni Masana (19/05/2020 00:19:14)

Ejecutar macro al cambiar valor de una formula
Jacob (19/05/2020 04:34:52)

Ejecutar macro al cambiar valor de una formula
Antoni Masana (19/05/2020 16:16:29)

Pos: 140

Val: 19

Ha disminuido su posición en 4 puestos en Excel (en relación al último mes)

Ejecutar macro al cambiar valor de una formula

Publicado por Mau (6 intervenciones) el 30/07/2019 04:22:48

Buenas.

Ojalá me pudieran ayudar con un problema con una macro que tengo en un archivo, estoy tratando de aprender por mi cuenta como funciona esto de las macros pero vaya que es muy complicado, poco a poco voy usando codigos para las funciones basicas que necesito pero estoy atorado.

Mi archivo lo que hace al abrir el excel es ejecutar un comando que borra y agrega unas formulas a varias celdas.

Y tengo ademas un codigo que activa una macro (INDICE) al cambiar el valor de una formula en una celda (D5).

Hasta ahi todo bien. El problema es que necesito el mismo codigo de ejecutar la macro (INFLACION) al cambiar el valor de la formula pero en otra celda (D15), ahi es cuando simplemente copio el codigo, cambio la celda, y al hacer los movimientos en el excel entra en una especie de bucle activando la primera macro (INDICE) y la segunda (INFLACION) despues de unos 2, 3 segundos ya se queda quieto. Esto copiando el codigo con el excel abierto, obviamente. Pero al cerrarlo y volver abrir, como mencione antes, se ejecuta un codigo solo al abrir y ya que hace movimientos provoca la ejecucion de las 2 macros y ahi es cuando de plano se traba.

Ese es mi problema, como puedo agregar el segundo codigo sin que provoque todos esos fallos, todos los ejemplos que he visto en internet solo te explican cual es codigo pero siempre es para una celda, y yo necesito dos celdas distintas.

Y como pregunta extra, notaran que en la celda G3 tengo anotado una columna, que se relaciona con las formulas en las celdas D2, D11 y D15, esto porque la tabla de los valores necesito que al actualizarse cada año, la matriz tome la nueva columna, usando la funcion indirecto es que encontre la forma de agregar esa columna cada año, pero estoy seguro que debe haber alguna forma de no usar la funcion indirecto, que se agregue una columna en automatico cuando yo la escriba y que la matriz en las formulas tome esa nueva columna.

Espero no ser muy revuelto y ojala me pudieran ayudar, se los agradeceria mucho. comparto mi archivo. Gracias.

Indice-Inflacion.zip(52,0 KB)

Valora esta pregunta

Me gusta: Está pregunta es útil y esta clara

No me gusta: Está pregunta no esta clara o no es útil

Responder

Pos: 2

Val: 4.149

Ha mantenido su posición en Excel (en relación al último mes)

Ejecutar macro al cambiar valor de una formula

Publicado por Antoni Masana (1459 intervenciones) el 30/07/2019 07:12:36

El evento Worksheet_Calculate se ejecuta cada vez que Excel tiene que recalcular las fórmulas.
Y ¿Cuando tiene que recalcular las formulas? cada vez que se modifica una celda.

¿Que esta pasando?

Modificas manualmente una celda ==> Se ejecuta Calculate ==> Llama a INDICE ==> Modifica una celda ==> Se ejecuta Calculate ==> Llama a INDICE ==> Modifica una celda ==> Se ejecuta Calculate ==> etc.

Y entra en bucle.

Esto se evita asi:

Application.EnableEvents = False: Call INDICE

Application.EnableEvents = True

Adjunto libro.

Saludos.
\\//_

Indice-Inflacion-Corregido.zip(53,2 KB)

Valora esta respuesta

Me gusta: Está respuesta es útil y esta clara

No me gusta: Está respuesta no esta clara o no es útil

Comentar

Pos: 2

Val: 4.149

Ejecutar macro al cambiar valor de una formula

Publicado por Antoni Masana (1459 intervenciones) el 30/07/2019 15:26:47

Te había contestado pero esta incompleto y al ver el mensaje que me has enviado he recordado que faltaba comentar algo.

Esta macro es la primera que se ejecuta al abrir el libro como ya sabes.

Private Sub Workbook_Open()

    Range("B5").Select:    Selection.ClearContents

    Range("C5").Select:    Selection.ClearContents

    Range("D5").Select:    Selection.ClearContents

    Range("C5").Select:    Selection.ClearContents

    Range("B11").Select:    ActiveCell.FormulaR1C1 = "=+PROPER(TEXT(R1C3,""mmmm""))"

    Range("B15").Select:    ActiveCell.FormulaR1C1 = "=+PROPER(TEXT(R1C3,""mmmm""))"

    Range("C15").Select:    ActiveCell.FormulaR1C1 = "=+R[-14]C[1]"

    Range("C11").Select:    ActiveCell.FormulaR1C1 = "=+R[-10]C[1]-1"

    Range("C16").Select:    Selection.Copy

    Range("C17").Select

    Selection.PasteSpecial Paste:=xlPasteValues, _

                           Operation:=xlNone, _

                           SkipBlanks:=False, _

                           Transpose:=False

    Application.CutCopyMode = False

    Range("B5").Select

End Sub

De esta forma el código se lee mejor.

Lo primero que he visto es que la celda C5 la borras dos veces, supongo que debe ser un error.
Lo siguiente es que pones formulas. ¿Para que? ¿Es que se pierden por el camino?
Y lo tercero es lo que te he comentado en el post anterior, lo correcto sería esto:

Private Sub Workbook_Open()

    Application.EnableEvents = False

    Range("B5").Select:    Selection.ClearContents

    Range("C5").Select:    Selection.ClearContents

    Range("D5").Select:    Selection.ClearContents

    Range("C5").Select:    Selection.ClearContents

    Range("B11").Select:    ActiveCell.FormulaR1C1 = "=+PROPER(TEXT(R1C3,""mmmm""))"

    Range("B15").Select:    ActiveCell.FormulaR1C1 = "=+PROPER(TEXT(R1C3,""mmmm""))"

    Range("C15").Select:    ActiveCell.FormulaR1C1 = "=+R[-14]C[1]"

    Range("C11").Select:    ActiveCell.FormulaR1C1 = "=+R[-10]C[1]-1"

    Range("C16").Select:    Selection.Copy

    Range("C17").Select

    Selection.PasteSpecial Paste:=xlPasteValues, _

                           Operation:=xlNone, _

                           SkipBlanks:=False, _

                           Transpose:=False

    Application.CutCopyMode = False

    Range("B5").Select

    Application.EnableEvents = True

End Sub

Ahora otra cosa:

Supongamos que no te he contestado y quieres ir ejecutando las macros paso a paso para ver que hacen.
Una de las cosas que puedes hacer es ponerte encima de la primera linea de instrucción y pulsar F9, esto provoca que la ejecución se pare y pulsando F8 vas ejecutando paso a paso.

Esto esta muy bien, pero: ¿como lo hago para la macro OPEN?

Tienes dos opciones:

Opción - 1

* Deshabilitas las macros.
* Abres el libro.
* Habilitas las macros.
* Te colocas en la macro OPEN y pulsando F8 vas ejecutando paso a paso.

Opción - 2

* Pones un STOP al inicio de la macro OPEN.

Private Sub Workbook_Open()

    Stop

    Range("B5").Select:    Selection.ClearContents

...

End Sub

* Guardas y cierras el libro
* Abres el libro.
* La macro se ejecuta y se para en el STOP
* Pulsando F8 vas ejecutando paso a paso.
* Cuando acabes las pruebas recuerda quitar el STOP

Para hacer pruebas con Application.EnableEvents en las macros. Crea estas dos:

Private Sub Macros_STOP()

    Application.EnableEvents = False

End Sub

Private Sub Macros_START()

    Application.EnableEvents = True

End Sub

La primera para la ejecución de las macros y la segunda la activa, evita tener que ir cada vez al menú de opciones para activar o desactivar las macros.

Saludos.
\\//_

Valora esta respuesta

Comentar

Pos: 140

Val: 19

Ejecutar macro al cambiar valor de una formula

Publicado por Mau (6 intervenciones) el 30/07/2019 21:53:16

Muchas gracias por la ayuda, ya estuve revisando el archivo y probando lo que me dijo, al parecer ya funciona bien y no se traba pero ahora aun no comprendo algo.

Agregue, en la hoja de Calculate, la macro para Inflacion, para que se active sola y no con el boton que habia puesto originalmente.

Ahora pasa esto:

Al cambiar solo una celda, ya sea B5 o C5, noto que se activa la macro INDICE, cuando no lo hacia antes, puesto que al agregar solo el mes o el año el resultado de la formula no cambia ya que necesita los dos valores para hacer la operacion y tambien activa la de INFLACION pero no se esta modificando la formula en C16.

Evidentemente aqui lo que necesito es que solo se active una u otra al cambiar el valor de su formula respectiva.

Siguiendo los pasos de la macro, note un error, que supongo no debe pasar, al revisar la macro al abrir el excel noté que despues de cada una de las lineas activa las macros de INDICE e INFLACION, lo que supongo para los que saben de esto, es terrible, porque las activa una y otra vez hasta que acaba el codigo de OPEN.

Por lo que entiendo, la macro al borrar el mes, modifica la formula a 0 y activa las macros, pero cuando borra el año, la formula sigue en 0 pero vuelve a activarlas cuando supuestamente no deberia pasar.

Finalmente, al activarse las macros, la ultima celda seleccionada es C17, la de la macro INFLACION, cuando en el codigo para abrir el excel, quiero que se quede al final en la celda B5.

PD. efectivamente fue un error lo de borrar dos veces la misma celda, no lo habia visto. Tiene formulas ya que tanto el mes como el año se pueden modificar a necesidad y al hacer eso pierden la formula.

Adjunto de nuevo mi archivo, muchas gracias por su ayuda.

Indice-Inflacion-Corregido.zip(49,3 KB)

Valora esta respuesta

Comentar

Pos: 2

Val: 4.149

Ejecutar macro al cambiar valor de una formula

Publicado por Antoni Masana (1459 intervenciones) el 31/07/2019 06:54:32

Me ha costado seguir el razonamiento de la explicación y no porque este mal redactado si no porque estoy espeso a las 6 de la mañana.

Pero he visto una incoherencia. Esta es tu macro antes:

Private Sub Worksheet_Calculate()

    Static Anterior_Valor As Variant

    If Anterior_Valor <> Range("D2").Value Then

        Anterior_Valor = Range("D2").Value

        Application.EnableEvents = False: Call INDICE

        Application.EnableEvents = True

    End If

    If Anterior_Valor <> Range("C16").Value Then

        Anterior_Valor = Range("C16").Value

        Application.EnableEvents = False: Call INFLACION

        Application.EnableEvents = True

    End If

End Sub

Fijate que utilizas la misma variable para saber si han cambiado dos campos diferentes. Y esta es la misma corregida.

Private Sub Worksheet_Calculate()

    Static Anterior_D02 As Variant, _

           Anterior_C16 As Variant

    If Anterior_D02 <> Range("D2").Value Then

        Anterior_D02 = Range("D2").Value

        Application.EnableEvents = False: Call INDICE

        Application.EnableEvents = True

    End If

    If Anterior_C16 <> Range("C16").Value Then

        Anterior_C16 = Range("C16").Value

        Application.EnableEvents = False: Call INFLACION

        Application.EnableEvents = True

    End If

End Sub

Saludos.
\\//_

Valora esta respuesta

Comentar

Pos: 140

Val: 19

Ejecutar macro al cambiar valor de una formula

Publicado por Mau (6 intervenciones) el 01/08/2019 06:43:41

Muchas muchas gracias, ahora si funciona como quiero, en verdad se lo agradezco mucho

Valora esta respuesta

Comentar

Ejecutar macro al cambiar valor de una formula

Publicado por Jacobo (1 intervención) el 18/05/2020 22:07:53

Y si cambina más de dos campos?, no se mucho de programación pero estoy intentando desarrollar este código:

Private Sub Worksheet_Change(ByVal Target As Range)

    Static anteriorvalorL3 As Variant, _

    anteriorvalorL4 As Variant, _

    anteriorvalorL5 As Variant, _

    anteriorvalorL6 As Variant

   If Range("L3").Value <> anteriorvalorL3 Then

     anteriorvalorL3 = Range("L3")

        Sheets("Lista Org. de Inv.").Cells(Target.Row, 13) = Now()

        End If

If Range("L4").Value <> anteriorvalorL4 Then

     anteriorvalorL4 = Range("L4")

        Sheets("Lista Org. de Inv.").Cells(Target.Row, 13) = Now()

        End If

If Range("L5").Value <> anteriorvalorL5 Then

     anteriorvalorL5 = Range("L5")

        Sheets("Lista Org. de Inv.").Cells(Target.Row, 13) = Now()

        End If

If Range("L6").Value <> anteriorvalorL6 Then

     anteriorvalorL6 = Range("L6")

        Sheets("Lista Org. de Inv.").Cells(Target.Row, 13) = Now()

        End If

End Sub

Necesito configurar hasta 29 celdas, las cuales tienen formula y cambian de resultado.

Valora esta respuesta

Comentar

Pos: 2

Val: 4.149

Ejecutar macro al cambiar valor de una formula

Publicado por Antoni Masana (1459 intervenciones) el 19/05/2020 00:19:14

Puedes crear tantas variables y repetir el IF tantas veces como celdas tengas que cambiar.

Ahora bien si en tu caso las celdas a verificar están en las misma columna y van de la fila 3 a la 31 (Necesito configurar hasta 29 celdas) es bastante simple

Private Sub Worksheet_Change(ByVal Target As Range)

    Static Anterior_Valor(31) As Variant

    Dim Col as Long, Fil as Long

    Col = Target.Column

    Fil = Target.Row

    If Col = 12 Then                      ' --- Columna 'L'

        If Fil >= 3 And Fil <= 31 Then    ' --- Filas de la 3 a la 31

            If Range("L" & Fil).Value <> Anterior_Valor(Fil) Then

                Anterior_Valor(Fil) = Range("L" & Fil)

                Sheets("Lista Org. de Inv.").Cells(Target.Row, 13) = Now()

            End If

        End If

    End If

End Sub

Y asi de simple se verifican 29 o 29000 celdas.

Saludos.
\\//_

Valora esta respuesta

Comentar

Ejecutar macro al cambiar valor de una formula

Publicado por Jacob (1 intervención) el 19/05/2020 04:34:52

El código que intento armar es para lograr que en las celdas de la columna M se regitstre la Fecha y hora en que cambio el resultado de las fórmulas configuradas en la Columna L.

El rango donde están las celdas que continene fórmulas es L3:L29
El rango donde están las celdas donde quiero se registre la fecha y hora en que cambio el resultado de las fórmulas del reango anterior es M3:M29

El resultado de las celdas de la columna L es cambiante y deseo que en las celdas de la columna M quede el registro de fecha y hora del último cambio.

Probé el código que me compartiste pero no registra nada en las celdas de la columna M.

Te agradezco muchismo la ayuda.

Valora esta respuesta

Comentar

Pos: 2

Val: 4.149

Ejecutar macro al cambiar valor de una formula

Publicado por Antoni Masana (1459 intervenciones) el 19/05/2020 16:16:29

Realizo unas pequeñas modificaciones,

Private Sub Worksheet_Change(ByVal Target As Range)

    Static Anterior_Valor(31) As Variant

    Dim Col as Long, Fil as Long

    Col = Target.Column

    Fil = Target.Row

    If Col = 12 Then                      ' --- Columna 'L'

        If Fil >= 3 And Fil <= 29 Then    ' --- Filas de la 3 a la 29

            If Range("L" & Fil).Value <> Anterior_Valor(Fil) Then

                Anterior_Valor(Fil) = Range("L" & Fil)

                Application.EnableEvents = False

                Cells(Fil, 13) = Now()

                Application.EnableEvents = True

            End If

        End If

    End If

End Sub

Si no te funciona enviame el libro, puedes hacerlo a mi correo: amasana@hotmail.com

Saludos.
\\//_

Valora esta respuesta

Comentar

Hacer cuadrante de horarios y que al cambiar un nombre cambien todos

tabla en excel con datos actualizables