An experiment in applied Excel formulas

October 9, 2009 by JP ·

As an exercise, I developed a set of routines that lets you apply a formula to a group of target cells. Actually, it's because I couldn't find anything similar anywhere else. It's ready to be made into an add-in, but currently it only works on formulas with one parameter (i.e. PROPER, AVERAGE, SUM, CLEAN, etc).

For example, if you have a group of cells with a given formula or value, and you want to change them to match the value of another cell, it's a tedious process to edit each one and change them. I find this to be a common problem.

For example, if you have ten cells (with or without formulas) and have another cell with the SUM formula, you can use this code to apply the SUM formula to those ten cells in a few short steps.

Screenshot

Apply Formulas

To get the formula from the source cell, I wrote the following function:

Function GetCellFormula(sourceCellValue As String) As String
' returns formula from cell
  GetCellFormula = Mid$(sourceCellValue, 2, _
    WorksheetFunction.Find("(", sourceCellValue) - 2)
End Function

To return a Range Object, I encapsulated the usual method:

Function GetRange(strRange As String) As Excel.Range
  On Error Resume Next
  GetRange = Excel.Range(strRange)
  ' if above fails, GetRange will = Nothing
End Function

And I even went ahead and broke one of my own rules about looping. Here's how I update the target cells with the source formula:

  ' apply formula to each target cell
  For Each cell In targetRng
  targetCellContents = Right$(cell.Formula, Len(cell.Formula) - 1)
  cell.Formula = "=" & sourceFormula & "(" & targetCellContents & ")"
  Next cell

Download sample workbook