Macro to Add Comments with vlookup
 

I have an array of data on one sheet, and another sheet with definitions
for each unique record in the array.

I'd like to have Excel automatically look up the definition for each
record and place it in a comment on that cell. This way, the end user
just hovers over each cell and gets the definition for the value in the
cell.

I assume this must be done with a macro, but I have no experience with
macros. Here's my first stab at it (which doesn't work). Could someone
point me in the right direction?

Sub AutoComment()

Dim CommentValue


Set CommentValue = VLOOKUP(ActiveCell,SkillLegend!A:B,2,FALSE)


With B4: H27
If .Comment Is Nothing Then .AddComment
..Comment.Text Text:=CommentValue
End With
End Sub

Thank you!


--
AllenWatts
------------------------------------------------------------------------
AllenWatts's Profile: http://www.excelforum.com/member.php...o&userid=34301
View this thread: http://www.excelforum.com/showthread...hreadid=540704

Macro to Add Comments with vlookup
 

Sub AutoComment()

Dim CommentValue as String, cell as Range

for each cell in Range("B4:H27")

CommentValue = Application.VLOOKUP(Cell.Value,Range("SkillLegend! A:B",2,FALSE)

cell.NoteText Text:=commentValue
Next

End Sub

--
Regards,
Tom Ogilvy


"AllenWatts" wrote:


I have an array of data on one sheet, and another sheet with definitions
for each unique record in the array.

I'd like to have Excel automatically look up the definition for each
record and place it in a comment on that cell. This way, the end user
just hovers over each cell and gets the definition for the value in the
cell.

I assume this must be done with a macro, but I have no experience with
macros. Here's my first stab at it (which doesn't work). Could someone
point me in the right direction?

Sub AutoComment()

Dim CommentValue


Set CommentValue = VLOOKUP(ActiveCell,SkillLegend!A:B,2,FALSE)


With B4: H27
If .Comment Is Nothing Then .AddComment
.Comment.Text Text:=CommentValue
End With
End Sub

Thank you!


--
AllenWatts
------------------------------------------------------------------------
AllenWatts's Profile: http://www.excelforum.com/member.php...o&userid=34301
View this thread: http://www.excelforum.com/showthread...hreadid=540704

Macro to Add Comments with vlookup
 

Thank you for your help.

I am still a little confused. I get a Type Mismatch error when i run
this, the debug window is highlighting the line:

CommentValue = Application.VLookup(cell.Value, "SkillLegend!A:B", 2,
False)


as i am trying to understand what you've written, i get little
confused. if we create a variable called "cell" and it is a range,
where in the code do we set what it is?

Thanks again.


--
AllenWatts
------------------------------------------------------------------------
AllenWatts's Profile: http://www.excelforum.com/member.php...o&userid=34301
View this thread: http://www.excelforum.com/showthread...hreadid=540704