Max value in array and return adjacent column (similar to offset/match in excel)

[drex79]

Hello,

I have been trying to use more arrays for storing data, however after much searching, I haven't been able to find a solution to my problem.

I am looking to now find a MAX value in one column on an array and, once determined return the adjacent column in record (similar to an offset/match in excel).

Here is a small test sub to better explain:




Sub my_test_array()


Dim arr(1 To 10, 1 To 2) As Variant
Dim i As Integer
Dim j As Integer
Dim s As String


'ratios
arr(1, 2) = "23"
arr(2, 2) = "24"
arr(3, 2) = "50"
arr(4, 2) = "60"
arr(5, 2) = "90" '<----------- this is the largest value in the array
arr(6, 2) = "80"
arr(7, 2) = "50"
arr(8, 2) = "12"
arr(9, 2) = "11"
arr(10, 2) = "2"


'time values
arr(1, 1) = "11:45"
arr(2, 1) = "12:00"
arr(3, 1) = "12:15"
arr(4, 1) = "12:30"
arr(5, 1) = "12:45" '<----------- would like associated value returned
arr(6, 1) = "13:00"
arr(7, 1) = "13:15"
arr(8, 1) = "13:30"
arr(9, 1) = "13:45"
arr(10, 1) = "14:00"


'show array in msgbox
For i = LBound(arr, 1) To UBound(arr, 1)
For j = LBound(arr, 2) To UBound(arr, 2)
s = s & vbCrLf & arr(i, j)
Next j
Next i


MsgBox s


End Sub




Can anyone point me in the right direction? I though maybe it be best to write a function to loop through the array, but I think I may be over my head.

Thanks in advance!

Neil

[Bob Phillips]

Use some Excel functions in VBA


MsgBox Application.Transpose(Application.Index(arr, 0, 1))(Application.Match(Application.Max(x), Application.Transpose(Application.Index(arr, 0, 2)), 0))

[drex79]

Use some Excel functions in VBA


MsgBox Application.Transpose(Application.Index(arr, 0, 1))(Application.Match(Application.Max(x), Application.Transpose(Application.Index(arr, 0, 2)), 0))



Thanks for the suggestion. Part of my problem is solving for X (in your example). So 2 part question;

How do I determine the max value in the column 2 in my array?
Once determined, how do I return the matching value in that record in column 1?

[SamT]

See snb's VBA for smarties Contents (http://www.snb-vba.eu/inhoud_en.html) for more than you ever wanted to know about arrays.


Dim Mx As long

For j = LBound(arr, 2) To UBound(arr, 2)
If arr(j,2) > Mx Then
Mx = arr(j,2)
i = j
End If
Next

MsgBox arr(i, 1)

[drex79]

See snb's VBA for smarties Contents (http://www.snb-vba.eu/inhoud_en.html) for more than you ever wanted to know about arrays.


Dim Mx As long

For j = LBound(arr, 2) To UBound(arr, 2)
If arr(j,2) > Mx Then
Mx = arr(j,2)
i = j
End If
Next

MsgBox arr(i, 1)


Thanks SamT! And wow, what a reference. Certainly bookmarked!

Not sure if this was a challenge, but it seems to have only looped through the first 2 items in the array, returning "12:00" instead of "12:45".

Removed the ,2 in the Upper Lower boundaries and it worked!


Dim Mx As long

For j = LBound(arr) To UBound(arr)
If arr(j,2) > Mx Then
Mx = arr(j,2)
i = j
End If
Next

MsgBox arr(i, 1)

[SamT]

:)

[Bob Phillips]

Thanks for the suggestion. Part of my problem is solving for X (in your example). So 2 part question;

How do I determine the max value in the column 2 in my array?
Once determined, how do I return the matching value in that record in column 1?


Sorry about that, I had created an inytermediate step that I laoded into x. Without it it is


MsgBox Application.Transpose(Application.Index(arr, 0, 1))(Application.Match(Application.Max(Application.Transpose(Application.Ind ex(arr, 0, 2))), Application.Transpose(Application.Index(arr, 0, 2)), 0))


or in steps


aryCol1 = Application.Transpose(Application.Index(arr, 0, 1))
aryCol2 = Application.Transpose(Application.Index(arr, 0, 2))
MaxValueinCol2 = Application.Max(aryCol2)
MsgBox aryCol1(Application.Match(MaxValueinCol2, aryCol2, 0))