If the =vlookup() would return the #n/a, your code will have trouble.

Option Explicit
Sub AutoComment()

Dim abvar As Variant 'in case it's an error
Dim mycell As Range
For Each mycell In Range("B4:H27")
abvar = Application.VLookup(mycell.Value, _
worksheets("SkillLegend").range("A:B"), 2, False)

if iserror(abvar) then
'skip it?
else
if mycell.comment is nothing then
'no existing comment
else
mycell.comment.delete
end if
mycell.AddComment Text:=abvar
end if

Next mycell

End Sub

AllenWatts wrote:

I cannot figure out what I'm doing wrong. I get a "type mismatch" error
on the vlookup line.

Sub AutoComment()

Dim abvar As String
Dim mycell As Range

For Each mycell In Range("B4:H27")

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

mycell.AddComment Text:=abvar

Next mycell

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=541045

--

Dave Peterson