Finding highest value in array

ExcelGeek
Not sure how to use the code example you show here - what does aplication.large return

Excelgeek
I got this working - so that is cool - anyway I can get the coordinates of the elements as well - so I know the position in the array where it found the ranked value?

It seems that the WorksheetFunction.Match is the one to use - to find the position in the array - But my array is 20 by 20 - so I cannot get Match to work

Code:

---

Avar=Application.WorksheetFunction.Large(CorrelMatrix, 1)

---

Assuming a 1-based array, you could use:

Code:

---

for n = lbound(correlmatrix, 2) to ubound(correlmatrix, 2)
varmatch = application.match(avar, application.index(correlmatrix, 0, n))
if not iserror(varmatch) then
  msgbox "x=" & varmatch & "; y=" & n
end if
next n

---

though I suspect it would be as fast if not faster to simply iterate every element rather than using worksheet functions on arrays.

Excelgeek
I will try it - but I think you are right about simply using a loop and find it that way instead - it may be faster with the loop - thanks.

Hi Rasm,

It's not a bad idea to write the array in a worksheet, use some formulas and get back the results in an array.

Here is a routing which might help you.

Code:

---

Function SORTMATRIX(ByRef Matrix, ByRef ObvsDown, ByRef ObvsAcross) As Variant
   
    Dim UB1        As Long
    Dim UB2        As Long
    Dim strMatrix  As String
    Dim strObvsD    As String
    Dim strObvsA    As String
    Dim strMaxVals  As String
    Dim strRank    As String
   
    Dim wksTemp    As Worksheet
   
    With Application
        .ScreenUpdating = 0
        .DisplayAlerts = 0
    End With
   
    If TypeOf Matrix Is Range Then Matrix = Matrix.Value
    If TypeOf ObvsDown Is Range Then ObvsDown = ObvsDown.Value
    If TypeOf ObvsAcross Is Range Then ObvsAcross = ObvsAcross.Value
   
   
    Set wksTemp = ThisWorkbook.Worksheets.Add
   
    UB1 = UBound(Matrix, 1)
    UB2 = UBound(Matrix, 2)
   
    With wksTemp
        .Range("b2").Resize(UB1, UB2).Value = Matrix
        .Range("a2").Resize(UB1).Value = ObvsDown
        .Range("b1").Resize(, UB2).Value = ObvsAcross
        .Range("a:c").EntireColumn.Insert
        strMatrix = .Range("e2").Resize(UB1, UB2).Address
        strObvsD = .Range("d2").Resize(UB1).Address
        strObvsA = .Range("e1").Resize(, UB2).Address
        strMaxVals = .Range("b2").Resize(UB1).Address
        strRank = .Range("c2").Resize(UB1).Address
       
        .Range("b2").Resize(UB1).Formula = "=MAX(INDEX(" & strMatrix & ",0,MATCH(d2," & strObvsA & ",0)))"
        .Range("a2").Resize(UB1).Formula = "=INDEX(" & strObvsD & ",MATCH(b2,INDEX(" & strMatrix & ",0,MATCH(d2," & strObvsA & ",0)),0))"
        .Range("c2").Resize(UB1).Formula = "=RANK(B2," & strMaxVals & ")+COUNTIF($B$2:B2,B2)-1"
       
        With .Range("a2").Resize(UB1, 3)
            .Value = .Value2
            .Sort .Cells(1, 2), 2, Header:=2
            SORTMATRIX = .Cells(1).Resize(UB1, 2)
        End With
    End With
    wksTemp.Delete
   
    With Application
        .ScreenUpdating = 1
        .DisplayAlerts = 1
    End With
   
End Function

---

and call

Code:

---

Sub kTest()
   
    Dim a
   
    a = SORTMATRIX([b2:u21], [a2:a21], [b1:u1])'the range could be CorrelMatrix
   
End Sub

---

Been away - just seen this - I will try it - thanks