データ検索 配列 覚書

by

実用的かも

Public Function GetMakerbyID(intID As Long) As String
    Dim SearchRange As Range
    Dim MyAray      As Variant
    Dim i           As Long
    GetMakerbyID = ""
    MakerM.Select
    Set SearchRange = Range(Cells(1, 1), Cells(Rows.Count, 2).End(xlUp))
    '配列格納
    MyAray = SearchRange
    '格納した配列をループ
    For i = LBound(MyAray) To UBound(MyAray)
    '検索ワードに一致したら取得
        If MyAray(i, 1) = intID Then
            GetMakerbyID = MyAray(i, 2)
            Exit For
        End If
    Next i
End Function

速度的にも問題はない。１回でも複数回でも実用的。