Application.vlookup problem

[David Goodall]

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

 

[Chip Pearson]

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