How to find a value in an array?

Question

I have an array:- Dim arr(0 To 5, 1 To 2) As Variant

I am able to find the minimum value in the array using:-

Debug.Print Application.Min(arr)

How do I find the values that are populated adjacent to the minimum value?

Answer 1

Here is logic for one way ( note I am reading the array in from the sheet rather than having it already specified).

I loop columns as there are less columns than rows. I seed an initial valid minimum value by taking column 1 minimum Application.WorksheetFunction.Min(Application.WorksheetFunction.Index(arr, 0, 1)) . I set this value to that of a future column minimum if that future minimum is less than the existing.

Application.WorksheetFunction.Index is used to slice the array. By specifying Index(array,0,1) I am slicing column wise as opposed to Index(array,1,0) which would be row wise. There are limitations on the array dimensions that will be handled by Index.

Option Explicit

Public Sub GetMinumum()

Dim arr()

arr = ThisWorkbook.Worksheets("Sheet3").Range("A1").CurrentRegion.Value

Dim currColumn As Long

Dim currMinimum As Double

'set starting value for comparison
currMinimum = Application.WorksheetFunction.Min(Application.WorksheetFunction.Index(arr, 0, 1))

For currColumn = LBound(arr, 2) To UBound(arr, 2)

    If Application.WorksheetFunction.Min(Application.WorksheetFunction.Index(arr, 0, currColumn)) < currMinimum Then

      currMinimum = Application.WorksheetFunction.Min(Application.WorksheetFunction.Index(arr, 0, currColumn))

    End If

Next currColumn

Debug.Print currMinimum

End Sub

Output is -3

Version 2 to get adjacent value

Option Explicit

Public Sub GetMinumum()

    Dim arr()

    arr = ThisWorkbook.Worksheets("Sheet3").Range("A1").CurrentRegion.Value

    Dim currRow As Long

    Dim currMinimum As Double
    currMinimum = arr(1, 1)
    Dim outputValue As Variant
    outputValue = vbNullString

    For currRow = LBound(arr, 1) To UBound(arr, 1)

        If arr(currRow, 1) < currMinimum Then
            currMinimum = arr(currRow, 1)
            outputValue = arr(currRow, 2)
        End If
    Next currRow

    Debug.Print outputValue

End Sub