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