EXCEL VBAでVLOOKUP関数を表現
VLOOKUP関数は、実務でよく使われています。また、VLOOKUP関数を使える使えないで、EXCELの習得度の判定をされることもあります。VLOOKUP関数は便利な機能ですが、物足りないところもあります。今回はVBAでVLOOKUP関数を表現するだけではなく、その足りないところも補完するように書いてみました。
VLOOKUP関数とは
=VLOOKUP(A3,function!$G$2:$I$18,2,0) A3が検索値、範囲G2:I18内のG列を探索して、A3と完全一致したら、G2:I18表の2列目値をB2に出力します。
とても便利ですが、動画で解説しているように、マスター表に存在しない品番を入力したり、マスター表に同じ品番が重複している場合、ユーザーとしては、物足りない印象を受けます。VBAでVLOOKUP関数を表現するときは、そこを補完してみました。
VBA
エクセルのシートをクリックしたときに発火するプログラムですので、Sheet3(vba)のエディターにコードを書いていきます。
code = ActiveCell.Offset(-1, 0) → アクティブなセルの上のセルの値を変数codeに格納しています。
for文で変数codeと表内G列を一致する値がないか探索させます。
一致したら、8列目、9列目の値をアクティブなセルの上の行の右1列目、2列目に設置します。
一致したら、boolean型変数flagをTRUEとします。FALSEであったら、マスターに一致する品番がないことをメッセージ表示します。
一致したら、変数kaisu を1づつ加算します。kaisuが1より大きければ、マスター内に品番が重複していることをメッセージ表示します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim code As String 'セルの値を入れる変数 Dim i As Integer 'マスター表の行番号 Dim flag As Boolean '完全一致したらTRUE 一致しなかったらFALSEを返す変数 Dim kaisu As Integer '初期値は０、マスター表で一致する毎に１づつ増える code = ActiveCell.Offset(-1, 0) If Len(code) = 4 And Target.Column = 1 And (Target.Row >= 3 And Target.Row <= 12) Then 'MsgBox (code) For i = 2 To 18 If code = Cells(i, 7) Then 'MsgBox ("一致します") ActiveCell.Offset(-1, 1) = Sheets("vba").Cells(i, 8) ActiveCell.Offset(-1, 2) = Sheets("vba").Cells(i, 9) flag = True kaisu = kaisu + 1 End If Next i If flag = False Then MsgBox ("マスターに一致する品番は有りません") ActiveCell.Offset(-1, 1) = "" ActiveCell.Offset(-1, 2) = "" End If If kaisu > 1 Then MsgBox ("同じ品番が２個以上マスターに存在します") End If End If End Sub |
ですが、そもそも品番H003とか覚えられないですよね？次回の記事では、覚えなくてもよいような処理を公開します。
次回もよろしくお願いいたします。
