How to find the position of maximum value in VBA array ?

[ajay1111]

Hello,
I have dynamic size of array and i want to find the position array(selected) which contains maximum value.
For example : Array size is 30 i.e Arr(30) and i want to find the position of maximum value among Arr(4), Arr(5) and Arr(6) only (Not all).

The code i tried does not work as intended as it gives position of maximum value of Arr(4), Arr(5) and Arr(6) among all arrays i.e Arr(30).
While I want position of maximum value of Arr(4), Arr(5) and Arr(6) among arrays Arr(4), Arr(5) and Arr(6) only.

Eg: If Arr(5) contains maximum value among Arr(4), Arr(5) and Arr(6) then "5" should be returned

Sub posi()

  Dim position As Long
  
  position = WorksheetFunction.Match(WorksheetFunction.Max(Arr(1),Arr(2),Arr(3)), Arr(), 0)

End Sub

 

[JoeMo]

Maybe something like this (run this code on a new worksheet):

Sub PositionOfMax()
Dim A(1 To 30), first, last, M, N As Long
'populate the array
For i = LBound(A) To UBound(A)
    A(i) = WorksheetFunction.RandBetween(1, 31)
Next i
' select start and end elements
first = 4: last = 6
'find max and position of max
For i = first To last
    If A(i) > M Then
        M = A(i)
        N = i
    End If
Next i
Range("A1:A30").Value = Application.Transpose(A)
MsgBox "Max value is :" & M & " in position " & N
End Sub

 

[ajay1111]

Thank you for the help sir ! But i wanted to know if it is possible without using "loop" and just using worksheet function to achieve this !

maximumvalue=WorksheetFunction.Max(Arr(4),Arr(5),Arr(6))
position = WorksheetFunction.Match(maximumvalue, Arr(), 0)

Here, The position of maximumvalue is searched in all arrays: Arr(0) to Arr(30) due to use of Arr() in above code.
Is there a way search position of maximumvalue in Arr(4), Arr(5) & Arr(6) only by changing Arr() to something else !

 

[JoeMo]

Don't think so. And, your position variable may not even correspond to one of the array elements among Arr(4), Arr(5), and Arr(6) if an earlier element [Arr(0) to Arr(3)] has the same maximumvalue since the Match function will find the relative position of the first matching element in Arr.

 

[Peter_SSs]

But i wanted to know if it is possible without using "loop" and just using worksheet function to achieve this !

Try this (though I'm not sure it's any better than looping)

Sub Position_Of_Max()
  Dim A(1 To 30), first As Long, last As Long, i As Long, Pos As Long, SubA As Variant
  
  'populate the array
  For i = LBound(A) To UBound(A)
      A(i) = WorksheetFunction.RandBetween(1, 31)
  Next i
  ' select start and end elements
  first = 4: last = 6
  
  'Extract Sub-Array
  SubA = Application.Index(A, Application.Transpose(Evaluate("row(" & first & ":" & last & ")")))
  
  'Calculate sub-array position and adjust to main array position
  Pos = WorksheetFunction.Match(WorksheetFunction.Max(SubA), SubA, 0) + first - 1
  
  MsgBox Pos
End Sub

 

[Alex Blakenburg]

See if this does what you are after.

Sub GetMaxPosInGivenRange()

    Dim rng As Range
    Dim arr As Variant
    
    Set rng = Range("A1:A14")
    arr = rng
    
    Dim position As Long
    Dim maximumvalue As Long    ' Change to Double if decimal
    
    maximumvalue = Application.Max(Application.Index(arr, Array(4, 5, 6), 0))
    position = Application.Match(maximumvalue, Application.Index(arr, Array(4, 5, 6), 0), 0)
    Debug.Print "Position on specified range: " & position

End Sub