ExcelBanter - Userform for data pull

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Userform for data pull (https://www.excelbanter.com/excel-programming/318555-userform-data-pull.html)

Userform for data pull

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!!

Userform for data pull

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
"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!!
|

Userform for data pull

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!!

Userform for data pull

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!!

Userform for data pull

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)

"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!!

Userform for data pull

Userform for data pull

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!!
|
|
|
|

Userform for data pull

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).Offs et(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

VB Newbie wrote:

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!!

--

Dave Peterson