Macro to paste comment text in selected cells onto selected cells

[exceluser2007]

Hi All,

I would like to understand how to do the following.

To create a macro that pastes the comments in all cells of a selected range into those corresponding cells.

The only caveat is, that if the comment contains the following text:

30/06/08: 3months 0% tax

I would like to exclude everything prior to ":" and trim all leading and trailing blanks, so that the required output would be

3months 0% tax

Could anyone please show how to write this code?

regards

 

[crimson_b1ade]

try the below (which needs to be pasted into a vba Module). you'll need to have your commented cells selected before running the macro. you can also assign the below macro to a command button if you like as well:

Sub ReplaceComments()
For Each cmt In Selection
cmt.Value = cmt.Comment.Text
Next cmt
Selection.Replace What:="30/06/08: 3months 0% tax", Replacement:= _
"3months 0% tax", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False _
, SearchFormat:=False, ReplaceFormat:=False
End Sub