viernes, 29 de enero de 2010

Modificar fórmulas de una Hoja con una Macro

Descargar el fichero versión 2007: modificaformula.xlsm

Modificar una fórmula que se encuentra en una celda se pude hacer editando la fórmula (por ejemplo con F2) y realizando a mano las modificaciones. Si deseamos modificar un gran conjunto de celdas que contienen fórmulas con cierta estructura, podemos utilizar Buscar y Reemplazar, aunque esta labor puede que no sea perfectamente homogénea para todas las celdas. Lo ideal sería disponer de una macro que efectuara los cambios en las fórmulas. Presentamos un caso práctico donde ésto se realiza con una macro.

Disponemos de apuntes contables por meses en diferentes hojas: desde el Mes1 al Mes12. Disponemos de otra hoja que se denomina Inicio, que contiene los saldos de apertura. En la primera hoja denominada TOTAL deseamos sumar los saldos de Inicio con los saldos del Mes que el usuario elija pulsando con el ratón sobre unos botones de radio, también denominados botones de opción.

  • Si el mes seleccionado en el botón de opción es el Mes1, la fórmula de la celda B4 de la hoja TOTAL será: =+Inicio!B4+'Mes1'!B4
  • Si el mes seleccionado es el Mes12 la fórmula será: =+Inicio!B4+'Mes12'!B4


Al efectuar el cambio de botón se puede apreciar el cambio de valores numéricos en las celdas de la hoja TOTAL.



Código:

Sub muta()
Dim formula As String, formulanew As String
Dim celda As Range
Dim R As Range
Dim p, q, x, y As Byte, z, n As Long
Application.ActiveWorkbook.Worksheets("TOTAL").Activate
Set R = Range("B4:C14")
For Each celda In R.Cells
  'FormulaLocal captura la fórmula de la celda
  formula = celda.FormulaLocal
  'calculamos en que caracter comienza Mes
  p = InStr(formula, "Mes")
  'Tomamos la parte de la izquierda hasta Mes incluido
  x = Left(formula, p + 2)
  'El usuario selecciona un mes
  y = Range("Z1")
  'longitud de la formula
  n = Len(formula)
  'calculamos en que caracter se encuentra la 2ª !
  q = InStr(p, formula, "!")
  'Tomamos la parte derecha de la fórmula
  z = Right(formula, n - q + 2)
  'componemos la fórmula nueva
  formulanew = x & y & z
  'dejamos el nuevo valor de la formula
  celda.FormulaLocal = formulanew
Next
End Sub

4 comentarios:

  1. Adolfo, tu blog está buenísimo. Te cuento que lo consulto regularmente, solo para ver que es lo nuevo que has publicado, porque de seguro me sirve para mi actividad.
    Te mando un abrazo desde Mendoza, Argentina.

    ResponderEliminar
  2. Hola Adolfo, mi nombre Hugo. Primero que nada felicitarte por tu Blog, muy buena claridad en la explicación. Segundo, quería saber si existe una forma de mostrar un Tag que te muestre la sintaxtis de una función personalizada cuando la estamos llamando desde una celda determinada. Las funciones predefinidas que trae Excel muestran esta guía recordando los argumentos que se deben especificar mientras estamos capturando la función. Agradeceré tu pronta respuesta. Un saludo!!

    ResponderEliminar
  3. Adolfo, gracias por tantas publicaciones puesto que ayudas a mucha gente, lo de modificar fórmulas es interesante pero necesito saber si tienes el código para excel 2003

    ResponderEliminar
  4. Necesito una macro para agregar a una formula de la celda una formula antes si.error (esa formula) Alguien puede sugerirme como hacer eso?

    ResponderEliminar