# Finding index value in a large array • ### Question

• ```' Below I calculate and fill an array with 649 values. I then find the minimum value in that large array. What I ' want to do is after I find the minimum value I also want to print the index value of the minimum value. Ideally I ' would be able to print that in the next cell over. Function OpExt(Cut_length As Single, Usage As Single, Cost As Single)

Dim Grip As Single ' Required Scrap in inches for either cutting or stamping an extrusion
Dim Cut_width As Single ' The amount of material removed by the saw
Dim Rungs_per_ext As Single
Dim Ext_per_day As Single
Dim Scrap_per_ext As Single
Dim Salvage_scrap_per_ext As Single
Dim Salvage_scrap_per_day As Single
Dim Salvage_lengths_per_year As Single
Dim Savings_per_year(1 To 649) As Variant
Dim i As Double
Dim j As Long

j = 1

For i = 150 To 312 Step 0.25
Grip = 4 ' The amount of scrap to cut an extrusion
Cut_width = 0.1875

Rungs_per_ext = i / (Cut_width + Cut_length)
Ext_per_day = Usage / Rungs_per_ext

If (Rungs_per_ext - WorksheetFunction.RoundDown(Rungs_per_ext, 0)) * Cut_length > (Grip + Cut_width) Then
Scrap_per_ext = (Rungs_per_ext - WorksheetFunction.RoundDown(Rungs_per_ext, 0)) * Cut_length
Else
Scrap_per_ext = ((Rungs_per_ext - WorksheetFunction.RoundDown(Rungs_per_ext, 0)) * Cut_length) + Cut_length
End If

Salvage_scrap_per_ext = Scrap_per_ext - Grip - Cut_width
Salvage_scrap_per_day = Salvage_scrap_per_ext * Ext_per_day
Salvage_lengths_per_year = (Salvage_scrap_per_day / i) * 365

Savings_per_year(j) = (Cost * Salvage_lengths_per_year)

j = j + 1

Next

OpExt = WorksheetFunction.Min(Savings_per_year()) ' I want to return the lowest amount of savings

End Function```

Monday, May 16, 2016 10:23 PM

### All replies

• The following is example code of finding the minimum value in an array and then returning the array index value of the found minimum value. Note that it returns the index of the first occurrence only of the minimum value.

Note that the example code writes the array to a worksheet so that the results can be checked so take care if you use the code that you do not leave the line in to overwrite good data on your worksheet.

Sub ReturnLowestArrayValue()

Dim arrTest() As Variant
Dim i As Long
Dim varLowest As Variant
Dim lngPosition As Long

ReDim arrTest(1 To 500)

For i = 1 To UBound(arrTest)
arrTest(i) = WorksheetFunction.RandBetween(1, 100)
Next i

varLowest = WorksheetFunction.Min(arrTest())

lngPosition = WorksheetFunction.Match(varLowest, arrTest, False)

'Following line of code writes to the Active Worksheet
Range("A1").Resize(UBound(arrTest), 1).Value = WorksheetFunction.Transpose(arrTest())

If Not IsError(lngPosition) Then
MsgBox varLowest & " is at position " & lngPosition
Else