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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

   Range("C5").Value = "John Doe"
    Range("c5").Comment.Visible = False
    Range("c5").Comment.Text Text:=Range("d5").Value
byundtMechanical EngineerCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kristibigoAuthor Commented:
Beautiful!  Thank you!
kristibigoAuthor Commented:
Thank you for your quick response.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.