Excel Sort Order doesn't match comparison

[ozloz]

Hi all
I am trying to so something which I thought would be very simple but am tearing my hair out.
I am trying to work through two lists to update values that match in both worksheet 1 and 2, and add values from worksheet 2 that don't have a corresponding entry in WS 1

Do do this I could skip down each entry in WS1, use find to see if there is a matching entry in WS1. If so, update it. If not, add it. Simple but slow if there are a lot of records

So I thought I could sort them both by the key field (computer name) and then bubble my way down.
* If the entry in WS1 is less than the current entry in WS2, add it and move both to the next
* If they are the same, update it and move both to the next
* If the entry in WS2 is greater than the current entry in WS1, ignore it and move to the next

BUT Excel doesn't seem to sort in the same order than it compares. For example excel sorts a list like this:
Worksheet 1:
ACT-P222H
ADWIN7VM
AG_TESTBC
AG100000020
AG100000021
AG100000022

WorkSheet 2:
Skipping

 

[ozloz]

Sorry, can find how to edit as it posted accidentally. Anyway I carry on:
Worksheet 1:
ACT-P222H
ADWIN7VM
AG_TESTBC
AG100000020
AG100000021
AG100000022

Worksheet 2:
ACT-P222H
AG100000020
AG100000021
AG100000022

Skipping through and checking,
if I use if (WS2.value < WS1.Valut) then

AG_TESTBC compares greater than AG100000020
? "AG_TESTBC" < "AG100000020"
False


Does anybody know how it is possible to either sort in the same order that Excel will compare or change the way I compare values?

Many thanks

 

[ozloz]

Amazing how immediately after posting a question the answer comes to you.
It seems to work correctly if I use strComp(WS1.value,WS2.Value,vbTextCompare)
Doh!