Finding highest value in array

[Rasm]

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

[Rasm]

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

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

[excelgeek]

Assuming a 1-based array, you could use:

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.

[Rasm]

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.

[Admin]

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.

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

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

[Rasm]

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