macro to insert comments?

[darkwood]

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

 

[Don Guillett]

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

 

[darkwood]

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

 

[vezerid]

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

 

[darkwood]

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

 

[Dave Peterson]

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