Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Microsoft Excel - Macros - Search - Search Sequentially ======

<code excel>
' Tries to find vCrit within oArrWithCrit.
' If found then return corresponding oArrWithValues otherwise vDefault.
Function ArrayFindSequential(oArrWithCrit As Variant, vCrit As Variant, oArrWithValues As Variant, vDefault As Variant)

    Dim vArr1 As Variant
    Dim vArr2 As Variant
    Dim lRow As Long
    ArrayFindSequential = vDefault
    If (UBound(oArrWithCrit) - LBound(oArrWithCrit)) = (UBound(oArrWithValues) - LBound(oArrWithValues)) Then
        For lRow = LBound(oArrWithCrit, 1) To UBound(oArrWithCrit, 1)
            If oArrWithCrit(lRow, 1) = vCrit Then
                ArrayFindSequential = oArrWithValues(lRow, 1)
                Exit Function
            End If
        Next
    Else
        ArrayFindSequential = "Criteriarange and sum range must be of same length"
    End If

    
    ' Clear all objects.
    Set vArr1 = Nothing
    Set vArr2 = Nothing

End Function
</code>

and

<code excel>
' Tries to find vCrit within oArrWithCrit.
' If found then returns vFoundValue otherwise vNotFoundValue.
'=ArrayFind(A1:A1000,"Foo",B1:B1000)
Function ArrayFindSequentialEx(oArrWithCrit As Variant, vCrit As Variant, vFoundValue As Variant, vNotFoundValue As Variant)
    Dim vArr1 As Variant
    Dim vArr2 As Variant
    Dim lRow As Long
    ArrayFindSequentialEx = vNotFoundValue
    For lRow = LBound(oArrWithCrit, 1) To UBound(oArrWithCrit, 1)
        If oArrWithCrit(lRow, 1) = vCrit Then
            ArrayFindSequentialEx = vFoundValue
            Exit Function
        End If
    Next

    
    ' Clear all objects.
    Set vArr1 = Nothing
    Set vArr2 = Nothing

End Function
</code>