Differences

This shows you the differences between two versions of the page.

--- microsoft_excel:macro_search_sequentially [2016/07/11 11:30] – created peter
+++ microsoft_excel:macro_search_sequentially [2021/08/04 14:25] (current) – removed peter
@@ Line 1: / Line 1: @@
-====== Microsoft Excel - Macro 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>