Show a Formula in Another Cell

[tommy bak]

Well, my code actually looks a lot like Zack's code, but I'll post it anyway.
For non-us users it's pracical to have the comment in their language.
This code will change all comments in selection (if any)
I think the only difference is the use of FormulaLocal and AutoSize.

Sub show_formula_in_comment()
Dim rCell As Range, OldText As String
Application.ScreenUpdating = False
On Error Resume Next
For Each rCell In Selection.SpecialCells(xlCellTypeFormulas, 23)
    With rCell
        .AddComment
        With .Comment
            .Text Text:=CStr(rCell.FormulaLocal)
            .Shape.TextFrame.AutoSize = True
            .Visible = True
        End With
    End With
Next
Application.ScreenUpdating = True
End Sub
 
'Sub to remove the comments 
Sub RemoveComments()
    Selection.ClearComments
End Sub

Tommy Bak