Help- Simple function fails working with pasted values

[Mel]

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").EntireColumn, 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

 

[Jim Cone]

Your code worked for me. It returned False.
MyCell is the name for cell C2,
C is range("C5") and contains "75630084212"...

If IsError(Application.Match(c.Value, Worksheets("MySheet").Range("MyCell").EntireColumn, 0)) Then
c.Offset(0, 1).Value = True
Else
c.Offset(0, 1).Value = False
End If
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware




"Mel" <[email protected]>
wrote in message
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").EntireColumn, 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

 

[Mel]

But did you create the entries in MyCell's column by pasting them in
as values, not manually entering them? Otherwise, yes, it works for me
too.

Do this to see it without having to use VBA:

In a sheet, create the following formula in F3:
=ISERROR(MATCH("75630084212",A:A,0)), which results in TRUE because
nothing is in column A.

Now manually type "75630084212" into a cell in column and see the
formula give a FALSE because it finds the value.

Now delete all entries in column a, type "75630084212" in cell F5, and
copy that cell. Right click a cell in column A and paste special -
values. See the value in column A yet see the formula still shows
TRUE, meaning it cannot find the value that is actually there.

It's the same root issue in VBA. How do we get around it? It has to be
pasted as values - I cannot change that. How do I get Excel to see the
value that is, in fact, there?

TIA,
Melina

 

[Dave Peterson]

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.

 

[Mel]

Thanks everyone! I used your suggestions and it worked.