How do I change this to lookup a word in the range i specified. It works
with numbers but not letters

ans = Application.Match(CLng(TextBox2.Text), Range("A1:A100"), 0)


Thanks in advance

Greg

if isnumeric(Textbox2.Text) then
ans = Application.Match(CLng(TextBox2.Text), Range("A1:A100"), 0)
else
ans = Application.Match(TextBox2.Text, Range("A1:A100"), 0)
End if

--
Regards,
Tom Ogilvy


"Greg" wrote in message
...
How do I change this to lookup a word in the range i specified. It works
with numbers but not letters

ans = Application.Match(CLng(TextBox2.Text), Range("A1:A100"), 0)


Thanks in advance

Greg

CLng() converts the numeric string to real numbers.

If you drop that from your statement, you'll be looking for a text match:

ans = Application.Match(TextBox2.Text, Range("A1:A100"), 0)



Greg wrote:

How do I change this to lookup a word in the range i specified. It works
with numbers but not letters

ans = Application.Match(CLng(TextBox2.Text), Range("A1:A100"), 0)

Thanks in advance

Greg

--

Dave Peterson

Thank you Tom

Exactly what I needed

Greg

Thanks Dave

Dropping the clng alone won't do if your A1:A100 contains a mix of numbers
and text (where cells have real numbers stored as numbers).

If that is the case, see my suggestion.

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote in message
...
CLng() converts the numeric string to real numbers.

If you drop that from your statement, you'll be looking for a text match:

ans = Application.Match(TextBox2.Text, Range("A1:A100"), 0)



Greg wrote:

How do I change this to lookup a word in the range i specified. It
works
with numbers but not letters

ans = Application.Match(CLng(TextBox2.Text), Range("A1:A100"), 0)

Thanks in advance

Greg

--

Dave Peterson

Yep.



Tom Ogilvy wrote:

Dropping the clng alone won't do if your A1:A100 contains a mix of numbers
and text (where cells have real numbers stored as numbers).

If that is the case, see my suggestion.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
CLng() converts the numeric string to real numbers.

If you drop that from your statement, you'll be looking for a text match:

ans = Application.Match(TextBox2.Text, Range("A1:A100"), 0)



Greg wrote:

How do I change this to lookup a word in the range i specified. It
works
with numbers but not letters

ans = Application.Match(CLng(TextBox2.Text), Range("A1:A100"), 0)

Thanks in advance

Greg

--

Dave Peterson

--

Dave Peterson

I used the code on each textbox on the form I needed to look up so I used
the number one on tb1 and the other code on tb 2& 3. Thanks for both your
help.

It runs perfect.

Greg