Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    copy a formula into a comment . (Excel 95)

    I have this formula in Cell D21. How can I just "copy" it into a comment?

    CellText="+SUM("+CellRange+")"
    ActiveSheet.Cells(RowCount,ColCount).Value = celltext

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: copy a formula into a comment . (Excel 95)

    If you select the cell with the formula, highlight the cell contents in the Formula Bar, press Ctrl-C, then insert comment, and press Ctrl-V. The formula text should now form the comment.

    If you need a VBA solution, I'm sure we can come up with something.

    Andrew C

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy a formula into a comment . (Excel 95)

    Might not be a bad idea. That is a VBA solution.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: copy a formula into a comment . (Excel 95)

    The following code will do what you want with the active cell<pre> Sub FormulaToComment()
    With ActiveCell
    .AddComment
    .Comment.Text Text:=ActiveCell.Formula
    End With
    End Sub</pre>

    Andrew

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: copy a formula into a comment . (Excel 95)

    As that last code will generate an error if a comment already exists, it might be bett to include some error handling <pre> Sub FormulaToComment()
    On Error Resume Next
    With ActiveCell
    .AddComment
    .Comment.Text Text:=ActiveCell.Formula
    End With
    On Errtor GoTo 0
    End Sub</pre>

    That modification should allow you to run the code again should you change the formula in the cell, as the comment would not be updated automatically.

    Andrew C

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy a formula into a comment . (Excel 95)

    Andrew,

    Apart from the typo "On Errtor" <img src=/S/grin.gif border=0 alt=grin width=15 height=15>, I have a dislike for using On Error- unless there is no other way. There's always the danger that you'll miss out on the fact that the error might be caused by something else.

    One way to avoid the error would be to put ".ClearComments" before the ".AddComment".

    If that was not desirable, the next best thing would be to explicitly trap just the one error:

    <pre>Sub FormulaToComment()
    On Error GoTo ErrorHandler
    With ActiveCell
    .AddComment
    .Comment.Text Text:=ActiveCell.Formula
    End With

    Exit Sub
    ErrorHandler:
    If Err.Number <> 1004 Then
    MsgBox "This is a real error"
    End If
    End Sub
    </pre>

    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: copy a formula into a comment . (Excel 95)

    Thanks Geoff, I completely overlooked the .ClearComments, but as it is ignored if no comment exists, the following should cover either creating or updating the comment :<pre> Sub FormulaToComment()
    With ActiveCell
    .ClearComments
    .AddComment
    .Comment.Text Text:=ActiveCell.Formula
    End With
    End Sub</pre>

    Andrew

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: copy a formula into a comment . (Excel 95)

    "Err.Number <> 1004" may be too wide a net. This version forces an append or new comment, and needs more work (like peeling off the last formula), but I'm hungry. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Hunger makes haste. Edit in red.

    Sub FormulaToComment()
    Dim strPreComment As String
    With ActiveCell
    On Error Resume Next
    strPreComment = .Comment.Text
    If Err.Number > 91 Then
    MsgBox "An error has occurred"
    <font color=red>Exit Sub</font color=red>
    ElseIf Err.Number = 91 Then
    .AddComment
    End If
    .Comment.Text Text:=strPreComment & _
    IIf(strPreComment = "", "", vbLf) & ActiveCell.Formula
    End With
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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