How do I copy cell text to comments and vise versa

[senthile]

How do I copy cell text to comments in excel 2010 and vise versa (i.e. from comments to cell)

 

[bruderbell]

Sub swap()

This is the title of the macro

If ActiveCell.Comment Is Nothing Then

ActiveCell is the cell you have highlighted when yourun the macro. So this if statement checks if your current cell has no comment.

x = ""
ActiveCell.AddComment

If there was no comment, this code executes. x is a variable I chose to store the comment value. So if there was no comment, I define x as blank (""). Then I create a blank comment.

Else
x = ActiveCell.Comment.Text
End If

This else code executes if the if statement was false, so in this case if there really was a comment. In that case, we define x as the comment's text.

y = ActiveCell.Value

y is another variable, and here I store the cell value to it.

If y = Empty Then
'ActiveCell.Comment.Delete

If the cell was empty, then I delete the comment.

Else
'ActiveCell.Comment.Text = y
End If

Otherwise, I set the comment text to y. Remember that we previously defined y as the cell contents.

ActiveCell.Value = x
End Sub

Last, I set the cell value to x, which was the comment text. End Sub ends the macro.

Remember that an apostrophe (') mutes a line. So when I gave you the two variants of the macro, I just muted the portions that performed the actions you didn't want.

Keep on Googling. Macros can do just about anything, and it's a kick learning about them.

 

[senthile]

Hi Bruderbell,

Need a another help, when i try to use the below given VBA quote (Macro) to copy the date (Eg : 15-May-2013), i am getting an error as "Run-time error '1004': Aplication-defined or object-defined error", when i press "Debug" it comes to "ActiveCell.Comment.Text y" with yellow highlight. can you help me to fix this.
Note : I will be using this macro to copy the text as well as data alone many times.

Sub swap()
If ActiveCell.Comment Is Nothing Then
x = ""
ActiveCell.AddComment
Else
x = ActiveCell.Comment.Text
End If
y = ActiveCell.Value
If y = Empty Then
ActiveCell.Comment.Delete
Else
ActiveCell.Comment.Text y
End If
'ActiveCell.Value = x
End Sub

 

[bruderbell]

Indeed. The macro was pickup up that date, and seeing it was a date, it wasn't treating it as a string. This is working for me:

Sub swap()
Dim y As String, x As String
If ActiveCell.Comment Is Nothing Then
x = ""
ActiveCell.AddComment
Else
x = ActiveCell.Comment.Text
End If
y = ActiveCell.Value
If y = Empty Then
ActiveCell.Comment.Delete
Else
ActiveCell.Comment.Text y
End If
'ActiveCell.Value = x
End Sub

 

[senthile]

Indeed. The macro was pickup up that date, and seeing it was a date, it wasn't treating it as a string. This is working for me:

Sub swap()
Dim y As String, x As String
If ActiveCell.Comment Is Nothing Then
x = ""
ActiveCell.AddComment
Else
x = ActiveCell.Comment.Text
End If
y = ActiveCell.Value
If y = Empty Then
ActiveCell.Comment.Delete
Else
ActiveCell.Comment.Text y
End If
'ActiveCell.Value = x
End Sub

No words to say... many thanks.... for your instant reply... I works....

 

[senthile]

- Just found that this is working for each cell...
- if i choose multiple cells, still macro runs on 1st cell? are there any possibility to run this macro on selected multiple cell?

 

[skorpionkz]

Hi semthile

try below code. I just modified what you had before:

Sub swap()
Dim rng As Range, cell As Range
Dim y As String, x As String

Set rng = Selection

For Each cell In rng

    If cell.Comment Is Nothing Then
    x = ""
    cell.AddComment
    Else
    x = cell.Comment.Text
    End If
    y = cell.Value
    If y = Empty Then
    cell.Comment.Delete
    Else
    cell.Comment.Text y
    End If
    'Cell.Value = x

Next cell
End Sub

 

[senthile]

It is working...
Similarly can share me the revised below given macro to copy multiple selected comments to multiple selected cells?

Sub swap()
If ActiveCell.Comment Is Nothing Then
x = ""
ActiveCell.AddComment
Else
x = ActiveCell.Comment.Text
End If
y = ActiveCell.Value
If y = Empty Then
'ActiveCell.Comment.Delete
Else
'ActiveCell.Comment.Text y
End If
ActiveCell.Value = x
End Sub

 

[skorpionkz]

Sub swap()
Dim rng As Range, cell As Range
Dim y As String, x As String

Set rng = Selection

For Each cell In rng

If cell.Comment Is Nothing Then
x = ""
cell.AddComment
Else
x = cell.Comment.Text
End If
y = cell.Value
If y = Empty Then
'Cell.Comment.Delete
Else
'Cell.Comment.Text y
End If
cell.Value = x

Next cell
End Sub