VBAにおいてのVLOOKUPを使った自動入力（特定のセルに検索値を入れると検索値をみて自動入力されるようにしたい）

Question

VBA初心者です。
質問させてください。

上記のような表があり、例えば、B4に100を入力すればA2の100を検索値として
C4~F4までを自動で入力されることは可能でしょうか？

これまでVLOOKUP関数を使っていたのですが、VBAで対応したいと思っており質問させていただきました。

Answer 1

VBEditor画面にブックやシートがツリー表示されている部分があると思います。
ここで対象とするシートをダブルクリックして開くと、そのワークシート用のコード入力画面が表示されます。

ワークシート用のコード入力画面では、そのワークシート用の関数を作ったり、ワークシート上で発生するイベントに対する処理を記述したりできます。

今回の場合だと、「セルに値が入力された時」に、「その値を検索して、紐づく値を他のセルに入力する」というのが処理の概要だと思います。

つまりセルの値が変更(Change)された時のイベントに、目的の処理を記述すれば実現できます。

入力画面の左上のドロップダウンリストは最初「(General)」が選択されていると思いますが、これを「Worksheet」に変更します。
そして右側のドロップダウンリストで「Change」を選択すると、「Worksheet_Change」というメソッドが自動的に作られるとおもいます。

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

これがワークシート上のセルに変更(Changeイベント)が発生した場合に処理される関数です。
（どのセルが変更されたかをTarget引数として受け取っています。）

このイベント関数に「値を検索して、紐づく値を他のセルに入力する」という処理を記述すれば、目的の動作をさせることができると思います。

---

なお、注意点が２点あります。

①Worksheet_Changeイベントは、全てのセルで発生する

対象ワークシートのセルであれば、どのセルが変更された場合にもChangeイベントが発生しています。
特に何もしたくない、処理とは関係のないセルが変更された時でも、イベントは発生してしまいます。

このため、イベント処理の先頭で「Targetが対象範囲内のセルでなければ何もせず処理を抜ける」という処理を記載するのがセオリーとなっています。

Private Sub Worksheet_Change(ByVal Target As Range)
	If Target.Column <> 2 Then Exit Sub	'B列以外の変更なら処理を抜ける
	'以下、B列に変更があった時の処理を記述
	
End Sub

　

②Ctrl+Zなどが効かなくなる

これはVBAで作成したマクロからセルを変更した場合についてまわる問題なのですが、Ctrl+Zなどによるやりなおしが効かなくなります。

VlookUpなどのExcel関数を使い、ワークシート上の操作だけで入力や計算などをしている分には間違えてしまったときにCtrl+Zで一手ずつ戻すことができるのですが、マクロからセルの値を変更してしまうとそれ以前には巻き戻せなくなってしまいます。

VBAは複雑な処理ができる反面こういったデメリットもありますので、導入の際には頭の片隅に置いてご検討ください。

Answer 2

vlookup関数をC4:F4に入れておいてB4に値を入れるとC4:F4に検索結果を入れることは可能ですが、VBAでマクロを作った場合、B4に値を入れるだけではC4:F4に検索結果を入れることはできません。
B4に値を入れた後、マクロを起動してやる必要があります。
VBAで関数(function)を作った場合ならばvlookup関数と同様の操作で結果は得られます。
VBAならばvlookupと違って検索範囲よりも左側のセルの値も取り出せるので、必要ならば作ってみてください。
ps. 「作ってください」はここでは推奨されていません。