Retrieve cell values with comments

[asif_1986]

Hi,
I have a large worksheet in which many cells have comments (text, picture or both). I want to retrieve the cell values in other worksheet , which can easily be done by using VLOOKUP function but I want to retrieve the comments too. And these comments should be placed where the cell values are.

Please give suggestions.

(I am using MS Excel-2007)

 

[pedie]

I dont think there is a formula to get the comments into cell unless you write a custom function or a code to achieve this

Do you just want list of all the comments in one sheet specifically?

try this code.
It will show you list of all range with comments and comments next to it in new sheet. Then you can apply the Vlook up which is easy.

Sub showcomments()
   Application.ScreenUpdating = False
   Dim commrange As Range
   Dim mycell As Range
   Dim curwks As Worksheet
   Dim newwks As Worksheet
   Dim i As Long
   Set curwks = ActiveSheet
   On Error Resume Next
   Set commrange = curwks.Cells _
       .SpecialCells(xlCellTypeComments)
   On Error GoTo 0
   If commrange Is Nothing Then
      MsgBox "no comments found"
      Exit Sub
   End If
   Set newwks = Worksheets.Add
    newwks.Range("A1:D1").Value = _
        Array("Address", "Name", "Value", "Comment")
   i = 1
   For Each mycell In commrange
      With newwks
        i = i + 1
        On Error Resume Next
        .Cells(i, 1).Value = mycell.Address
        .Cells(i, 2).Value = mycell.Name.Name
        .Cells(i, 3).Value = mycell.Value
        .Cells(i, 4).Value = mycell.Comment.Text
      End With
   Next mycell
   Application.ScreenUpdating = True
End Sub