I had a colleague ask me an interesting question the other day. He has repeating ranges of data that are structured similarly, and he wants to perform calculations on each block. The formulas can get kind of long and error prone. When he catches an error, he has to go back and Copy – Paste Special – Formulas again for each range. … He asks is there any quick formula to use another cell’s formula. For example if A1 has =SUM(B2:D3), typing =FOO(A1) in D2 would return the same as =SUM(E3:G4).
Oh, I’m familiar with writing buggy formulas and feeling like I’m forever fixing and copying. I think any solution is going to create so much overhead that it will be better to stick with copying. However, as Jamie Collins would say, it’s the Everest complex – do it because it’s there.
My first thought, of course, would be to parse the formula by looping through the Precedents property of the range with the formula. It would limit it’s effectiveness to formulas that only referred to ranges on the same sheet, but I knew parsing with string functions would be a losing proposition. Fortunately, I came to my senses and remembered the ever-handy ConvertFormula method of the Application object. Here’s my first hack at it:
Dim sForm As String
sForm = Application.ConvertFormula(rtarget.Formula, xlA1, xlR1C1, , rtarget)
sForm = Application.ConvertFormula(sForm, xlR1C1, xlA1)
InheritFormula = Application.Evaluate(sForm)
The inner workings of ConvertFormula have always been a mystery to me. Maybe if I new why it was invented, I would have a better of understanding of why it works the way it does. Like why the FromReferenceStyle is required but the ToReferenceStyle is optional. Anyway, the first ConvertFormula converts the formula (well, the name works) from A1 to R1C1 relative to the target cell. So
=SUM(A3:A5) gets converted to
=SUM(RC:RC). The second ConvertFormula converts it back to A1, presumably relative to the calling cell.
I’m sure this will break with many, many formulas. Array formulas come to mind and something that probably wouldn’t work too well with the Evaluate method. Come to think of it, maybe an array formula is what Jason’s “colleague” needs. (We know it’s really you Jason.) He could highlight the range, edit the formula, and control+shift+enter to fill it in the range. I suppose that’s not much better than just copying and pasting.
Below are the formulas on which I tested it. You’ll notice that I very cowardly didn’t include any complex or difficult formulas. I really don’t want to know where it doesn’t work.