Application.vlookup problem
Hello,
I'm having problem running the following piece of code and would appreciate any help. Firstly I've created two comboboxes, the result of one populates the second. I then want to use the application.vlookup to populate textboxes on my form. Private Sub CB2_Change() Dim appexcel as excel.application Dim choice as string Dim wks as worksheet set choice = CB2.text 'this is the name of the sheet. Set appexcel = Application textbox1.text = application.vlookup(CB1.text, wks(choice).range("A1:N200"),3,true) ' this line errors when I debug and I think,it is to do with the choice variable. etc End sub Hope this makes sense. Regards David |
Application.vlookup problem
David,
Since wks is defined as a Worksheet type object, you can't get another sheet reference from it using (). Instead, you should Set wks to the appropriate sheet and use that unadorned variable in your VLOOKUP function: Set wks = Worksheets(choice) textbox1.text = application.vlookup(CB1.text, _ wks.range("A1:N200"),3,true) Or, you Worksheets(choice) to specify the sheet: textbox1.text = application.vlookup(CB1.text, _ Worksheets(choice).range("A1:N200"),3,true) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "David Goodall" wrote in message ... Hello, I'm having problem running the following piece of code and would appreciate any help. Firstly I've created two comboboxes, the result of one populates the second. I then want to use the application.vlookup to populate textboxes on my form. Private Sub CB2_Change() Dim appexcel as excel.application Dim choice as string Dim wks as worksheet set choice = CB2.text 'this is the name of the sheet. Set appexcel = Application textbox1.text = application.vlookup(CB1.text, wks(choice).range("A1:N200"),3,true) ' this line errors when I debug and I think,it is to do with the choice variable. etc End sub Hope this makes sense. Regards David |
All times are GMT +1. The time now is 02:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com