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
- '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
- 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!