EXCEL VBA で VLOOKUP的な機能

質問

お世話になります

=VLOOKUP($G6,マスタ２!$A$2:$F$27,2,FALSE)
で値を取得できる状況で
それを$G6が変更されたイベントで値をセルにセットするようにしたいのですがどのようにすればいいでしょうか？

複数人でコピー＆ペーストしたりするので
セルに式を埋め込まずに実現したい事情があります。

よろしくお願いします。

回答No.3

tom04

こんばんは！

＞VLOOKUP的な機能
とありますので、一例です。
画面左下のG6セルがChangeするSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペースト　→　Excel画面に戻り、G6セルのデータを色々変更してみてください。

尚、結果を表示させるセルは同じSheetのA2セルとした場合です。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
Dim c As Range
If Target.Address = "$G$6" Then
Set c = Worksheets("マスタ2").Range("A:A").Find(what:=Target, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Range("A2") = c.Offset(, 1) '←A2セルに表示
Else
MsgBox "該当データなし"
End If
End If
End Sub 'この行まで

※　コード内の「A2]部分は実際に表示させたいセル番地にしてください。m(_ _)m

回答No.2

eden3616

No1に追記です。

>$G6が変更されたイベントで値をセルにセットするようにしたいのですがどのようにすれば

「G6」を変更するシートを右クリックしてコードの表示に以下のコードを貼り付けてください。
セルG6に値を入力すると結果がダイアログで表示されます。

■VBAコード

Private Sub Worksheet_Change(ByVal Target As Range)
Dim 値 As Variant

'見つからなかった場合エラーとなるのでエラー対策
On Error Resume Next

'変更されたセルがG6以外なら終了
If Target.Address(False, False) <> "G6" Then Exit Sub

'Vlookupで値を取得
値 = WorksheetFunction.VLookup(Range("G6"), Sheets("マスタ２").Range("A2:F27"), 2, False)

'値の有無を判定して結果表示
If Len(値) = 0 Then
　　MsgBox "見つかりませんでした"
Else
　　MsgBox 値
End If

End Sub

回答No.1

eden3616

VBAであってもエクセルの関数は利用できます。
記述は以下のようになります。

WorksheetFunction.関数名(引数)

（例）
合計 = WorksheetFunction.Sum(Range("A1:A3"))

ですのでVlookup関数も以下のように利用できます。
(範囲A1:B3において、A4と一致する行のB列の値を取得する場合)

値 = WorksheetFunction.VLookup(Range("A4"), Range("A1:B3"), 2)