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.

"Jim Rech" wrote:

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).Offs et(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
"VB Newbie" wrote in message
...
| 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
|
|
| "VB Newbie" wrote in message
| ...
| 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).Offs et(0, 7)
| cb = Box1.Value
|
| Application.WorksheetFunction.VLookup(cb, Range(x, y), 2,
| False).Select
| = _ Box2.Value
|
| End Sub
|
| Thank you for your assistance!!
|
|
|
|