Excel VBA to Convert All Indirect Functions to Standard Reference

I've got a large spreadsheet with multiple indirect formulas built into it. I need a macro to convert all of these indirect formulas into their corresponding standard references.

Cell C1 Contains "B3"

i.e. =Indirect(C1)

I need a macro to convert the indirect formula to =B3.

This is a very basic example the file has formulas in it such as :
=SUMIF(INDIRECT("'"&$A$2&"'!$I$9:$XFD$9"),F$7,INDIRECT("'"&$A$2&"'!$I$84:$XFD$84"))

Open in new window


I've seen a lot of posting from folks but can't seem to get any of them to work.
thomas-sherrouseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
Here is a not terribly sophisticated sub that will convert INDIRECT references into regular cell references. It looks for INDIRECT( in cell formulas, then seeks the next right parenthesis. The material in between those parentheses is then evaluated and the formula updated.

If your INDIRECT formula has embedded right parentheses, the macro will fail. It will fail if there is one or more spaces between INDIRECT and the left parenthesis. It will also fail if INDIRECT is a literal in the formula (i.e. not a function references).

As written, the macro works on every worksheet in the active workbook.
Sub NoINDIRECT()
Dim ar As Range, cel As Range, celHome As Range, rg As Range
Dim ws As Worksheet
Dim frmla As String, IndirectRef As String
Dim j As Long, k As Long
Dim bIndirect As Boolean
Set celHome = ActiveCell
For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    Set rg = Nothing
    On Error Resume Next
    Set rg = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    
    If Not rg Is Nothing Then
        For Each ar In rg.Areas
            For Each cel In ar.Cells
                frmla = cel.Formula
                bIndirect = False
                Do
                    j = InStr(1, frmla, "INDIRECT(")
                    If j = 0 Then Exit Do
                    bIndirect = True
                    k = InStr(j, frmla, ")")
                    If k > 0 Then
                        IndirectRef = Mid(frmla, j + 9, k - j - 9)
                        IndirectRef = Application.Evaluate(IndirectRef)
                    End If
                    frmla = Left(frmla, j - 1) & IndirectRef & Mid(frmla, k + 1)
                Loop
                If bIndirect Then cel.Formula = frmla
            Next
        Next
    End If
Next
Application.Goto celHome
End Sub

Open in new window


If the macro fails, please post a sample workbook showing the type of formulas that cause the macro to fail.
0
byundtMechanical EngineerCommented:
I made the code a little more robust. It will not make the replacement for INDIRECT if there is an error in doing so. It may also run faster because I turned calculation off while the macro runs.
Sub NoINDIRECT()
Dim ar As Range, cel As Range, celHome As Range, rg As Range, rgIndirect As Range
Dim ws As Worksheet
Dim frmla As String, IndirectRef As String, oldFrmla As String
Dim j As Long, k As Long
Application.Calculation = xlCalculationManual
Set celHome = ActiveCell
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    Set rg = Nothing
    Set rg = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
    
    If rg Is Nothing Then
        Err.Clear
    Else
        For Each ar In rg.Areas
            For Each cel In ar.Cells
                j = 0
                Do
                    frmla = cel.Formula
                    oldFrmla = frmla
                    j = InStr(j + 1, frmla, "INDIRECT(")
                    If j = 0 Then Exit Do
                    k = InStr(j, frmla, ")")
                    If k > 0 Then
                        IndirectRef = Mid(frmla, j + 9, k - j - 9)
                        IndirectRef = Application.Evaluate(IndirectRef)
                    End If
                    If Err.Number = 0 Then
                        Set rgIndirect = Nothing
                        Set rgIndirect = Range(IndirectRef)
                        If Not rgIndirect Is Nothing Then
                            frmla = Left(frmla, j - 1) & IndirectRef & Mid(frmla, k + 1)
                            
                            cel.Formula = frmla
                            If Err.Number <> 0 Then
                                Err.Clear
                                cel.Formula = oldFrmla
                            End If
                        End If
                    Else
                        Err.Clear
                    End If
                Loop
            Next
        Next
    End If
Next
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.Goto celHome
End Sub

Open in new window


If the macro fails, please post a sample workbook showing the type of formulas that cause the macro to fail.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thomas-sherrouseAuthor Commented:
Awesome ! Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.