Userform for data pull

[Guest]

Good day to all!

I am trying to import data into my simple 2-field userform using a lookup
function. In the first field, I enter my criteria. Then I click the "Find"
button to look this up in a range. However, the data doesn't come into that
field and I get an error msg saying runtime error 424, object required. Here
is what I have:

Private Sub FindButton_Click()
Dim x As Range, y As Range
Dim cb As String
Set x = Worksheets("Table").Range("B2")
Set y = Worksheets("Table").Range("B65536").End(xlUp).Offset(0, 7)
cb = Box1.Value

Application.WorksheetFunction.VLookup(cb, Range(x, y), 2, False).Select
= _ Box2.Value

End Sub

Thank you for your assistance!!

 

[Jim Rech]

I didn't run your code but shouldn't the last line be something like:

Box2.Value = Application.WorksheetFunction.VLookup(cb, Range(x, y), 2,
False)

--
Jim Rech
Excel MVP
| Good day to all!
|
| I am trying to import data into my simple 2-field userform using a lookup
| function. In the first field, I enter my criteria. Then I click the
"Find"
| button to look this up in a range. However, the data doesn't come into
that
| field and I get an error msg saying runtime error 424, object required.
Here
| is what I have:
|
| Private Sub FindButton_Click()
| Dim x As Range, y As Range
| Dim cb As String
| Set x = Worksheets("Table").Range("B2")
| Set y = Worksheets("Table").Range("B65536").End(xlUp).Offset(0, 7)
| cb = Box1.Value
|
| Application.WorksheetFunction.VLookup(cb, Range(x, y), 2, False).Select
| = _ Box2.Value
|
| End Sub
|
| Thank you for your assistance!!
|

 

[Chip Pearson]

Change
Application.WorksheetFunction.VLookup(cb, Range(x, y), 2,
False).Select
= _ Box2.Value

to

Box2 = Application.WorksheetFunction.VLookup(cb, Range(x, y), 2,
False)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Hi Chip and Jim-

I've used both of your inputs, and now the error message is a runtime error
'1004': Unable to get the Vlookup property of the worksheetfunction class.

Almost there!!
Thank you both for your responses!

 

[Bob Phillips]

Is it wrap-around. Try

Box2 = Application.WorksheetFunction.VLookup(cb, _
Range(x, y), 2, False)


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jim Rech]

One cause of that error message is there is no match. You have to use error
trapping for that possibility. Otherwise I think your code looks okay.

Private Sub FindButton_Click()
Dim x As Range, y As Range
Dim cb As String
Dim Result As Variant
Set x = Worksheets("Table").Range("B2")
Set y = Worksheets("Table").Range("B65536").End(xlUp).Offset(0, 7)
cb = Box1.Value
On Error GoTo NoMatch
Result = Application.WorksheetFunction.VLookup(cb, Range(x, y), 2,
False)
PutResult:
Box2.Value = Result
Exit Sub
NoMatch:
Result = "No Match"
Resume PutResult
End Sub

--
Jim Rech
Excel MVP
| Hi Chip and Jim-
|
| I've used both of your inputs, and now the error message is a runtime
error
| '1004': Unable to get the Vlookup property of the worksheetfunction class.
|
| Almost there!!
| Thank you both for your responses!
|
| "Chip Pearson" wrote:
|
| > Change
| > Application.WorksheetFunction.VLookup(cb, Range(x, y), 2,
| > False).Select
| > = _ Box2.Value
| >
| > to
| >
| > Box2 = Application.WorksheetFunction.VLookup(cb, Range(x, y), 2,
| > False)
| >
| >
| > --
| > Cordially,
| > Chip Pearson
| > Microsoft MVP - Excel
| > Pearson Software Consulting, LLC
| > www.cpearson.com
| >
| >
| > | > > Good day to all!
| > >
| > > I am trying to import data into my simple 2-field userform
| > > using a lookup
| > > function. In the first field, I enter my criteria. Then I
| > > click the "Find"
| > > button to look this up in a range. However, the data doesn't
| > > come into that
| > > field and I get an error msg saying runtime error 424, object
| > > required. Here
| > > is what I have:
| > >
| > > Private Sub FindButton_Click()
| > > Dim x As Range, y As Range
| > > Dim cb As String
| > > Set x = Worksheets("Table").Range("B2")
| > > Set y =
| > > Worksheets("Table").Range("B65536").End(xlUp).Offset(0, 7)
| > > cb = Box1.Value
| > >
| > > Application.WorksheetFunction.VLookup(cb, Range(x, y), 2,
| > > False).Select
| > > = _ Box2.Value
| > >
| > > End Sub
| > >
| > > Thank you for your assistance!!
| > >
| >
| >
| >

 

[Guest]

Thank you for all your help, Jim. However, I still couldn't get the
worksheetfunction.vlookup to work. So what I did was simply create a hidden
sheet with the lookup functions in it, so when I entered criteria in box1 it
would go to a cell and then be looked up. I could then pull the output to
the form from the hidden sheet. It was very strange, because everything
seemed so straightforward.

 

[Dave Peterson]

Another option is to drop the .worksheetfunction:

Private Sub FindButton_Click()
Dim x As Range, y As Range
Dim cb As String
dim Res as variant

Set x = Worksheets("Table").Range("B2")
Set y = Worksheets("Table").Range("B65536").End(xlUp).Offset(0, 7)
cb = Box1.Value

res = Application.VLookup(cb, Range(x, y), 2, False)

if iserror(res) then
box2.value = "missing!"
else
box2.value = res
end if

End Sub