• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 976
  • Last Modified:

Excel - How to convert cell values into comments

I have found a VBA program that will take the comments in certain cells and display them onto a separate worksheet - noting the cell reference number, the cell value and the comment.  However, I would like to reverse this capability.  I would like to take a row indicating the cell reference, the cell value, and the comment, and when I run the program, it will insert the comment to the very cell referenced on that row.  For instance:

Cell Reference      Name      Cube/Office
$C$5            John Doe      South 112

Running the VBA program would then go to the cell referenced C5, insert "John Doe" as its value, and insert a comment "South 112".

Is this possible?
  • 2
1 Solution
   Range("C5").Value = "John Doe"
    Range("c5").Comment.Visible = False
    Range("c5").Comment.Text Text:=Range("d5").Value
Here is a macro that will put comments in Sheet1 for cell addresses starting with A2 on the active worksheet. The target cell value and comment text are drawn from the adjacent columns.
Sub ReverseComments()
Dim cel As Range, rg As Range, targ As Range
Application.ScreenUpdating = False
Set rg = Range("A2")    'Top left cell with data
Set rg = Range(rg, Cells(Rows.Count, rg.Column).End(xlUp)).Resize(, 3) 'All the data in those three columns
With Worksheets("Sheet1")       'Target worksheet
    On Error Resume Next
    For Each cel In rg.Cells
        If cel <> "" Then
            Set targ = Nothing
            Set targ = .Range(cel.Value)
            If Not targ Is Nothing Then
                targ.Value = cel.Offset(0, 1)
                targ.AddComment cel.Offset(0, 2).Value
            End If
        End If
    On Error GoTo 0
End With
Application.ScreenUpdating = True
End Sub

Open in new window

kristibigoAuthor Commented:
Beautiful!  Thank you!
kristibigoAuthor Commented:
Thank you for your quick response.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now