Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function to Sum colored cells values not working | Excel Programming | |||
'INDIRECT'ly referred cell's own simple formula fails.... | Excel Programming | |||
Validation code not working with pasted data | Excel Programming | |||
VLOOKUP stops working with pasted values | Excel Worksheet Functions | |||
Very simple function not working | Excel Discussion (Misc queries) |