I'm a bit rusty on my VBA, but here goes:
I have a workbook that has thousands of INDEX MATCH formulas that are drastically slowing down the performance of excel. Ideally what I'd like to do is write a macro that will replace the INDEX MATCH formulas in a specific selection with the direct cell reference. For example, replace "=INDEX(A:A, MATCH(D2, C:C, 0))" with "=A5". It seems that a find and replace macro using the CELL("address" function would make the most sense here but I'm open to other ideas.
I found the below code for adding IFERROR to a given formula:
Sub WrapIfError() 'PURPOSE: Add an IFERROR() Function around all the selected cells' formulas 'SOURCE: www.TheSpreadsheetGuru.com Dim rng As Range Dim cell As Range Dim x As String 'Determine if a single cell or range is selected If Selection.Cells.Count = 1 Then Set rng = Selection If Not rng.HasFormula Then GoTo NoFormulas Else 'Get Range of Cells that Only Contain Formulas On Error GoTo NoFormulas Set rng = Selection.SpecialCells(xlCellTypeFormulas) On Error GoTo 0 End If 'Loop Through Each Cell in Range and add =IFERROR([formula],"") For Each cell In rng.Cells x = cell.Formula cell = "=IFERROR(" & Right(x, Len(x) - 1) & "," & Chr(34) & Chr(34) & ")" Next cell Exit Sub 'Error Handler NoFormulas: MsgBox "There were no formulas found in your selection!" End Sub
It seems like I should be able to sub out the "=IFERROR(" with "=CELL(""address"", " but I keep getting a 1004 error.
Any help would be greatly appreciated!