Add new formula to existing formulas on entire worksheet

T

T. Spina

Hi! I hope someone can help me with this. I have a worksheet where the
summary sheet pulls info from another 15 worksheets, and we were having
trouble getting the #N/A errors to hide when data wasn't entered into
the cells.

I found a formula that works, and I just need to know how to add it to
the entire sheet, especially since the other cells already contain the
original formula.


Here is an example of what the formula was like before:

=VLOOKUP('VP Summary'!A2,'Air Products'!$A$2:$C$28,3,FALSE)

This is what I had when I managed to add the other formula, and what I
need to do to the several hundred remaining cells:

=IF(ISNA(VLOOKUP('VP Summary'!A2,'Air
Products'!$A$2:$C$28,3,FALSE)),"",VLOOKUP('VP Summary'!A2,'Air
Products'!$A$2:$C$28,3,FALSE))

Any assistance would be greatly appreciated.

Theresa
 
G

Gord Dibben

To add the ISNA trap formula to all cells.

Select the range of cells then run this macro.

Sub NATrapAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(ISNA*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(ISNA(" & myStr & "),""""," & myStr & ")"
End If
End If
Next
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP
 
Top