Fast way to change text in a column of cells into comments 2

[Davefeet]

Fast way to change text in a column of cells into comments?

I dont want to go through each cell and manually insert a comment and then paste the data into it.

Thanks in advance

SLC: Greatest snow on earth!!!

 

[SkipVought]

Dave,

Why don't you use the macro recorder to do one comment box, then, if you're have problems using it in a loop, post back with your code.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[macropod]

Try something the following macro, which adds the text to the comments for each selected cell, or even the whole worksheet, and displays the comments in an appropriately-sized box.:

Sub CellToComments()
Application.ScreenUpdating = False
Dim CommentRange As Range, TargetCell As Range
'skip over errors caused by trying to delete comments in cells with no comments
On Error Resume Next
'If the whole worksheet is selected, limit action to the used range.
If Selection.Address = Cells.Address Then
	Set CommentRange = Range(ActiveSheet.UsedRange.Address)
Else
	Set CommentRange = Range(Selection.Address)
End If
'Test whether the cell contains a formula, so as to decide whether to turn it into a comment.
For Each TargetCell In CommentRange
	With TargetCell
		'check whether the cell has a formula
		If Left(.Formula, 1) <> "=" Then 'To turn formulae into comments instead, change the '<>' to '='
			'delete any existing comment
			.Comment.Delete
			'add a new comment
			.AddComment
			'copy the formula into the comment box
			.Comment.Text Text:=.Formula
			'display the comment
			.Comment.Visible = True
			With .Comment.Shape
				'automatically resizes the comment
				.TextFrame.AutoSize = True
				'position the comment adjacent to its cell
				If TargetCell.Column < 254 Then.IncrementLeft -11.25
				If TargetCell.Row <> 1 Then .IncrementTop 8.25
			End With
		End If
	End With
Next
MsgBox "	To print the comments, choose" & vbCrLf & "  File|Page Setup|Sheet|Comments," & vbCrLf & "then choose the required print option.", vbOKOnly
Application.ScreenUpdating = True
End Sub

Not that the code to reposition the comment won't give the right results for thr 1st row or columns IT-IV.

Cheers