Sorting of text data different in Excel adn VBA? Office 2K3

R

ROCKnRLR

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.
 
T

Tom Ogilvy

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.
 
R

ROCKnRLR

NickHK said:
*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.>
Click to expand...
*[/QUOTE]

THANKS!!
 
N

NickHK

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
 
Top