I have a lookup function that I'd like to embed into a macro to place
the lookup result in a comment over a certain cell. Is this possible?
If so, how can I do it?


--
darkwood
------------------------------------------------------------------------
darkwood's Profile: http://www.excelforum.com/member.php...o&userid=29948
View this thread: http://www.excelforum.com/showthread...hreadid=506782

first try doing with the macro recorder on. Come back for further help

--
Don Guillett
SalesAid Software

"darkwood" wrote in
message ...

I have a lookup function that I'd like to embed into a macro to place
the lookup result in a comment over a certain cell. Is this possible?
If so, how can I do it?


--
darkwood
------------------------------------------------------------------------
darkwood's Profile:
http://www.excelforum.com/member.php...o&userid=29948
View this thread: http://www.excelforum.com/showthread...hreadid=506782

I haven't used macros before, can you please elaborate?


--
darkwood
------------------------------------------------------------------------
darkwood's Profile: http://www.excelforum.com/member.php...o&userid=29948
View this thread: http://www.excelforum.com/showthread...hreadid=506782

Hi darkwood,

I responded to a similar request in
microsoft.public.excel.worksheet.funtions a while back. In there I also
have the code. Do a search with the words:

vezerid comment VBA

See if the thread is any similar to your problem. If so, come back with
some information as to the structure of the lookup table that you have
in mind and I will be happy to make any modifications.

HTH
Kostis Vezerides

I tried searching by that string, but only this post came up.


--
darkwood
------------------------------------------------------------------------
darkwood's Profile: http://www.excelforum.com/member.php...o&userid=29948
View this thread: http://www.excelforum.com/showthread...hreadid=506782

This is from a previous post. Maybe it'll give you an idea:

You could write some code that could populate the comment with the results of an
=vlookup() function.

But if you change that part number, then the comment will be wrong until that
macro runs again.

This sounds like a nice idea until you start getting down to how many things can
go wrong (my opinion only).

If I were you, I'd just use an adjacent cell to contain that description.

But if you want a macro to do that:

Option Explicit
Sub AddCommentsToSelection()

Dim myRng As Range
Dim myCell As Range
Dim myLookupRng As Range
Dim lArea As Double
Dim res As Variant

Set myRng = Selection
Set myLookupRng = Worksheets("sheet2").Range("a:b")

For Each myCell In myRng.Cells
If myCell.Comment Is Nothing Then
'do nothing
Else
myCell.Comment.Delete
End If

res = Application.VLookup(myCell.Value, myLookupRng, 2, False)
If IsError(res) Then
'don't put anything there?
Else
myCell.AddComment Text:=res
With myCell.Comment
.Shape.TextFrame.AutoSize = True
If .Shape.Width 300 Then
lArea = .Shape.Width * .Shape.Height
.Shape.Width = 200
.Shape.Height = (lArea / 200) * 1.3
End If
End With
End If
Next myCell

End Sub

Just select the range to add the comments and run the macro.

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

and I borrowed the resizing comment code from Debra Dalgleish's site:
http://www.contextures.com/xlcomments03.html#Resize


darkwood wrote:

I have a lookup function that I'd like to embed into a macro to place
the lookup result in a comment over a certain cell. Is this possible?
If so, how can I do it?

--
darkwood
------------------------------------------------------------------------
darkwood's Profile: http://www.excelforum.com/member.php...o&userid=29948
View this thread: http://www.excelforum.com/showthread...hreadid=506782

--

Dave Peterson