Posts by rjengelking

Hello,

The index match formulas save a lot of time up front to ensure the correct data is being pulled in, otherwise I would save myself the time and just direct link in the first place .

The reason I posted the IFERROR macro is because I believe it's doing something very close to what I'm trying to accomplish. That is, to go through all of my formulas and add a string at the beginning of each to get the desired result. In my case it's CELL("address" instead of IFERROR.

Thanks.

Hello!

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!

Thanks,

Rob