VBA doesn't compares correctly via greater than string comparison

I have a sorting going on that looks like this

Code:

---

     
                If istArray(k, 4) > sollArray(l, 4) Then   
                'Ist > soll -> soll first
                    finalArray(i, 1) = sollArray(l, 1)
                    finalArray(i, 4) = sollArray(l, 2)
                    finalArray(i, 5) = sollArray(l, 3)
                    l = l + 1
                Else  'Ist < soll -> ist first
                    finalArray(i, 1) = istArray(k, 1)
                    finalArray(i, 2) = istArray(k, 2)
                    finalArray(i, 3) = istArray(k, 3)
                    k = k + 1
                End If

---

For k = 1 I got the Word "ERR_BSUP_EL_OC" in istArray and for l = 1 the word "ERR_BSU_STELPMPDFCT" in sollArray. Both are written in uppercase (UCASE()) to be sure to compare correctly. When I hover over the variables in Visual Basics I see these entries getting compared. istArray should be greater > than sollArray. Therefor the first part should be activated. But it doesn't! The second part after the else gets triggered. I don't get why.

If I use Excels IF("ERR_BSUP_EL_OC">"ERR_BSU_STELPMPDFCT_VW";"Yes";"No") in Excel directly it even says "yes". What am I missing here? :banghead:

BTW the sorting works superb except for these two inputs, where the sorting gets "out of line".

The 8th character of istArray is P, which is ASCII 80.

The 8th character of sollArray is _, which is ASCII 95.

80 is not greater than 95, so the test fails.

I'd assume VBA is doing a binary comparison by default. You could use

Code:

---

if strcomp(istArray(k, 4), sollArray(l, 4), vbtextcompare) = 1 Then

---

Quote:

---

Originally Posted by Aflatoon

I'd assume VBA is doing a binary comparison by default. You could use

Code:

---

if strcomp(istArray(k, 4), sollArray(l, 4), vbtextcompare) = 1 Then

---

---

Like Bob Phillips wrote the ASCII compare for "_" and "P" states, that "_" is smaller than "P" what I wouldn't have guessed. Especially when Excel itself says that "_" is smaller than "P".

The full idea is to do something like this:
- If the strings are the same, write everything in one line
- If on string is smaller than the other, write the smaller one in the line.

So it's kind of a sorting. Of course I could use the strcomp() for the first part, but for the second part?

Did you try the code I posted?

Quote:

---

Originally Posted by Aflatoon

Did you try the code I posted?

---

Sorry I thought strcomp() was only for equal. Tried it and it works perfect. :bow:

It will do less than, equal, or greater than, and return -1, 0 and 1 as appropriate. :)