Sorting order please help!

[m4a1]

Hi everyone. I encounter a problem regarding the coding of a function which is designed to sort the (n by 3) matrix by largest value e.g

transform
CHF EUR 0.923126
CHF USD 0.154571
EUR USD 0.254522

into
CHF EUR 0.923126
EUR USD 0.254522
CHF USD 0.154571
here is my coding:

Function Sorting(vector1 As Variant)
Dim n, k, i, j As Integer
Dim temp As Variant
n = vector1.Rows.Count
ReDim temp(1 To n, 1 To 3)
For j = 1 To n
k = n
For i = 1 To n
If (Abs(vector1(j, 3))) > (Abs(vector1(i, 3))) Then
k = k - 1
End If
Next i

temp(k, 1) = vector1(j, 1)
temp(k, 2) = vector1(j, 2)
temp(k, 3) = vector1(j, 3)
Next j
Sorting = temp
End Function
I dont know where went wrong, i m an beginner, please help!!!!!

[mdmackillop]

Hi,
Welcome to VBAX
An array doesn't have countable rows in that fashion
try

Function Sorting(vector1 As Variant)
Dim n, k, i, j As Integer
Dim temp As Variant
n = UBound(vector1)
ReDim temp(1 To n, 1 To 3)
For j = 1 To n
k = n
For i = 1 To n
If (Abs(vector1(j, 3))) > (Abs(vector1(i, 3))) Then
k = k - 1
End If
Next i
temp(k, 1) = vector1(j, 1)
temp(k, 2) = vector1(j, 2)
temp(k, 3) = vector1(j, 3)
Next j
Sorting = temp
End Function



or, If you are getting data from a range


Sub DoSort()
Dim arr As Range
Set arr = Range("Data")
Rws = arr.Rows.Count
Cells(10, 1).Resize(Rws, 3) = Sorting(arr)
End Sub
Function Sorting(vector1 As Variant)
Dim n, k, i, j As Integer
Dim temp As Variant
n = vector1.Rows.Count
ReDim temp(1 To n, 1 To 3)
For j = 1 To n
k = n
For i = 1 To n
If (Abs(vector1(j, 3))) > (Abs(vector1(i, 3))) Then
k = k - 1
End If
Next i
temp(k, 1) = vector1(j, 1)
temp(k, 2) = vector1(j, 2)
temp(k, 3) = vector1(j, 3)
Next j
Sorting = temp
End Function

[m4a1]

Hi mdmackillop,

Thanks for your help. i found out the the original codes are ok, the function works ,there was a mistake i made when implementing the function,lol. however, according to my original coding, i spotted a problem, which is that the function doesnt work when two or matrix value are the same. i think in the if statement, i need to make modification. any suggestions?

please see the attachment.

[mdmackillop]

Function Sorting(vector1 As Variant)
Dim n, k, i, j, m As Integer
Dim temp As Variant
n = vector1.Rows.Count
ReDim temp(1 To n, 1 To 3)

For j = 1 To n
k = n
For i = 1 To n
If (Abs(vector1(j, 3))) > (Abs(vector1(i, 3))) Then
k = k - 1
End If

Next i
'test for prior equal value; if found; enter above
For m = 1 To n
If vector1(j, 3) = temp(m, 3) Then
k = m - 1
Exit For
End If
Next
temp(k, 1) = vector1(j, 1)
temp(k, 2) = vector1(j, 2)
temp(k, 3) = vector1(j, 3)
Next j
Sorting = temp
End Function