Sort decending/acending list with macro

[aRangerpete]

Hello

We are trying to do some calculations but something goes wrong. In column J you can see some numbers and in some cells we have equal numbers. We want to sort the numbers in column L and K related to the numbers in column J. We want to run the calculations first in J followed by L and K. The numbers in L and K should sort ascending when we run our macro. If you look in the cells in J39 and J40 we have the same numbers (17). But the numbers in L39, L40 should sort ascending. The right calculation should give 2 in L39 and 4 in L40. For K 39 should that be 4 and and 15 in K40. The same problem occurs when we have several equal numbers in column J.

I hope someone can take a little time to solve our problem

Best regards  

Sub sortera()

Dim counter As Integer

counter = 3
For i = 2 To 4

For i2 = 3 To 15

Cells(counter, 13) = counter - 2
Cells(counter, 10) = Cells(i2, i)
Cells(counter, 11) = i2
Cells(counter, 12) = i

counter = counter + 1
Next i2

Next i

Call SortMultipleColumns

End Sub

Sub SortMultipleColumns()

Range("J3:L41").Sort Key1:=[J3], Order1:=xlDescending, _
Key2:=[L3], Order2:=xlAscending, _
Key3:=[K3], Order3:=xlAscending

Call placeNewRank

End Sub

Sub placeNewRank()

For i = 3 To 41
Cells(Cells(i, 11), Cells(i, 12) + 4) = Cells(i, 13)
Next i

End Sub

[#2]

@aRangerpete 

Could you attach a small sample workbook or make it available via OneDrive, Google Drive or similar, so that we can experiment?

[#3]

@Hans Vogelaar does this work?

https://1drv.ms/u/s!Akq4T6vFl0myaQGCGq3ITdP1EeE?e=peaK4L

Best regards

Peter

[#4]

@aRangerpete 

I have downloaded your sample workbook - thanks!

I see the problem; it appears to be a bug in the way Excel handles a mix of ascending and descending sort orders. I don't see an easy solution - let's see if someone else comes up with a bright idea.

[#5]

@Hans Vogelaar
My father found a solution. He found that it is not always enough to change from text to numbers via Format Cells. At first the list was in TEXT-formatted numbers and he changed these with Format Cells for Excel to format them as real numbers, but it was not enough. He had to use a formula called NUMBER VALUE for excel to accept the numbers, if you run the table through that formula it works.