VBA key comparison (lt/gt/eq) different in Excel than VBA

[polarisking]

I'm doing a straightforward two file match and I have an instance where I expect to advance the key in File A because it's less than the key in File B. Doing a comparison in Excel, it's less than, but the VBA compare says is greater than.

Here are the two values:
1799tmp_0_13846IIE
1799XNAIIE12XNAIIE12IIE

If you test the first value for GT the second, Excel says FALSE.
If you go to an immediate window and type

if "1799tmp_0_13846IIE" > "1799XNAIIE12XNAIIE12IIE" then debug.Print "Yes"

It comes back "Yes".

I'm baffled, as if appears to be an issue of CASE where the code for "t" is 116 and for X it's 88. If that's the issue, how do I make the VBA eq/gt/lt test case-insensitive?

 

[SirJB7]

Hi, polarisking!

Excel IF function is case insensitive, so "t" in fact is considered as "T" and "T" isn't greater than X. For comparison of equal use the function EXACT, for greater or lower, well, test for equal, be kind. Redmond guys didn't think... about it neither.

VBA instruction IF is case sensitive, so that's why you get what expected.

Regards!

 

[NARAYANK991]

Hi ,

In VBA , when ever you want a case-insensitive comparison of two text strings , always convert them to the same case , using the LCase or UCase function on both text strings ; thus if you have two text strings A and B , then use :

LCase(A) = LCase(B)

or

UCase(A) = UCase(B)

to compare two text strings. If you know in advance that any one of these strings will be either lower case or upper case , you can avoid the function for that string e.g. if you know that B will always be an upper case string then , you can use :

UCase(A) = B

Narayan