Excel sort vs VBA

[Guest]

This is part of a sequence that was sorted in ascending order in Excel...

Ad Spend - Arbeitsagentur - Press (€)
Ad Spend - Arbeitsagentur - Trade Magazines/Press ($)
Ad Spend - Arbeitsagentur - Trade Magazines/Press (€)
Ad Spend - Arbeitsagentur (Group Label)
Ad Spend - Cadremploi - Online ($)
Ad Spend - Cadremploi - Online (£)

When running a loop in VBA, the value in the 4th line is less than the value
in the 3rd. Why did is this so?

Is there a way of comparing cells so that the hireachy is the same as Excel
uses for sorting.

Currently I am using..

if range(CellAdress).value < range(CellAdress.offset(1,0)).value then...

 

[Norman Jones]

Hi Glynn,

Perhaps:

Ad Spend - Arbeitsagentur (Group Label)

should be

Ad Spend - Arbeitsagentur - (Group Label)

( missing hyphen ?)

 

[Guest]

The problem is not what the line should say, it is how Excel has sorted the
line compared to how visual basic regards the data. Excel says one is greater
than the other, while VBA says that one is less than the other.

To distill the problem to the bare minimum..

In Excel the formula =IF("-">"(","Yes","No") gives the result "No"

In VBA the routine...

Sub Test()
If "-" > "(" Then
MsgBox "Yes"
Else
MsgBox "No"
End If
End Sub

....gives the result "Yes"

 

[Bob Phillips]

VBA looks right to me. Asc("-") is 45, Asc ("(") is 40, so it should be. Why
sort does it the other way, is beyond me at this minute.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Peter T]

There are many differences between comparisons in Excel & VBA, particularly
with different data types. However in this particular case to get VBA to
simulate Excel head your module
Option Compare Text

and you should find the result of your test demo is reversed to same as
Excel

But need to do a lot more to fully simulate Excel

Regards,
Peter T