Please Help - Vlookup in a macro.
 

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

Please Help - Vlookup in a macro.
 

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

Please Help - Vlookup in a macro.
 

Hi
Try this

Sub AutoComment()


Dim abvar As String
Dim mycell As Range

For Each mycell In Range("B4:H27")
If mycell.Value < "" Then
abvar = Application.VLookup(mycell.Value,
Range("SkillLegend!A1:B10"), 2, False)
mycell.AddComment Text:=abvar
End If
Next mycell

End Sub

VLookUp doen't like to search for an empty string. Just to be safe you
might use
abvar = Cstr(Application.VLookup(mycell.Value,
Range("SkillLegend!A1:B10"), 2, False))

to force abvar to be a character string.

regards
Paul

Please Help - Vlookup in a macro.
 

Thank you both!


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