Thanks everyone! I used your suggestions and it worked.



On Jul 17, 5:58*am, Dave Peterson wrote:
Remember that the format of the cell doesn't change the underlying value of that
cell.

If you have a number (not just digits) in a cell, then format the cell as Text,
the value is still a number.

If you change the entry (just hitting F2, then enter), then this change will
make the digits Text.

I think you have a couple of choices. *Convert all your text numbers to number
numbers or treat all your entries as text. *But this means you'll (probably)
have to make sure the table is correct, too.

The other option would be to look twice -- once using numbers and once using text.

Dim res as variant
dim LookUpRng as range
dim myVal as variant

...
myval = c.value
set lookuprng = worksheets("mysheet").range("MyCell").entirecolumn

'look for text
res = Application.Match(myval & "", lookuprng, 0)
if iserror(res) then
* *'look as number (but only if it looks like a number
* *if isnumeric(myval) then
* * * res = application.match(myval + 0, lookuprng, 0)
* *end if
end if

if iserror(res) then
* *msgbox "no match found"
else
* *msgbox "found on row: " & res
end if

=======
Untested, uncompiled. *Watch for typos.

On 07/16/2010 23:09, Mel wrote:



Excel 2003

I have a simple problem where pasted data is not recognized by a
function and need help.

The range "MyRange"
* • is just a group of cells formatted as text.
* • The first cell value is "75630084212" (text).

The column in Worksheets("MySheet") that has Range("MyCell"):
* • The column's cells are all formatted as text.
* • The column has about 20 entries, all numerical in appearance, but
are text.
* • The entries were pasted all at once as PasteSpecial xlPasteValues
* • The column has "75630084212" in one of its cells (the same string
in "MyRange").

I run my code and the snippet below gives an undesired result. It
determines the value is NOT in the column when it actually IS.

For Each c In Range("MyRange")

* * *If IsError(Application.Match(c.Value, _
* * * * *Worksheets("MySheet").Range("MyCell").EntireColum n, 0)) = True
Then

It deems the function as true when it should be false because the
string IS in the column.

BUT THEN, in the range the function is trying to find a match, I
double click into the cell that has the match and then escape back out
without making a change. I run the code again, and now IT WORKS. So,
entering into the matched cell and back out again did something to
allow Excel's function to see it.

As corroboration, if I manually type the values into the column in
MySheet, the function works correctly then too.

What can I do about this to make the function work when working with
these value-pasted entries? Please ask I didn't make this clear
enough.

TIA,
Melina

--
Dave Peterson