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