Find and Replace - extraction

[Sheripres]

Okay...I never asked this question and I cannot find anything on the internet.

Just like we can extract and paste Name Ranges, is there a way to extract the list of formulas from Find?

On my main spreadsheet, there are many formulas, vlookups, if statements, etc. I did a Find on "!". By finding the exclamation mark, I clicked the FIND ALL and see the entire list of formulas, vlookups, etc.

I want to extract that entire list and sort out the various sheet tabs that are associated with that list.

I tried everything and I am just tossing it out there. Not a big deal, but I sure would love it.

Thanks!

 

[NdNoviceHlp]

Hi Sheripres. You didn't specify output format. This seems to work for identifying all formulas in the wb with msgbox output. You may want to trial it with a wb with only a few formulas. HTH. Dave

Sub FindFormula()
For Each sht In ThisWorkbook.Sheets
For Each c In sht.UsedRange
If InStr(c.Formula, "=") Then
MsgBox sht.Name & "  " & c.Address
End If
Next c
Next sht
End Sub

 

[Artik]

Probably

Sub FormulasInSheet()
    Dim rngF As Range
    Dim rng As Range
    Dim vResult As Variant
    Dim Nm As Name
    Dim i As Long
    Dim wksN As Worksheet
    Dim wksA As Worksheet

    On Error Resume Next
    Set rngF = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0

    If rngF Is Nothing Then
        MsgBox "There are no formulas in this sheet.", vbExclamation
        Exit Sub
    End If

    Set wksA = ActiveSheet

    ReDim vResult(1 To rngF.Cells.Count + 1, 1 To 4)

    i = 1
    vResult(i, 1) = "Name"
    vResult(i, 2) = "Cell"
    vResult(i, 3) = "Value"
    vResult(i, 4) = "Formula"

    For Each rng In rngF.Cells
        If Not IsEmpty(rng.Value) Then
            i = i + 1
            vResult(i, 2) = rng.Address
            vResult(i, 3) = rng.Value
            vResult(i, 4) = "'" & rng.FormulaLocal

            For Each Nm In ActiveWorkbook.Names
                If rng.Address(External:=True) = Nm.RefersToRange.Address(External:=True) Then
                    vResult(i, 1) = Nm.Name
                    Exit For
                End If
            Next Nm
        End If
    Next rng

    With ActiveWorkbook
        Set wksN = .Worksheets.Add(After:=.Sheets(.Sheets.Count))
    End With
    wksN.Range("A1").Value = "Sheet: " & wksA.Name
    wksN.Range("A3").Resize(i, 4).Value = vResult

End Sub

Artik

 

[Peter_SSs]

Would something like this be any use? See post #2 in that thread, including sample output.

 

[Sheripres]

Probably

Sub FormulasInSheet()
    Dim rngF As Range
    Dim rng As Range
    Dim vResult As Variant
    Dim Nm As Name
    Dim i As Long
    Dim wksN As Worksheet
    Dim wksA As Worksheet

    On Error Resume Next
    Set rngF = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0

    If rngF Is Nothing Then
        MsgBox "There are no formulas in this sheet.", vbExclamation
        Exit Sub
    End If

    Set wksA = ActiveSheet

    ReDim vResult(1 To rngF.Cells.Count + 1, 1 To 4)

    i = 1
    vResult(i, 1) = "Name"
    vResult(i, 2) = "Cell"
    vResult(i, 3) = "Value"
    vResult(i, 4) = "Formula"

    For Each rng In rngF.Cells
        If Not IsEmpty(rng.Value) Then
            i = i + 1
            vResult(i, 2) = rng.Address
            vResult(i, 3) = rng.Value
            vResult(i, 4) = "'" & rng.FormulaLocal

            For Each Nm In ActiveWorkbook.Names
                If rng.Address(External:=True) = Nm.RefersToRange.Address(External:=True) Then
                    vResult(i, 1) = Nm.Name
                    Exit For
                End If
            Next Nm
        End If
    Next rng

    With ActiveWorkbook
        Set wksN = .Worksheets.Add(After:=.Sheets(.Sheets.Count))
    End With
    wksN.Range("A1").Value = "Sheet: " & wksA.Name
    wksN.Range("A3").Resize(i, 4).Value = vResult

End Sub

Artik

 

[Sheripres]

Artik, this is perfect! Thank you so much.

In case anyone is scratching their heads why I want this, I just want to have a list of which sheet tab are connected to the main pricing sheet tab so I can remove the sheet tabs that are not being used. This was someone else's project and they never cleaned it up in 10 years, so by having this list, it will save me time instead of looking through the pricing sheet tab for vlookups, formulas, references to other sheet tabs, etc.

Have a great day! Thanks again; this is my favorite site to go to when I have issues with Excel!

 

[Sheripres]

Would something like this be any use? See post #2 in that thread, including sample output.

Thank you for responding. Artik's VBA is what I needed. I did respond to his to explain my purpose for this.

I am going to keep your VBA for future use in another scenario!

Thanks!

 

[Peter_SSs]

Thank you for responding. Artik's VBA is what I needed.

You're welcome. Glad you got a successful outcome.