Excel formula printing 5

[LordGarsdale]

Hi all
I have a large spreadsheet with lots of formulae scattered around on it.
Is there any existing program (VB or VBA ?) that will just print out a list of cells that have a formula in and the formula.
I have tried the 'old' method of formula - view - print but due to formula lengths, I needed to make all of the columns very, very wide and the hardcopy was almost impossible to read.
Here's hoping otherwise guess what I'll be doing over the Christmas break :-(

John

 

[sawedoff]

Go to this link and follow the instructions.

http://j-walk.com/ss/excel/tips/tip37.htm

Sawedoff

 

[LordGarsdale]

Thanks sawedofff, that looks exactly what I need. Happy Christmas and have a star on me !

John

 

[sawedoff]

Thanks. Have a good holiday.



Sawedoff

 

[dcompto]

My thanks, too, sawedoff--and another star.

 

[macropod]

hi folks,

Here's some code I use for doing the same kind of thing, except that mine appends the formula to the relevant cell as a comment. If you select a particular range, only that range is processed, otherwise all formulae on the worksheet are processed.

Cheers

Sub AddFormulaToComment()
Dim CommentRange As Range, TargetCell As Range
Call MacroEntry
'skip over errors caused by trying to delete comments in cells with no comments
On Error Resume Next
'If the whole worksheet is selected, limit action to the used range.
If Selection.Address = Cells.Address Then
    Set CommentRange = Range(ActiveSheet.UsedRange.Address)
Else
    Set CommentRange = Range(Selection.Address)
End If
'If the cell contains a formula, make it a comment.
For Each TargetCell In CommentRange
    With TargetCell
        'check whether the cell has a formula
        If Left(.Formula, 1) = "=" Then
            'delete any existing comment
            .Comment.Delete
            'add a new comment
            .AddComment
            'copy the formula into the comment box
            .Comment.Text Text:=.Formula
            'display the comment
            .Comment.Visible = True
            With .Comment.Shape
                'automatically resizes the comment
                .TextFrame.AutoSize = True
                'position the comment adjacent to its cell
                If TargetCell.Column < 254 Then .IncrementLeft -11.25
                If TargetCell.Row <> 1 Then .IncrementTop 8.25
            End With
        End If
    End With
Next
MsgBox "    To print the comments, choose" & vbCrLf & _
       "  File|Page Setup|Sheet|Comments," & vbCrLf & _
       "then choose the required print option.", vbOKOnly
Call MacroExit
End Sub

 

[LordGarsdale]

Hi macropod

Thanks for an alternative. I think I can find a use for this too, so a star's on its way.
John

 

[BoniR]

A star for both of you...
I have had this on my list to research for a while...so you both saved me sometime!
Boni
)


Boni J. Rychener
Hammerman Associates, Inc.
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter

http://www.hammerman.com

800-783-2269