CODEWITHSUNDEEP
arraysexcelvbafind
Yousaf
Yousaf

Reputation: 137

Returning an Index of a Min value in Array Excel VBA

I'm trying to find an index of the minimum value from sum(3) array. And it should be assigned to a variable min

min = index of minimum value

Should I sort the array first or is there any direct way of doing it?
Here is my code: 

 `Sub Min_index()
        Dim Pt_array(3) As Single
        Pt_array(0) = 0
        Pt_array(1) = 12.3
        Pt_array(2) = 16.06
        Pt_array(3) = 20.11

        Dim Ad_E_array(3) As Single
        Dim Lo_E_array(3) As Single
        Dim Bs_temp As Single

        Dim i As Integer
        i = 0
        Do While i < 4
            Bs_temp = BS
            Ad_E_array(i) = Ad_E    'defined in previous Sub
            Lo_E_array(i) = Lo_E    'defined in previous Sub

            If Bs_temp + Pt_array(i) - Qth < BS_Maximum_limit Then
                Bs_temp = Bs_temp + Pt_array(i) - Qth
                Ad_E_array(i) = Ad_E_array(i) + 0
                Lo_E_array(i) = Lo_E_array(i) + 0
                Call function_decide(int_forecast_hour - 1, Bs_temp, Qth + 1, Lo_E_array(i), Ad_E_array(i))
            Else
                Lo_E_array(i) = Pt_array(i) - Qth - (BS_Maximum_limit - Bs_temp)
                Bs_temp = BS_Maximum_limit
                Call function_decide(int_forecast_hour - 1, Bs_temp, Qth + 1, Lo_E_array(i), Ad_E_array(i))
            End If
        i = i + 1
        Loop

        Dim sum(3) As Single
        Dim min As Single
        i = 0
        Do While i < 4
        sum(i) = Abs(Lo_E_array(i)) + Abs(Ad_E_array(i))
        i = i + 1
        Loop
  End Sub`

Upvotes: 3

Views: 24424

Answers (1)

user4039065
user4039065

Reputation:

You can receive the 1-based index position of the element containing the minimum value with the an Excel Application object's use of the worksheet's MIN function and MATCH function.

Sub wqewuiew()
        Dim Pt_array(3) As Single, p As Long
        Pt_array(0) = 1000
        Pt_array(1) = 12.3
        Pt_array(2) = 16.06
        Pt_array(3) = 20.11

        p = Application.Match(Application.Min(Pt_array), Pt_array, 0)

        Debug.Print p   '<~~ 'p' is 2 (I changed the value of the first array element)
End Sub

Upvotes: 4

Related Questions