Help- Simple function fails working with pasted values
 

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

Help- Simple function fails working with pasted values
 

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"
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

Help- Simple function fails working with pasted values
 

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



On Jul 17, 12:17*am, "Jim Cone" wrote:
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 *USAhttp://www.mediafire.com/PrimitiveSoftware

"Mel"
wrote in ...
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

Help- Simple function fails working with pasted values
 

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




--
Dave Peterson

Help- Simple function fails working with pasted values
 

On Fri, 16 Jul 2010 21:09:08 -0700 (PDT), 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").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



When you Paste a numeric value into a cell formatted as Text, the
value remains numeric. My guess is that the source for your
PasteSpecial operation has the numbers formatted as numbers.

You can check this by using appropriate ISTEXT functions to see what
you really have in those cells.

Help- Simple function fails working with pasted values
 

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