Transfer Comment to VLOOKUP Cell on Search

[CCorreia]

I have a spreadsheet that displays a cell's contents in column E based on a
numberical identifier in column B. These cells targeted by the VLOOKUP cell
are located in a seperate part of the same worksheet. I would like to
transfer the comment of the target cell along with the cell data when the
VLOOKUP cell populates. Here is the basic VLOOKUP formula I am using:

=VLOOKUP($B7,$Y$22:$AT$503,2)

Thanks for any help straightening this out, in advance!

 

[Gord Dibben]

Cell Comments cannot be returned using formulas alone.

VBA is required.

See Debra Dalgleish's site for various macros for dealing with Comments.

http://www.contextures.on.ca/xlcomments03.html


Gord Dibben MS Excel MVP

 

[CCorreia]

Thanks Gord,

I actually went over Debra's how-to previously and that provided me with the
solution for displaying graphics from a server side folder in the
spreadsheet.

I forgot to mention that the comment boxes do not have text, they only
display the pictures linked to the outside folder. I assume I will need to
use the following code from Debra's tutorial as the basis for my VBA, however
I do not know how to retrieve the picture file path from the original comment
and insert it in the comment that is created using this code, instead of the
date and time.

Sub CommentDateTimeAdd()
'adds comment with date and time,
' positions cursor at end of comment text

Dim strDate As String
Dim cmt As Comment

strDate = "dd-mmm-yy hh:mm:ss"
Set cmt = ActiveCell.Comment

If cmt Is Nothing Then
Set cmt = ActiveCell.AddComment
cmt.text text:=Format(Now, strDate) & Chr(10)
Else
cmt.text text:=cmt.text & Chr(10) _
& Format(Now, strDate) & Chr(10)
End If

With cmt.Shape.TextFrame
.Characters.Font.Bold = False
End With

SendKeys "%ie~"

End Sub


This would be easier to do in Access and I am familiar with how to do it
there, however this is not my spreadsheet so I can't just transfer all the
data over and do it there.

 

[Gord Dibben]

You may be able to cobble something together with this macro to insert a picture
in a Comment.

http://www.contextures.on.ca/xlcomments03.html#Picture

I haven't figured out how to get the path and name from the source Comment
however.


Gord