Using Vlookup in VBA code

[Guest]

I am having some problems using the VLookup command within my VBA code. I
have successfully written some code which will pull the Novell User ID and
set it to strNovellUser.

I want to now use the strNovellUser in a VLookup function to lookup some
other information I have on another sheet (Users)in the workbook. I have
verified that my Novell UserID shows up in column 1 and there is data in
column 2, but the VLookup is either not finding it or it is not being set
properly to strName.

Below is part of the code I am using. Any help would be greatly appreciated.

===============
Public Sub BasicInfo()
Dim strName As String

strName = WorksheetFunction.VLookup(strNovellUser, _
Worksheets("Users").Range("A2:E14"), 2)
MsgBox "First Name is " & strName

End Sub
===============

 

[Sharad Naik]

Hello Kathy,

Try with small modification as under (Added "FALSE")

strName = WorksheetFunction.VLookup(strNovellUser, _
Worksheets("Users").Range("A2:E14"), 2, FALSE)

If that too doesn't work try following (Further added "Trim")

strName = WorksheetFunction.VLookup(Trim(strNovellUser), _
Worksheets("Users").Range("A2:E14"), 2, FALSE)

Sharad

 

[Dave Peterson]

I would think you'd want your =vlookup() to have a 4th parm of false (exact
match required):

Public Sub BasicInfo()
Dim strName As Variant
strName = Application.VLookup(strNovellUser, _
Worksheets("Users").Range("A2:E14"), 2, 0)
if iserr(strname) then
msgbox "Not found"
else
MsgBox "First Name is " & strName
end if
End Sub

I like application.vlookup() so that I can check for an error. But that means I
have to declare strName as a variant (varName???).