An experiment in applied Excel formulas
October 9, 2009 • JP • No Comments • Rate This Article
• Links to this article
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
To get the formula from the source cell, I wrote the following function:
' returns formula from cell
GetCellFormula = Mid$(sourceCellValue, 2, _
WorksheetFunction.Find("(", sourceCellValue) - 2)
End Function
To return a Range Object, I encapsulated the usual method:
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:
For Each cell In targetRng
targetCellContents = Right$(cell.Formula, Len(cell.Formula) - 1)
cell.Formula = "=" & sourceFormula & "(" & targetCellContents & ")"
Next cell
Excel ·
Rate This Article:
(1 votes, average: 4.00 out of 5)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Leap Year functions in VBA
Next Post: My review of JMT Excel Utilities
Looping's not a sin if it's the only way to get something done. If each cell has a distinct formula, then it's the only (reliable) way.