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/

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/

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

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

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/