martes, 1 de julio de 2014

VBA: Cómo convertir nuestras fórmulas en valores.

Hoy veremos un sencillo procedimiento para convertir cualquier fórmula (dentro de una rango concreto) en valores...
Lo primero será tener claro qué fórmulas localizadas en qué celdas son las que nos interesa convertir en valores., y para nuestro ejemplo serán las que se encuentran en las celdas C2,D5 y E4:

VBA: Cómo convertir nuestras fórmulas en valores.



Con la propiedad .HasFormula detectaremos si nuestras celdas contienen algún tipo de fórmula...
En un módulo del proyecto de VBA para nuestro Libro de trabajo, dentro del Editor de VB:

Sub ConvierteFormulaenValor()
'recorremos las celdas de nuestro rango elegido
For Each rngcell In Range("C2,D5,E4")
    'añadimos el control que detecta si la celda tiene Fórmula
    If rngcell.HasFormula Then
        'cuando la tenga asignamos a la celda el valor que tenga....
        rngcell.Value = rngcell.Value
    End If
Next rngcell
End Sub



Tras ejecutar nuestra macro obtenemos el siguiente resultado...lógicamente sólo se habrá producido el cambio en las celdas indicadas!!

VBA: Cómo convertir nuestras fórmulas en valores.

16 comentarios:

  1. Es mucho más fácil seleccionar el rango, copiar y pegar los valores...

    ResponderEliminar
    Respuestas
    1. Hola, muchas gracias por tu comentario...
      Sería cierta tu afirmación en casos simples, pero imagina que no son tres casos si no tres mil, y que entre las celdas a convertir tienes otras que te interesa dejarlas como están, i.e, con fórmulas... es ahí donde este sencillo proceso toma sentido.

      Un saludo

      Eliminar
    2. Entonces porque en el código propuesto se especifia una a una las celdas que se quieren cambiar?
      ....
      For Each rngcell In Range("C2,D5,E4")
      ...

      Como tu has dicho: "Imagina que no son tres casos sino tres mil"
      Salu2

      Eliminar
    3. Hola de nuevo...
      veo que eres como Santo Tomás... necesitas ver para creer.

      Bastaría realizar algún loop FOR NEXT sobre las 3.000 celdas, con una condición IF..THEN o SELECT CASE para excluir las tres celdas.

      Saludos

      Eliminar
  2. Anonimo, es para VBA principalmente, en algo simple es mejor lo que dices, pero por ejemplo en un archivo con 100,000 filas y 24 columnas todas calculadas, y que desees cambiar sus formulas a valor, donde solo cambias aquellas que cumple sierta condicion para mantener estatico el valor, imaginate recorrer una a una para buscar y cambiar, mejor con macro pongo el criterio de exclusion y cambio todas en segundos.

    ResponderEliminar
  3. De acuerdo a este ejemplo, como se podria modificar para que convierta solo las formulas que tuviesen datos y se mantengan en formulas las que no consignan datos.

    ResponderEliminar
    Respuestas
    1. Hola Nilto,
      encantado de saludarte igualmente.

      entiendo que con tener dato te refieres a que sea un resultado diferente de cero... si es así, bastaría con añadir al condicional:
      If rngcell.HasFormula and rngcell.valu<>0 Then

      Espero te funcione
      Un cordial saludo

      Eliminar
  4. Gracias Ismael por tu respuesta, A tu condicional lo modifique para que se adapte a mis datos.
    If rngcell.HasFormula and rngcell.value >=0 Then 'Tengo valores por ejemplo 1 es varon y 0 es mujer'

    Pero despues de convertir las formulas en valores me muestra un error "13", No coinciden los datos, que estoy haciendo mal

    ResponderEliminar
    Respuestas
    1. Tendría que ver los datos..
      ¿puedes enviarme el fichero a excelforo@gmail.com?

      Eliminar
  5. Muchas gracias, estimado.
    Para muchos parece un código sencillo, pero cuando lo combinamos con otros códigos se vuelve indispensable...
    Muchos saludos.

    ResponderEliminar
  6. Hola Ismael, quiero aplicar el macro que explicaste pero me da error 438 en tiempo de ejecucion y pinta de amarillo la linea if.., soy novato en esto, te agradeceria si lo miras y me decis que esta mal..

    Sub ConvierteFormulaenValor()
    'recorremos las celdas de nuestro rango elegido
    For Each rngcell In Range("C3:D300")
    'añadimos el control que detecta si la celda tiene Fórmula
    If rngcell.HasFormula And rngcell.valu <> 0 Then
    'cuando la tenga asignamos a la celda el valor que tenga....
    rngcell.Value = rngcell.Value
    End If
    Next rngcell
    End Sub

    Gracias..

    ResponderEliminar
    Respuestas
    1. Hola Camilo,
      si lo has copiado tal cual, mira la línea:
      If rngcell.HasFormula And rngcell.valu <> 0 Then
      deberia ser:
      If rngcell.HasFormula And rngcell.value <> 0 Then
      Saludos

      Eliminar
  7. Ismael buena tarde, admiro el trabajo y lo bueno que sos para esto de las macros, yo soy hasta ahora un aficionado con ganas de llegar a tener si quiera la mitad de tu basta experiencia en este tema, quería preguntarte como sería el código en caso tal si solo quiero remplazar las celdas que contengan la función "buscarv" dentro de una hoja específica. Muchas gracias!!!

    ResponderEliminar
    Respuestas
    1. Hola Jorge,
      tendrías que emplear la propiedad .Formula o .Formulalocal para recuperar la fórmula de la celda, obtendrías algo así, como string:
      =BUSCARV(C6;A6:B9;2;0)
      y con algún tratamiento tendrás que condicionar que si los primeros caracteres por la izquierda coinciden con =BUSCARV entonces reemplazar por lo que quieras, su valor u otra cosaa.
      Un saludo

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.