| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userfrm Lookup Works For text not Number
Hi,
I have the following code for inserting data on a user form based on a number on a cell in a worksheet: Private Sub ComboBox1_Click() TextBox2.Text = WorksheetFunction.VLookup(ComboBox1.Text, Worksheets("Blending Details").Range("A2:Z500"), 2, False) End Sub When I put a word in the combo box the lookup is successful and I get the value returned in textbox2. But when a number is in the combox I get a runtime error. Any ideas? Thanks gregork |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Userfrm Lookup Works For text not Number
What error do you get?
-- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "gregork" wrote in message ... Hi, I have the following code for inserting data on a user form based on a number on a cell in a worksheet: Private Sub ComboBox1_Click() TextBox2.Text = WorksheetFunction.VLookup(ComboBox1.Text, Worksheets("Blending Details").Range("A2:Z500"), 2, False) End Sub When I put a word in the combo box the lookup is successful and I get the value returned in textbox2. But when a number is in the combox I get a runtime error. Any ideas? Thanks gregork |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Userfrm Lookup Works For text not Number
Hi Chip,
I get "Unable to get the VLookup property of the worksheet function class" regards gregork "Chip Pearson" wrote in message ... What error do you get? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "gregork" wrote in message ... Hi, I have the following code for inserting data on a user form based on a number on a cell in a worksheet: Private Sub ComboBox1_Click() TextBox2.Text = WorksheetFunction.VLookup(ComboBox1.Text, Worksheets("Blending Details").Range("A2:Z500"), 2, False) End Sub When I put a word in the combo box the lookup is successful and I get the value returned in textbox2. But when a number is in the combox I get a runtime error. Any ideas? Thanks gregork |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Userfrm Lookup Works For text not Number
Because you are looking up a string and not a number
Private Sub ComboBox1_Click() if not isnumeric(ComboBox1.Text) then TextBox2.Text = Application.VLookup(ComboBox1.Text, _ Worksheets("Blending Details").Range("A2:Z500"), 2, False) Else TextBox2.Text = Application.VLookup(cdbl(ComboBox1.Text), _ Worksheets("Blending Details").Range("A2:Z500"), 2, False) End If End Sub Adjust to suit your situation, but if your lookup range contains numbers, then lookup with a Number for best results (given you are looking for a number). -- Regards, Tom Ogilvy "gregork" wrote in message ... Hi, I have the following code for inserting data on a user form based on a number on a cell in a worksheet: Private Sub ComboBox1_Click() TextBox2.Text = WorksheetFunction.VLookup(ComboBox1.Text, Worksheets("Blending Details").Range("A2:Z500"), 2, False) End Sub When I put a word in the combo box the lookup is successful and I get the value returned in textbox2. But when a number is in the combox I get a runtime error. Any ideas? Thanks gregork |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Userfrm Lookup Works For text not Number
Many thanks Tom your code works perfectly. Unfortunately I now have another
small problem...The number I am looking up for a reference to a row on my worksheet........ [(ComboBox1.Text), _ Worksheets("Blending Details")]... can be listed more than once. So in text box2 I want to refer to the first occurrence of the ComboBox1.Text then in text box3 I want to refer to the second occurrence of the ComboBox1.Text (if there is one) and so on and so on. Sounds real complicated but my sheet is like a record of order details so each record has an order number (the number I am looking up) but many records can have the same order number as they are individual components of the same order. I hope your not confused after that lot ( I know I am). Thanks Again gregork "Tom Ogilvy" wrote in message ... Because you are looking up a string and not a number Private Sub ComboBox1_Click() if not isnumeric(ComboBox1.Text) then TextBox2.Text = Application.VLookup(ComboBox1.Text, _ Worksheets("Blending Details").Range("A2:Z500"), 2, False) Else TextBox2.Text = Application.VLookup(cdbl(ComboBox1.Text), _ Worksheets("Blending Details").Range("A2:Z500"), 2, False) End If End Sub Adjust to suit your situation, but if your lookup range contains numbers, then lookup with a Number for best results (given you are looking for a number). -- Regards, Tom Ogilvy "gregork" wrote in message ... Hi, I have the following code for inserting data on a user form based on a number on a cell in a worksheet: Private Sub ComboBox1_Click() TextBox2.Text = WorksheetFunction.VLookup(ComboBox1.Text, Worksheets("Blending Details").Range("A2:Z500"), 2, False) End Sub When I put a word in the combo box the lookup is successful and I get the value returned in textbox2. But when a number is in the combox I get a runtime error. Any ideas? Thanks gregork |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Userfrm Lookup Works For text not Number
Assume your combobox / textboxes are on a userform:
Dim rng as Range, i as long, cell as Range i = 0 set rng =Worksheets("Blending Details").Range("A2:A500") for each cell in rng if trim(lcase(cell.Text)) = trim(lcase(Combobox1.Text)) then i = i + 1 controls("Textbox" & i).Value = cell.offset(0,1).Value controls("Textbox" & i).Tag = cStr(cell.row-1) end if Next Based on a later question you asked, I added the row-1 of the value in the tag property. Then if you want to change that value, you would get the tag property from the textbox to get the index into the range Dim lngIdex as Long, rng as Range set rng =Worksheets("Blending Details").Range("A2:A500") lngIdex = clng(Textbox1.Tag) rng(lngIdex).offset(0,1).Value = Textbox1.Text -- Regards, Tom Ogilvy "gregork" wrote in message ... Many thanks Tom your code works perfectly. Unfortunately I now have another small problem...The number I am looking up for a reference to a row on my worksheet........ [(ComboBox1.Text), _ Worksheets("Blending Details")]... can be listed more than once. So in text box2 I want to refer to the first occurrence of the ComboBox1.Text then in text box3 I want to refer to the second occurrence of the ComboBox1.Text (if there is one) and so on and so on. Sounds real complicated but my sheet is like a record of order details so each record has an order number (the number I am looking up) but many records can have the same order number as they are individual components of the same order. I hope your not confused after that lot ( I know I am). Thanks Again gregork "Tom Ogilvy" wrote in message ... Because you are looking up a string and not a number Private Sub ComboBox1_Click() if not isnumeric(ComboBox1.Text) then TextBox2.Text = Application.VLookup(ComboBox1.Text, _ Worksheets("Blending Details").Range("A2:Z500"), 2, False) Else TextBox2.Text = Application.VLookup(cdbl(ComboBox1.Text), _ Worksheets("Blending Details").Range("A2:Z500"), 2, False) End If End Sub Adjust to suit your situation, but if your lookup range contains numbers, then lookup with a Number for best results (given you are looking for a number). -- Regards, Tom Ogilvy "gregork" wrote in message ... Hi, I have the following code for inserting data on a user form based on a number on a cell in a worksheet: Private Sub ComboBox1_Click() TextBox2.Text = WorksheetFunction.VLookup(ComboBox1.Text, Worksheets("Blending Details").Range("A2:Z500"), 2, False) End Sub When I put a word in the combo box the lookup is successful and I get the value returned in textbox2. But when a number is in the combox I get a runtime error. Any ideas? Thanks gregork |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Userfrm Lookup Works For text not Number
Thanks for your reply Tom. I'm sorry to be a nuisance but I can't get the
code to work. I think its probably a case of putting the code in the wrong event.Here's the code I have thus far hopefully you can point me in the right direction. Private Sub UserForm_Initialize() Dim i As Integer For i = 2 To 500 Me.ComboBox1.AddItem Worksheets("Blending Details").Cells(i, 1) ComboBox1.Value = "" TextBox1.Value = "" Next End Sub -------------------------------------------------------------------- Private Sub ComboBox1_Click() If Not IsNumeric(ComboBox1.Text) Then TextBox2.Text = Application.VLookup(ComboBox1.Text, _ Worksheets("Blending Details").Range("A2:Z500"), 2, False) TextBox3.Text = Application.VLookup(ComboBox1.Text, _ Worksheets("Blending Details").Range("A2:Z500"), 1, False) TextBox4.Text = Application.VLookup(ComboBox1.Text, _ Worksheets("Blending Details").Range("A2:Z500"), 3, False) TextBox10.Text = Application.VLookup(ComboBox1.Text, _ Worksheets("Blending Details").Range("A2:Z500"), 3, False) Else TextBox2.Text = Application.VLookup(CDbl(ComboBox1.Text), _ Worksheets("Blending Details").Range("A2:Z500"), 2, False) TextBox3.Text = Application.VLookup(CDbl(ComboBox1.Text), _ Worksheets("Blending Details").Range("A2:Z500"), 1, False) TextBox4.Text = Application.VLookup(CDbl(ComboBox1.Text), _ Worksheets("Blending Details").Range("A2:Z500"), 3, False) TextBox10.Text = Application.VLookup(CDbl(ComboBox1.Text), _ Worksheets("Blending Details").Range("A2:Z500"), 3, False) End If End Sub "Tom Ogilvy" wrote in message ... Assume your combobox / textboxes are on a userform: Dim rng as Range, i as long, cell as Range i = 0 set rng =Worksheets("Blending Details").Range("A2:A500") for each cell in rng if trim(lcase(cell.Text)) = trim(lcase(Combobox1.Text)) then i = i + 1 controls("Textbox" & i).Value = cell.offset(0,1).Value controls("Textbox" & i).Tag = cStr(cell.row-1) end if Next Based on a later question you asked, I added the row-1 of the value in the tag property. Then if you want to change that value, you would get the tag property from the textbox to get the index into the range Dim lngIdex as Long, rng as Range set rng =Worksheets("Blending Details").Range("A2:A500") lngIdex = clng(Textbox1.Tag) rng(lngIdex).offset(0,1).Value = Textbox1.Text -- Regards, Tom Ogilvy "gregork" wrote in message ... Many thanks Tom your code works perfectly. Unfortunately I now have another small problem...The number I am looking up for a reference to a row on my worksheet........ [(ComboBox1.Text), _ Worksheets("Blending Details")]... can be listed more than once. So in text box2 I want to refer to the first occurrence of the ComboBox1.Text then in text box3 I want to refer to the second occurrence of the ComboBox1.Text (if there is one) and so on and so on. Sounds real complicated but my sheet is like a record of order details so each record has an order number (the number I am looking up) but many records can have the same order number as they are individual components of the same order. I hope your not confused after that lot ( I know I am). Thanks Again gregork "Tom Ogilvy" wrote in message ... Because you are looking up a string and not a number Private Sub ComboBox1_Click() if not isnumeric(ComboBox1.Text) then TextBox2.Text = Application.VLookup(ComboBox1.Text, _ Worksheets("Blending Details").Range("A2:Z500"), 2, False) Else TextBox2.Text = Application.VLookup(cdbl(ComboBox1.Text), _ Worksheets("Blending Details").Range("A2:Z500"), 2, False) End If End Sub Adjust to suit your situation, but if your lookup range contains numbers, then lookup with a Number for best results (given you are looking for a number). -- Regards, Tom Ogilvy "gregork" wrote in message ... Hi, I have the following code for inserting data on a user form based on a number on a cell in a worksheet: Private Sub ComboBox1_Click() TextBox2.Text = WorksheetFunction.VLookup(ComboBox1.Text, Worksheets("Blending Details").Range("A2:Z500"), 2, False) End Sub When I put a word in the combo box the lookup is successful and I get the value returned in textbox2. But when a number is in the combox I get a runtime error. Any ideas? Thanks gregork |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Userfrm Lookup Works For text not Number
Only reason it wouldn't work that I can see is if your data in blending
details is numeric in nature but being stored as text. But then that wouldn't explain your original problem. -- Regards, Tom Ogilvy "gregork" wrote in message ... Thanks for your reply Tom. I'm sorry to be a nuisance but I can't get the code to work. I think its probably a case of putting the code in the wrong event.Here's the code I have thus far hopefully you can point me in the right direction. Private Sub UserForm_Initialize() Dim i As Integer For i = 2 To 500 Me.ComboBox1.AddItem Worksheets("Blending Details").Cells(i, 1) ComboBox1.Value = "" TextBox1.Value = "" Next End Sub -------------------------------------------------------------------- Private Sub ComboBox1_Click() If Not IsNumeric(ComboBox1.Text) Then TextBox2.Text = Application.VLookup(ComboBox1.Text, _ Worksheets("Blending Details").Range("A2:Z500"), 2, False) TextBox3.Text = Application.VLookup(ComboBox1.Text, _ Worksheets("Blending Details").Range("A2:Z500"), 1, False) TextBox4.Text = Application.VLookup(ComboBox1.Text, _ Worksheets("Blending Details").Range("A2:Z500"), 3, False) TextBox10.Text = Application.VLookup(ComboBox1.Text, _ Worksheets("Blending Details").Range("A2:Z500"), 3, False) Else TextBox2.Text = Application.VLookup(CDbl(ComboBox1.Text), _ Worksheets("Blending Details").Range("A2:Z500"), 2, False) TextBox3.Text = Application.VLookup(CDbl(ComboBox1.Text), _ Worksheets("Blending Details").Range("A2:Z500"), 1, False) TextBox4.Text = Application.VLookup(CDbl(ComboBox1.Text), _ Worksheets("Blending Details").Range("A2:Z500"), 3, False) TextBox10.Text = Application.VLookup(CDbl(ComboBox1.Text), _ Worksheets("Blending Details").Range("A2:Z500"), 3, False) End If End Sub "Tom Ogilvy" wrote in message ... Assume your combobox / textboxes are on a userform: Dim rng as Range, i as long, cell as Range i = 0 set rng =Worksheets("Blending Details").Range("A2:A500") for each cell in rng if trim(lcase(cell.Text)) = trim(lcase(Combobox1.Text)) then i = i + 1 controls("Textbox" & i).Value = cell.offset(0,1).Value controls("Textbox" & i).Tag = cStr(cell.row-1) end if Next Based on a later question you asked, I added the row-1 of the value in the tag property. Then if you want to change that value, you would get the tag property from the textbox to get the index into the range Dim lngIdex as Long, rng as Range set rng =Worksheets("Blending Details").Range("A2:A500") lngIdex = clng(Textbox1.Tag) rng(lngIdex).offset(0,1).Value = Textbox1.Text -- Regards, Tom Ogilvy "gregork" wrote in message ... Many thanks Tom your code works perfectly. Unfortunately I now have another small problem...The number I am looking up for a reference to a row on my worksheet........ [(ComboBox1.Text), _ Worksheets("Blending Details")]... can be listed more than once. So in text box2 I want to refer to the first occurrence of the ComboBox1.Text then in text box3 I want to refer to the second occurrence of the ComboBox1.Text (if there is one) and so on and so on. Sounds real complicated but my sheet is like a record of order details so each record has an order number (the number I am looking up) but many records can have the same order number as they are individual components of the same order. I hope your not confused after that lot ( I know I am). Thanks Again gregork "Tom Ogilvy" wrote in message ... Because you are looking up a string and not a number Private Sub ComboBox1_Click() if not isnumeric(ComboBox1.Text) then TextBox2.Text = Application.VLookup(ComboBox1.Text, _ Worksheets("Blending Details").Range("A2:Z500"), 2, False) Else TextBox2.Text = Application.VLookup(cdbl(ComboBox1.Text), _ Worksheets("Blending Details").Range("A2:Z500"), 2, False) End If End Sub Adjust to suit your situation, but if your lookup range contains numbers, then lookup with a Number for best results (given you are looking for a number). -- Regards, Tom Ogilvy "gregork" wrote in message ... Hi, I have the following code for inserting data on a user form based on a number on a cell in a worksheet: Private Sub ComboBox1_Click() TextBox2.Text = WorksheetFunction.VLookup(ComboBox1.Text, Worksheets("Blending Details").Range("A2:Z500"), 2, False) End Sub When I put a word in the combo box the lookup is successful and I get the value returned in textbox2. But when a number is in the combox I get a runtime error. Any ideas? Thanks gregork |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lookup column using text not col number | Excel Worksheet Functions | |||
| Lookup existence of a cell phone text number | Excel Discussion (Misc queries) | |||
| Excel 2002: How to lookup the last number and text in a column ? | Excel Discussion (Misc queries) | |||
| Lookup functions in Works Spreadsheet 8.0 | Excel Worksheet Functions | |||
| lookup or find matching number in text string | Excel Worksheet Functions | |||
Hybrid Mode
