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

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

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

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

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

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

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

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

Userfrm Lookup Works For text not Number
 

Hi Tom,
Could you please tell me what event I should put your code in to? ie Private
Sub ComboBox1_Click()
or Private Sub UserForm_Initialize()
or should it be in a textbox event?

Kind Regards
gregork

"Tom Ogilvy" wrote in message
...
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

Userfrm Lookup Works For text not Number
 

I assume you want the textboxes filled with a selection is made in the
combobox, so the click event for the combobox is appropriate.

--
Regards,
Tom Ogilvy

"gregork" wrote in message
...
Hi Tom,
Could you please tell me what event I should put your code in to? ie
Private
Sub ComboBox1_Click()
or Private Sub UserForm_Initialize()
or should it be in a textbox event?

Kind Regards
gregork

"Tom Ogilvy" wrote in message
...
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

Userfrm Lookup Works For text not Number
 

Hi Tom,
The click event for the combobox is where I had the code. When I run the
userform I get a error message"Could not find the specified object" then the
debug highlights the following line of the code:
Controls("TextBox" & i).Value = cell.Offset(0, 2).Value
I notice when I place the cursor over the value it is returning the value it
should be (the second listing of combobox1 on my worksheet).
Am I supposed to have put in a reference in the line:
Controls("TextBox" & i).Value = cell.Offset(0, 2).Value?

Many thanks for your help.

Kind Regards
gregork

"Tom Ogilvy" wrote in message
...
I assume you want the textboxes filled with a selection is made in the
combobox, so the click event for the combobox is appropriate.

--
Regards,
Tom Ogilvy

"gregork" wrote in message
...
Hi Tom,
Could you please tell me what event I should put your code in to? ie
Private
Sub ComboBox1_Click()
or Private Sub UserForm_Initialize()
or should it be in a textbox event?

Kind Regards
gregork

"Tom Ogilvy" wrote in message
...
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