Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform for data pull
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!! | | | | |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! | | | | |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA: Pull Record Into UserForm | Excel Programming | |||
VBA: Pull Record Into UserForm | Excel Programming | |||
VBA: Pull Record Into UserForm | Excel Programming | |||
VBA: Pull Record Into UserForm | Excel Programming | |||
pull down menu on userform | Excel Programming |