Excel VBAでVLookup関数を使って商品データから単価を自動で入力する

Excel VBAでWorksheet.Change イベントとVLookup関数を使って商品データから値を参照して自動で単価を入力する方法を紹介します。

今回のポイント

Worksheet.Change イベントはワークシートのセルの値が変更されたときに自動的に実行されます。今回はセルに商品名が入力されたときにVLookup関数が実行されて単価を表示するプログラムを作成します。

まずは次の様な「注文書」と「商品名、単価」の表をエクセルのシート1に作ります。

A8セルからA17セルに商品名を入力するとVLookup関数で右側の「商品名と単価」の表を参照してC8セルからC17セルに単価が表示されるようにしたいと思います。

エクセルの「開発」タブ →「Visual Basic」でVBE(Visual Basic Editor)の画面を表示してSheet1に下のコードをコピーして貼り付けます。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)      'セルが選択がされたときに自動的に実行されるイベント
   
   If Not Intersect(Target, Range("A3,A8:A17,B8:B17,D4,D5")) Is Nothing Then          '右のセル範囲が選択された場合は
                                                                                   
      Exit Sub                                                                      '何もせずにプログラムを終了します

   ElseIf Not Intersect(Target, Range("A18")) Is Nothing Then                              'セルA18が選択された場合は

      Application.EnableEvents = False                              'イベントループしないようにイベントを無効にします
      Range("A17").Select                                                                        'セルA17を選択します
      Application.EnableEvents = True                                                         'イベントを有効にします
   
   ElseIf Not Intersect(Target, Range("B18")) Is Nothing Then                              'セルB18が選択された場合は
   
      Application.EnableEvents = False
      Range("B17").Select                                                                        'セルB17を選択します
      Application.EnableEvents = True
   
   Else                                                                             '上記のセル以外が選択された場合は

      Application.EnableEvents = False
      MsgBox "入力するセルの範囲ではありません。"                                             'メッセージを表示します
      Application.EnableEvents = True

   End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)             'セルの値が変更されたときに自動的に実行されるイベント
    
   On Error GoTo メッセージ                              '商品名が間違っていたときは行ラベル「メッセージ:」に移動する
   
   If Not Intersect(Target, Range("A8:A17")) Is Nothing Then                    'セルA8:A17の値が変更された場合の処理
    
      If Target.Value = "" Then                                             'セルA8:A17に文字が入力されていない場合は

         Application.EnableEvents = False                           'イベントループしないようにイベントを無効にします
         Target.Offset(0, 2) = ""                                                    '2列右のセルの内容をクリアします
         Application.EnableEvents = True                                                      'イベントを有効にします

      Else                                                                        '商品名が正しく入力された場合の処理

         Application.EnableEvents = False
         Target.Offset(0, 2) = WorksheetFunction.VLookup(Target, Range("F:G"), 2, False)     'VLookup関数を実行します
         Application.EnableEvents = True

      End If
       
   End If
   
   Exit Sub                                                                                   'プログラムを終了します

メッセージ:                                                                                       'エラー発生時の処理

   Application.EnableEvents = False
   MsgBox "商品名が違います。"                                                                  'エラーメッセージ表示
   Target.Offset(0, 2) = ""                                                          '2列右のセルの内容をクリアします
   Application.EnableEvents = True
    
End Sub

「Worksheet_SelectionChangeイベント」で入力できるセルの範囲の指定を行っています。「シートの保護」をしても同じ設定を行えますが今回はVBAで設定してみました。

「Worksheet_Changeイベント」でセルの範囲「A8:A17」の値を変更したら2列右の「C8:C17」のセルにVLookupで参照した単価を表示する様にしています、また「A8:A17」のセルが空白の場合は「C8:C17」のセルも空白になるようにしています。

Target.Offset(0, 2) の前後にはWorksheet.Changeイベントがセルの範囲「A8:A17」の書き換え1回で終わるように「Application.EnableEvents プロパティ」を使ってイベントを無効にして、セルの値を変更した後にはイベント有効にする処理を入れています。

商品名を間違って入力したときはOn Error GoToステートメントでエラーメッセージを表示した後に「Application.EnableEvents プロパティ」でイベント有効にしてからプログラムを終了します。

これで商品名をA8セルからA17セルに入力するとC8セルからC17セルに単価が表示されるようになりました。