drex79
02-10-2016, 09:18 AM
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
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