Finding index value in a large array RRS feed

  • 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
            MsgBox varLowest & " not found!"
        End If

    End Sub

    Regards, OssieMac

    Tuesday, May 17, 2016 7:15 AM