Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Problem with VLookup in VBA

  Asked By: Oscar    Date: Feb 01    Category: MS Office    Views: 1061
  

'm having an issue using worksheetfuntion.vlookup, i'm sure it just
because i'm missing something so obvious that I can't see it!

I have a multipage control that has a few option buttons on it's
first page, depending on which one is selected by the user, the
contents of a combobox on the second page is altered using the
following code:

If opt1 = True Then cbo1.RowSource = "FirstList"
If opt2 = True Then cbo1.RowSource = "SecondList"
etc etc etc

Where 'FirstList' corresponds to a three-column wide named range on
the worksheet. What i'd then like to do is use vlookup to assign
values from this named range to a number of variables. However, the
code i've come up with keeps failing with a "Run-time Error 1004:
Unable to get the VLookup function of the WorksheetFunction class".
This code causing this is:

myVar3 = WorksheetFunction.VLookup(cbo1.value, Range(cbo1.RowSource),
3, False)


What is it that i'm missing???

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Tyreece Thompson     Answered On: Feb 01

You are just assigning the string "FirstList" as the RowSource. You need to
assign the address of the FirstList named range, as follows:

Sub TEST()
Dim myvar As String
UserForm1.ComboBox1.RowSource = Range("FirstList").Address
myvar$ = Application.WorksheetFunction.VLookup("CC", _
Range(UserForm1.ComboBox1.RowSource), 3, False)
MsgBox myvar$
End Sub

I also recommend including the userform Name as a qualifier when you refer to
the
combobox (you might get an "Object required" error otherwise).

 
Answer #2    Answered By: Drew Lewis     Answered On: Feb 01

That doesn't seem to fixthings, I still get the "Unable to get the
VLookup function of the WorksheetFunction class". I get the same
error when i use a new form with a single combobox and use the code
you included unmodified. Any other ideas?

 
Answer #3    Answered By: Adalard Fischer     Answered On: Feb 01

That's odd... I tested it in Excel 2003 & Excel 2002 and it works perfectly.
Obviously, "CC" was one of the values in the first column of my lookup range.

Maybe you are missing  a required reference to a library (I don't know which
one it would be). I will keep trying to figure out why you are having this
problem.

 
Didn't find what you were looking for? Find more on Problem with VLookup in VBA Or get search suggestion and latest updates.




Tagged:      

 
 
 
 

Related Post