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

注文書 Excel VBA VLookup
2024.11.19

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

今回は注文書の作成を例にしてプログラムを作っていきたいと思います。

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

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

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

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo メッセージ

If Intersect(Target, Range("A8:A17")) Is Nothing Then

   MsgBox "入力するセルの範囲が「A8:A17」ではありません。"

   Exit Sub

Else 'セルの範囲「A8:A17」の値を変更したら処理を実行します。

   If Target.Value = "" Then

      Application.EnableEvents = False

      Target.Offset(0, 2) = ""

      Application.EnableEvents = True

   Else

      Application.EnableEvents = False

      Target.Offset(0, 2) = WorksheetFunction.VLookup(Target, Range("F:G"), 2, False)

      Application.EnableEvents = True

   End If

End If

Exit Sub

メッセージ:
   
   MsgBox "商品名が違います。"
   Application.EnableEvents = True
    
End Sub

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

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

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

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

次回のブログでは商品名をリストで選択して入力出来るようにする記事を書きたいと思います。

それでは、最後までお読みいただきありがとうございました！

コメント