Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting of text data different in Excel adn VBA? Office 2K3
Hello guys, I found this forum by looking up the meaning of error 2042.
Thanks to you guys now I know that the value looked up is not found in the range. And there it lies the problem! I have a very large list from which the values can be looked up, so, I created a small VBA function that breaks down the list in smaller ranges and then using a Select statement it looks up a value within a range. If I put in Excel the following, the answer is TRUE: = "EP102RB" < "EP-1080SD" If I open an immediate window in VBA, the following returns FALSE: PRINT "EP102RB" < "EP-1080SD" How can that be? BTW, I never saw this happenign in 95, 97, 2K. --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting of text data different in Excel adn VBA? Office 2K3
In the Excel help on sort order:
Text and text that includes numbers are sorted in the following order: 0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = A B C D E F G H I J K L M N O P Q R S T U V W X Y Z Apostrophes (') and hyphens (-) are ignored, with one exception: If two text strings are the same except for a hyphen, the text with the hyphen is sorted last. I don't believe VBA follows this convention. -- Regards, Tom Ogilvy "ROCKnRLR " wrote in message ... Hello guys, I found this forum by looking up the meaning of error 2042. Thanks to you guys now I know that the value looked up is not found in the range. And there it lies the problem! I have a very large list from which the values can be looked up, so, I created a small VBA function that breaks down the list in smaller ranges and then using a Select statement it looks up a value within a range. If I put in Excel the following, the answer is TRUE: = "EP102RB" < "EP-1080SD" If I open an immediate window in VBA, the following returns FALSE: PRINT "EP102RB" < "EP-1080SD" How can that be? BTW, I never saw this happenign in 95, 97, 2K. --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting of text data different in Excel adn VBA? Office 2K3
This wasn't like this in the previous versions of Excel that i have use
(95,97,2000). Or at least I just noticed this behavior. Why? [image http://www.vortexmediagroup.com/images/banghead.gif] -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting of text data different in Excel adn VBA? Office 2K3
NickHK wrote:
*ROCKnRLR, If you put an "Option Compare Binary" at the top of the form/module you get False, "Option Compare Text" returns True. VBA defaults to Binary, whereas Excel is Text. * [/color] THANKS!! -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting of text data different in Excel adn VBA? Office 2K3
ROCKnRLR,
If you put an "Option Compare Binary" at the top of the form/module, you get False, "Option Compare Text" returns True. VBA defaults to Binary, whereas Excel is Text. NickHK "ROCKnRLR " wrote in message ... Hello guys, I found this forum by looking up the meaning of error 2042. Thanks to you guys now I know that the value looked up is not found in the range. And there it lies the problem! I have a very large list from which the values can be looked up, so, I created a small VBA function that breaks down the list in smaller ranges and then using a Select statement it looks up a value within a range. If I put in Excel the following, the answer is TRUE: = "EP102RB" < "EP-1080SD" If I open an immediate window in VBA, the following returns FALSE: PRINT "EP102RB" < "EP-1080SD" How can that be? BTW, I never saw this happenign in 95, 97, 2K. --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Text Data | Excel Discussion (Misc queries) | |||
Excell /office 2003 Data Sorting | Excel Discussion (Misc queries) | |||
Sorting in Excel - Office Pro 2003 | New Users to Excel | |||
Merging excel office data into a publisher office project(Australi | New Users to Excel |