Ejecutar macro según los valores de una columna

5 envíos / 0 nuevos
Último envío
guarnini
Imagen de guarnini
Offline
última acción: Hace 5 años 3 meses
alta: 18/12/2018 - 15:14
Puntos: 45
Ejecutar macro según los valores de una columna

Buenas tardes,

Llevo un tiempo leyendo en el foro para aprender sobre macros y estoy intentando hacer una macro que haga lo siguiente:

En una columna ("A2:A500") tengo un desplegable con 3 datos posibles ("Conversación", "Propuesta", "Venta"). A continuación tengo otras 3 columnas (B,C,D) que se llaman ("Fecha Conversación", "Fecha Propuesta", "Fecha Venta").

En función del dato que seleccione en cada una de estas filas quiero que se me rellene la columna correspondiente con la fecha en la que seleccionó dicho dato.

La macro que he hecho y revisado me da error 13 y es la siguiente:

Private Sub Worksheet_Change(ByVal Target As Range)

    Set Target = Range("A2:A500")

        If Target.Value = "Conversación" Then

        Sheets("Hoja1").Cells(Target.Row, 2) = Now

        End If

        If Target.Value = "Propuesta" Then

        Sheets("Hoja1").Cells(Target.Row, 3) = Now

        End If

        If Target.Value = "Venta" Then

        Sheets("Hoja1").Cells(Target.Row, 4) = Now

        End If

End Sub

 
¿Alguna ayuda de como puedo hacer que funcione? Muchísimas gracias por anticipado expertos! ;)

Etiquetas: 

Cron
Imagen de Cron
Offline
última acción: Hace 8 meses 2 días
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntos
alta: 23/06/2010 - 12:30
Puntos: 12735
Veo un par de cositas al

Veo un par de cositas al mirar por encima:

Primero: Target es la variable donde te aparece cuál ha sido la celda modificada. Si la sobreescribes, estás perdiendo una información valiosa. Ten en cuenta que esta macro se lanza siempre que modificas cualquier valor. Imagina que tienes celdas relacionadas, y al modificar una se modifican un montón más. Pues se lanza una vez por cada celda modificada. Puede ser una locura y hacer que la hoja tarde muchísimo cada vez que se modifique una sola celda.

Te recomiendo no sobreescribir esa variable, de manera que puedas discriminar si la celda modificada es de las que tú quieres controlar. Por ejemplo, así:

If Target.column = 1 then

    blabla

end if

De esta manera, solo se ejecutará lo de dentro del if cuando has modificado una celda de la columna A.

Segundo: tu error aparece porque que estás intentando extraer el valor de un rango cuando el rango son muchas celdas. Se puede hacer como tú lo tienes planteado , pero siguiendo con la sugerencia anterior, yo lo haría asignando el rango a otra variable que no sea Target. Después recorres todas las celdas de tu rango (sean muchas o una sola): 

Set Rango = Range("A2:A500")

for each celda in Rango

  if celda.value = bla bla bla

....

next

 

Tercero: Imagino que lo de arriba no lo quieres hacer para las 500 celdas cada vez que haya un cambio, sino únicamente para la celda que cambie. Yo pondría alguna cosa más:

If (InStr(Target.Address, ":") = 0) And (InStr(Target.Address, ",") = 0) And (Target.Column = 1) Then

Esto sirve para eliminar la reacción cuando se modifican varias celdas a la vez, ya sean contiguas o no. De esta manera nos aseguramos de que solo se ejecuta el código cuando modificamos una sola celda, y está en la columna A.

Sustituiría los IF por una estructura SELECT CASE, que es más legible.

Añadiría el caso de que se introduzca un valor diferente. Incluso cuando lo tengas preparado con validación de datos, ya te digo que los usuarios son capaces de ingeniárselas para saltarse cualquier medida que pongas.

De esta manera, tu código quedará así:

Private Sub Worksheet_Change(ByVal Target As Range)

If (InStr(Target.Address, ":") = 0) And (InStr(Target.Address, ",") = 0) And (Target.Column = 1) Then

    Select Case Target.Value

    Case "Conversación"

        Target.Offset(0, 1).Value = Now

    Case "Propuesta"

        Target.Offset(0, 2).Value = Now

    Case "Venta"

        Target.Offset(0, 3).Value = Now

    Case Else

        MsgBox "Has introducido un valor incorrecto en la columna A."

        Target.ClearContents

    End Select

End If

End Sub

 

Un saludo

guarnini
Imagen de guarnini
Offline
última acción: Hace 5 años 3 meses
alta: 18/12/2018 - 15:14
Puntos: 45
Muchísimas gracias por

Muchísimas gracias por decirme al detalle dónde me he equivocado y cómo se haría y por tu macro final, funciona genial.

En este post has dado una gran lección de cómo enseñar. Muchas gracias de verdad :)

PD:voy a intentar corregir el MsgBox porque entra en bucle

Cron
Imagen de Cron
Offline
última acción: Hace 8 meses 2 días
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntos
alta: 23/06/2010 - 12:30
Puntos: 12735
Gracias Tienes razón con el

Gracias

 

Tienes razón con el bucle. Es fallo mío, porque si modificas la celda en el código, también salta la misma macro que detecta que has modificado la celda. Y como el valor "celda vacía" no es ninguno de los valores que hemos prefijado, te vuelve a decir que no funciona y tal.

Esto se puede corregir de dos maneras. Una es admitir también el valor en blanco. Se haría así: 

    Select Case Target.Value

    Case "Conversación"

        Target.Offset(0, 1).Value = Now

    Case "Propuesta"

        Target.Offset(0, 2).Value = Now

    Case "Venta"

        Target.Offset(0, 3).Value = Now

    Case ""

    Case Else

        MsgBox "Has introducido un valor incorrecto en la columna A."

        Target.ClearContents

    End Select

Es decir, solo con introducir un caso en el que admita "" y no haga nada dentro sería suficiente.

Por otra parte, hay otra manera más elegante de hacer lo mismo. Sería así:

    Case Else

        MsgBox "Has introducido un valor incorrecto en la columna A."

        Application.EnableEvents = False

        Target.ClearContents

        Application.EnableEvents = True

    End Select

De esta manera, cuando borras el contenido de la celda evitas que se lance el evento de nuevo.

Finalmente, existe otra posibilidad, que es que el usuario borre el valor de la celda. Si no lo quieres consentir, entonces utiliza esta última solución. Si sí que lo quieres consentir, deberías utilizar la primera opción. Yo te recomiendo utilizar ambas opciones a la vez, y podías hacer algo más, como borrar las celdas B, C y D si el usuario borra la A. Podría ser así:

Private Sub Worksheet_Change(ByVal Target As Range)

If (InStr(Target.Address, ":") = 0) And (InStr(Target.Address, ",") = 0) And (Target.Column = 1) Then

    Select Case Target.Value

    Case "Conversación"

        Target.Offset(0, 1).Value = Now

    Case "Propuesta"

        Target.Offset(0, 2).Value = Now

    Case "Venta"

        Target.Offset(0, 3).Value = Now

    Case ""

        Application.EnableEvents = False

        Range(Target, Target.Offset(0, 3)).ClearContents

        Application.EnableEvents = True

    Case Else

        MsgBox "Has introducido un valor incorrecto en la columna A."

        Application.EnableEvents = False

        Target.ClearContents

        Application.EnableEvents = True

    End Select

End If

End Sub

guarnini
Imagen de guarnini
Offline
última acción: Hace 5 años 3 meses
alta: 18/12/2018 - 15:14
Puntos: 45
Mil gracias

Ha quedado perfecta la macro. Voy a hacer un curso intensivo de macros porque veo que no sé casi nada.

Muchas gracias por tu ayuda fenómeno!