Dave Peterson wrote:
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 wrote:

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

Jeremys Dad wrote:
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