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.

26 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
  8. Mil Gracias, justo esto era lo que andaba buscando para terminar mi graficadora de funciones (Un proyecto de la Universidad).
    Nunca he programado en VBA de Excel, pero si en otros lenguajes y con lo bien explicado que está tu código he entendido a la primera, aunque lo hayas escrito hace 3 años aún funciona en Office 2016.
    Enserio muchas gracias me has salvado el pellejo.

    ResponderEliminar
    Respuestas
    1. ;-)
      muchas gracias a tí... me alegro te hay servido
      Saludos

      Eliminar
  9. Hola, como puedo pasar esta formula de excel a Visual Basic?
    El resultado tiene que estar en la celda B16. Esta es la formula:
    =SI(D4="chs",BUSCARV(B11,BASE1!C2:I153,3), SI(D4="chr",BUSCARV(B11,BASE1!C2:I153,4)))

    ResponderEliminar
    Respuestas
    1. Hola Diana
      en tu macro:
      Range("B16").formulalocal="=SI(D4=""chs"",BUSCARV(B11,BASE1!C2:I153,3), SI(D4=""chr"",BUSCARV(B11,BASE1!C2:I153,4)))"

      Saludos

      Eliminar
  10. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
    Respuestas
    1. Este comentario ha sido eliminado por el autor.

      Eliminar
  11. Hola
    Necesito hacer una macro donde la columna R tiene montos (formulas) y algunos están en cero. En la columna S y T hay montos (formulas) que necesito se transformen en valores, copiando y pegando solos los que contengan en su columna R montos (no considerar los valores en cero) al momento de guardar el archivo. El archivo contiene varias hojas. Solo necesito que la Macro actué en una solo de ellas.

    Favor si me puedes ayudar.
    Saludos

    ResponderEliminar
    Respuestas
    1. Hola Tamara,
      podrías recorrer el rango de la columna R aplicando una condición para saber si hay o no valor en cada celda.. en caso afirmativo convertir el valor de esa fila para las columnas S y T.
      Algo así:
      for each celda in range(R1:R100)
      if celda.value<>0 then
      cells(celda.ror,"S").value=cells(celda.ror,"S").value
      cells(celda.ror,"T").value=cells(celda.ror,"T").value
      end if
      next celda

      podrías asociarlo al evento del Workbook beforeClose

      Saludos

      Eliminar
  12. hola yo deseo quitar formulas de sumar.si de una hoja excel pero contiene otras formulas de suma normal que necesito se queden como puedo hacer la condicionante

    ResponderEliminar
    Respuestas
    1. Hola
      tendrías que hacer un reemplazamiento parcial del contenido de la celda, quitando la parte de SUMAR.SI(...)
      quizá eliminando hasta le primer '+' o similar, depende de cómo esté introducida la fórmula

      Saludos

      Eliminar

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