Results 1 to 2 of 2
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    List Formulas (XP)

    Is it possible via code to create a list of cells that contain formulas and their respective formulas>

    Example:

    <table border=1><td>Cell</td><td>Formula</td><td>E15</td><td>=Z22</td></table>


    Thanks,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List Formulas (XP)

    Try this:
    <code>
    Sub ListFormulas()
    Dim wshSrc As Worksheet
    Dim wshTrg As Worksheet
    Dim oCell As Range
    Dim r As Long
    On Error GoTo ErrHandler
    Set wshSrc = ActiveSheet
    Set wshTrg = Worksheets.Add
    For Each oCell In wshSrc.UsedRange.SpecialCells(xlCellTypeFormulas)
    r = r + 1
    wshTrg.Cells(r, 1) = oCell.Address(False, False)
    wshTrg.Cells(r, 2) = "'" & oCell.Formula
    Next oCell
    wshTrg.Range("A1:B1").EntireColumn.AutoFit
    Exit Sub
    ErrHandler:
    MsgBox Err.Description, vbExclamation
    End Sub</code>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •