データ量が多すぎて vlookup が限界なので vba で二次元配列でやる方法

2020/08/29

vlookup は、すごく便利だけど、データ量が多すぎると時間掛かるし、限界だと感じるので、vba でやった場合の忘備録。
基本を理解しておけば、かなり便利だから、メモメモ。

考え方としては、DBに該当するシートの内容を全部配列に入れて、検索させる感じ。

例として、
シート1 検索したいワード

シート2 データベース

実行結果

スポンサーリンク

----- べんりあつめ。-----

Sub 検索処理()

'処理の非表示
'Application.ScreenUpdating = False

'DBの最終行・最終列を取得
Dim MaxRowDB As Long, MaxColDB As Long
With Worksheets(2).UsedRange
MaxRowDB = .Find("*", , , , xlByRows, xlPrevious).Row
MaxColDB = .Find("*", , , , xlByColumns, xlPrevious).Column
End With

'配列にデータを入れる(1配列宣言、2配列範囲設定、3配列格納)
Dim DBArray() As Variant
ReDim FlagArray(1 To MaxRowDB, 1 To MaxColDB)
DBArray = Range(Worksheets(2).Cells(1, 1), Worksheets(2).Cells(MaxRowDB, MaxColDB)).Value

'ここから検索処理
Worksheets(1).Activate '念のため、処理用シートを表示する

Dim SerchNum As Long '検索回数
Dim DbNum As Long '配列DBに格納している検索回数
Dim MaxRowS1 As Long

'Sheet1の最終行を取得
With ActiveSheet.UsedRange
MaxRowS1 = .Find("*", , , , xlByRows, xlPrevious).Row
End With

For SerchNum = 1 To MaxRowS1 '検索値の数だけループ
For DbNum = 1 To MaxRowDB 'DB行数だけループ
If Cells(SerchNum, 1) = DBArray(DbNum, 1) Then
     'Ifの左の数字が検索したい列で、右の数字は配列側の検索対象列
Cells(SerchNum, 2) = DBArray(DbNum, 2)
Cells(SerchNum, 3) = DBArray(DbNum, 3)
Cells(SerchNum, 4) = DBArray(DbNum, 4)
Cells(SerchNum, 5) = DBArray(DbNum, 5)
     '左の数字が検索結果を表示したい列で、右の数字は配列側の表示させたい列
Exit For
Else
End If
Next
Next

End Sub

スポンサーリンク

スマホのみ下に表示