Vlookup

[Jeremys Dad]

I'm just starting to play about with Excel VBA and have hit a rough
spot. I can't quite seem to grasp the proper coding for Vlookup.

The workbook contains 6 worksheets, one of which contains data on our
staff members. I'm trying to capture the name of the staff member
based on their staff number (we call it Case Org). Using the exit
event of a text box, I hope to get the staff member's name entered into
a cell.

Using Jeff Walkenbach's excellent "Dummies" guide, I can't seem to get
my code to work. I get any number of different errors as I have moved
things about. Any help would be appreciated.

As I read it, the following should work....

Private Sub txtCaseOrg_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim CaseOrgNum as Variant
Dim CaseOrgName as String

CaseOrgNum = txtCaseOrg.Text
Sheets("CaseOrgs").Activate

CaseOrgName = Application.VLookup(CaseOrgNum, Range("A1:B20"), 2,
False)

MsgBox CaseOrgName
End Sub

Thanks!

 

[Dave Peterson]

If the CaseOrgNum data in the worksheet is really a number, then you'll want to
look using a number.

In excel, 123 is different than '123 (text).

And if it's not found, you could add a check.



Private Sub txtCaseOrg_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim CaseOrgNum as Variant
Dim CaseOrgName as Variant 'could be an error

CaseOrgNum = txtCaseOrg.Text
'Sheets("CaseOrgs").Activate

if isnumeric(caseorgnum) then
'clng() maybe????
CaseOrgName = Application.VLookup(cdbl(CaseOrgNum), _
worksheets("caseorgs").Range("A1:B20"), 2, False)

'and just in case it isn't found
if iserror(caseorgname) then
msgbox "not a match, the board goes back
else
MsgBox CaseOrgName
end if
else
msgbox "not a number!"
end if

End Sub

 

[John Coleman]

What jumps out immediately is your line

Application.VLookup

There isn't any such beast. Try

Application.WorksheetFunction.VLookup

Let the IDE be your friend. When you type "Application." a drop-down
list should appear. Make your choices from that. If you can't find what
you need, you probably have to go through a child object. Consult the
online help.

HTH

-John Coleman

 

[Dave Peterson]

Application.vlookup will still work ok.

What jumps out immediately is your line

Application.VLookup

There isn't any such beast. Try

Application.WorksheetFunction.VLookup

Let the IDE be your friend. When you type "Application." a drop-down
list should appear. Make your choices from that. If you can't find what
you need, you probably have to go through a child object. Consult the
online help.

HTH

-John Coleman

 

[John Coleman]

Application.vlookup will still work ok.

You're right - but why? It seems to be both undocumented and illogical.
It is strange that Application.vlookup is accepted but
Range("A1").FontStyle isn't. Are there other cases where the
application object can directly refer to its grandchildren or are
worksheet functions exceptional (sort of like how Item is the default
method for collection objects)

Thanks for the correction

-John Coleman

 

[John Coleman]

I was able to dig up the answer. It *is* illogical in the abstract -
but it is the way it is for backwards compatibility. Excel 95 lacked
the WorksheetFunction method. I started VBA programming with Excel 97
and never realized it was different before.

 

[Dave Peterson]

I bet you found an answer by Tom Ogilvy!

One of the differences is the way they behave:

Application.vlookup returns an error that you can check:
dim Res as variant 'could return an error
res = application.vlookup(....)
if iserror(res) then
msgbox "no match"
else
msgbox res
end if

Application.worksheetfunction.vlookup raises a trappable error that you have to
catch:

dim res as variant
on error resume next
res = application.worksheetfunction.vlookup(...)
if err.number <> 0 then
msgbox "no match"
else
msgbox res
end if
on error goto 0

=====
application.match and application.worksheetfunction.match behave the same way.