Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup
Application.vlookup will still work ok.
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup
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. John Coleman wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup
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. John Coleman wrote: 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. John Coleman wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Which is faster: VLOOKUP-worksheet or VB-array VLOOKUP? | Excel Programming | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |