Finding max value in Array (vba)

[VBAn00b]

Dear forum members,

I have googled for hours but couln't find a understandable code that helps me to do the following:

Find the max. value within an array

I have a multi dimentional array with the date, the fund and the stock price. I need to calculate the max so I can find the time with the highest stock price within the array.

I hope someone can help me back on my way to solve this problem!

 

[SteveO59L]

Try posting a sample of your data and the required result.

 

[MisterCrash]

I don't think you need VBA for this. Suppose your dates are in cells A1:A200, the fund names are in cells B1:B200, and the prices are in cells C1:C200. In cell D1, enter this formula:

=B1&C1

Copy this formula down through cell D200. Now, enter the fund name that you want to analyze in cell F1. Below that, in cell F2, enter the following array formula, and hit [Ctrl] + [Shift] + [Enter] (not just [Enter]):

=LARGE(($B$1:$B$200=$F$1)*($C$1:$C$200), 1)

If you used [Ctrl] + [Shift] + [Enter], you will see braces ({}) around the formula. This will find the largest price for the fund. Now, enter the following in cell F3:

=INDEX($A$1:$A$200, MATCH($F$1&$F$2, $D$1:$D$200, 0))

This will look up the first date that the highest price is hit for that fund.

Does that help?

 

[VBAn00b]

[FONT=Arial]Dim y As Integer<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT]
[FONT=Arial]Dim Maximum As String<o:p></o:p>[/FONT]
[FONT=Arial]Maximum = 0<o:p></o:p>[/FONT]
[FONT=Arial]<o:p> </o:p>[/FONT]
[FONT=Arial]For y = 1 To UBound(vtresult)<o:p></o:p>[/FONT]
[FONT=Arial]    If vtresult(y, a, 0, 1) > Maximum Then<o:p></o:p>[/FONT]
[FONT=Arial]        Maximum = vtresult(y, 0, 0, 1)<o:p></o:p>[/FONT]
[FONT=Arial]    End If<o:p></o:p>[/FONT]
[FONT=Arial]Next<o:p></o:p>[/FONT]
[FONT=Arial]<o:p> </o:p>[/FONT]
[FONT=Arial]Range("K5") = Maximum[/FONT]

We have the above code, de a stands for the number of companies. We need to search the max for all companies and place this in a new array but we are stucked and don't know how to procede!

 

[iliace]

Basic algorithm for finding the max is like this:

Public Function FindMax(arr() As Variant, col As Long) As Long
  Dim myMax As Long
  Dim i As Long
  
  For i = LBound(arr, 1) To UBound(arr, 1)
    If arr(i, col) > myMax Then
      myMax = arr(i, col)
      FindMax = i
    End If
  Next i
End Function

FindMax(arr, 3) will return the index position of the largest number in column 3. You can then use that to return a corresponding value in a different column.

 

[VBAn00b]

Placing the max to an array seems to work now, but instead of stepping from a = a + 1 the process skips a = 1 and after de first loop it recognise a as 2 instead of 1. How is this possible and how can we fix this?

Dim maxima() As Variant<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
   ReDim maxima(company)<o:p></o:p>
   <o:p></o:p>
Dim y As Integer<o:p></o:p>
Dim Maximum As String<o:p></o:p>
<o:p></o:p>
For a = 0 To company<o:p></o:p>
For y = 0 To UBound(vtresult)<o:p></o:p>
   If vtresult(y, a, 0, 1) >= Maximum Then<o:p></o:p>
       Maximum = vtresult(y, a, 0, 1)<o:p></o:p>
   End If<o:p></o:p>
Next<o:p></o:p>
maxima(a) = Maximum<o:p></o:p>
a = a + 1<o:p></o:p>
Maximum = 0<o:p></o:p>
Next

 

[claimed88]

Basic algorithm for finding the max is like this:

Public Function FindMax(arr() As Variant, col As Long) As Long
  Dim myMax As Long
  Dim i As Long
  
  For i = LBound(arr, 1) To UBound(arr, 1)
    If arr(i, col) > myMax Then
      myMax = arr(i, col)
      FindMax = i
    End If
  Next i
End Function

FindMax(arr, 3) will return the index position of the largest number in column 3. You can then use that to return a corresponding value in a different column.

Can you also give the respective code for finding the minimum? Just switching > to < doesn't do the trick :D
Held would be much appreciated.

Many thanks!

 

[NdNoviceHlp]

Remove the a= a + 1 The loop already is incrementing a. HTH. Dave

 

[claimed88]

Remove the a= a + 1 The loop already is incrementing a. HTH. Dave

But there is no a=a+1 in the maximization function given in my quote. Are you referrin to the question asked above mine?

 

[NdNoviceHlp]

Yes. U need to start a new thread. Dave