moving comment data into cells

[Praedico]

I have an Excel 2003 spreadsheet on Win XP with a column of data. 635 rows plus the header. Some of the cells have comments, some don't.
I'd like to overwrite the data in the cell with the data in the comment (if the cell has one).
I don't really want to do this manually 635 times.
So far I have a macro with the following but it's not working:

Sub Macro1()
Dim r As Integer
Dim rmax as Integer
r = 2
rmax = 636
 
For r = 2 To rmax
If Sheet2.Range("a" & r).Comment.Text <> "" Then
Range("a" & r).Value = Range("g" & r).Comment.Text
End If
Next r
 
End Sub

I believe the issue is with the "comment.text" portion of the if statement and when it encounters a cell without a comment.

Any help is much appreciated
Thank you

 

[Gary McMaster]

Maybe this:

Public Sub Test()

Dim oComment As Comment

For Each oComment In ActiveSheet.Comments
    ActiveSheet.Range(oComment.Parent.Address).Value = oComment.Text
Next oComment

End Sub

 

[wigi]

Sub fff()

    Dim rng As Range
    
    For Each rng In Sheet2.Columns(1).SpecialCells(xlCellTypeComments)
        rng.Value = rng.Comment.Text
    Next

End Sub

 

[Praedico]

That worked.
Thank you very much.