Comment to cell in Excel 2003 (Dutch version)

[Flash0220]

Hello,


I'm having the following (small ?) problem.

I have a collumn containing different filenames.
In each cell of that collumn the path to that filename
is stored as a comment.

I want these comments to be converted to cell values (in a different collumn).

Is there an easy way to do this using formulas or VBA ?

Thank in advance for helping.




Flash

 

[Ruddles]

[FONT=Fixedsys]Option Explicit[/FONT]
 
[FONT=Fixedsys]Public Sub ExtractComments()[/FONT]
 
[FONT=Fixedsys]Dim ws As Worksheet[/FONT]
[FONT=Fixedsys]Dim iLastRow As Long[/FONT]
[FONT=Fixedsys]Dim iRow As Long[/FONT]
 
[FONT=Fixedsys]Set ws = ThisWorkbook.Sheets(1)[/FONT]
[FONT=Fixedsys]iLastRow = ws.Cells(ws.Rows.Count, "[COLOR=red]B[/COLOR]").End(xlUp).Row[/FONT]
 
[FONT=Fixedsys]For iRow = 1 To iLastRow[/FONT]
[FONT=Fixedsys]  If Not ws.Cells(iRow, "[COLOR=red]B[/COLOR]").Comment Is Nothing Then[/FONT]
[FONT=Fixedsys]    ws.Cells(iRow, "[COLOR=blue]C[/COLOR]") = ws.Cells(iRow, "[COLOR=red]B[/COLOR]").Comment.Text[/FONT]
[FONT=Fixedsys]  End If[/FONT]
[FONT=Fixedsys]Next iRow[/FONT]
 
[FONT=Fixedsys]End Sub[/FONT]

Change the bit in red to point to the column the file names are in and the bit in blue to the column you want the path written to.

 

[Flash0220]

Hello,


The filenames are in collumn B and i would like the path of that filename in collumn C.

Best regards,


Flash

 

[Ruddles]

Our posts crossed on the bitstream. Try my code (above).

 

[Flash0220]

Hello,


I'll give it a try, thanks for your fast answer !


Flash