Oftentimes I have a range filled with formulas that as such work correctly, but need an error checking mechanism. Take for example the following simple situation:
If cell A3 contains no data or a zero, the result in C3 will be #DIV/0!
If you don't want the errors to show, the solution is simple: test for zero:
But I tend to have much more complicated formulas, in which it is not always just one cell that might cause an error of some sort, so I need to wrap the entire formula in an error check. I thought it might be useful to have a generic little tool that lets you specify how to wrap the current function in a cell with an error test.
Here is the first try at a subroutine:
Dim oCell As Range
Dim sFormula As String
Dim sInput As String
Static sFormulaTemplate As String
If sFormulaTemplate = "" Then
sFormulaTemplate = "=IF(ISERROR(_form_),"""",_form_)"
sInput = InputBox("Enter base formula", , sFormulaTemplate)
If sInput = "" Then Exit Sub
sFormulaTemplate = sInput
For Each oCell In Selection
sFormula = Replace(sFormulaTemplate, "_form_", Right(oCell.Formula, Len(oCell.Formula) - 1))
oCell.Formula = sFormula
So how does this work?
You select the cells with the formulas that need worked over.
Then run the sub. An Input box is shown with a default template to change the formula:
What this means is that the current formula in the cell is going to be placed at each location where it says "_form_". Edit the formula template to match your need and click OK.
Because sFormulaTemplate is declared as Static, the utility remembers the template for a next time you use it.