Hi,
Is it possible to read with VBA comment in a cell and put it as a text in
the first free cell at the same row?
Thanks for your help.

Ireneusz

Yep.

Option Explicit
Sub testme02()
Dim myCommentCells As Range
Dim myCell As Range
Dim DestCell As Range

With ActiveSheet
Set myCommentCells = Nothing
On Error Resume Next
Set myCommentCells = .Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If myCommentCells Is Nothing Then
MsgBox "no comments"
Exit Sub
End If

For Each myCell In myCommentCells.Cells
.Cells(myCell.Row, .Columns.Count).End(xlToLeft).Offset(0, 1) _
.Value = myCell.Comment.Text
Next myCell
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Reniek wrote:

Hi,
Is it possible to read with VBA comment in a cell and put it as a text in
the first free cell at the same row?
Thanks for your help.

Ireneusz

--

Dave Peterson

Hi Irene,
Yes, it is possible, I would suggest using a function so that it is brought
into the cell of your choice. Avoids duplications and other problems.
Avoid problems with several different columns needing a wider column for
the text of a comment that does not match the rest of the cells in a column.

Cell Comments: http://www.mvps.org/dmcritchie/excel....htm#mycomment

There are other solutions on the page as well.


You can see the kind of code you need within this test of an event macro

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cells(Target.row, Columns.Count).End(xlToLeft).Offset(0, 1).Value _
= Target.Comment.Text
End Sub


To populate the first available after the last used cell on a row
-- there is no error checking, and the populated cells may not line up.

Sub PopulateFirstAvailableCellOnRowCommentText()
Dim cmt As Comment
For Each cmt In ActiveSheet.Comments
Cells(cmt.Parent.Row, Columns.Count).End(xlToLeft).Offset(0, 1).Value _
= cmt.Text
Next cmt
End Subt


---HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htmSearch Page: http://www.mvps.org/dmcritchie/excel/search.htm "Reniek"
wrote in message ...
Hi,
Is it possible to read with VBA comment in a cell and put it as a text in
the first free cell at the same row?
Thanks for your help.

Ireneusz