Reverse Cell Value to Comment

I've modified the code below.  When I use it, it seems to pull the correct name associated with the cell reference, but the comment is off by one.  Example:

Cell Reference      FunctionName      FunctionC/O
$C$5      John Doe      XX-42
$D$5      1-HP*      1-HP*

In the cell which has John Doe's name is correct, but it is referencing 1-HP* in the comment instead of XX-42.

Here it the modified code below.  I've also attempted to include a resizing code within and I think it's working, but haven't had much of a chance to test it out since the more important issue hasn't been resolved. [I'm having a hard time understanding the code process]:

Sub ReverseComments_Click()
Dim cel As Range, rg As Range, targ As Range
Dim MyComments As Comment
Dim lArea As Long

Application.ScreenUpdating = False

Set rg = Range("I2")    '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("BHM Org Chart")       '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.Comment.Delete
                targ.AddComment cel.Offset(0, 2).Value
            End If
        End If
    Next
    On Error GoTo 0
End With

For Each MyComments In ActiveSheet.Comments
    With MyComments
      .Shape.TextFrame.AutoSize = True
      If .Shape.Width > 300 Then
        lArea = .Shape.Width * .Shape.Height
        .Shape.Width = 100
        ' An adjustment factor of 1.1 seems to work ok.
        .Shape.Height = (lArea / 100) * 1.1
      End If
    End With
  Next ' comment

Application.ScreenUpdating = True

End Sub
kristibigoAsked:
Who is Participating?

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

x
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.

ragnarok89Commented:
I don't quite follow what you want to do. You want to take the contents of a cell, and create a comment on that cell that contains the cell value? Example

Cell value = xyz
Cell has no comments
you run the macro...
cell value becomes blank
cell comment becomes xyz

Is this the goal, and could you perhaps attach a sample excel file?

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:
It is a little confusing. I apologize.  Let me see if I can illustrate it a little better:

The columns with information starts on I2.

 I                             J                           K
Cell Reference      Name                    Cube
$C$5                     John Doe              XX-42
$D$5                     1-HP*                    1-HP*

I want cell C5 to have the value "John Doe" noted with a comment "XX-42".  Instead, I have cell C5 with John Doe as the value and "1-HP*" as the comment.  The code seems to drop down one cell on the third column.  I have a value in every cell, so there are no blanks for which may be an issue.

Is it within the code?
kristibigoAuthor Commented:
I've figured it out. Some of my cells are "Merge & Center" and the latter cell referencing something else is taking precedence.  So for anyone who may have this same issue - check to see if your cell reference has been merged.

FYI.
kristibigoAuthor Commented:
There wasn't a complete solution due to not having enough information submitted.
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.