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 said:
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
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
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
Hi,
I have the following code for inserting data on a user form
