ExcelのマクロによるVLOOKUPの参照シートの変更について

質問

ExcelのマクロによるVLOOKUPの参照シートの変更について

ブックＡのSheet1のセルA1にドロップダウンリストを作成し、アルファベットの"J"、"M"、"A"が選択できるようにしてあります。

ブックＡのSheet2のセルB1には、以下の式が入力してあり、別のブック（ブックＢ）から情報を読み込んでいます。
=VLOOKUP(A1,'￥￥s_g￥欠席￥[ブックＢ.xls]国語'!$B$4:$V$30,4,FALSE)

やりたい事は↓

ブックＡのSheet1のセルA1にある、ドロップダウンリストを変更すると、ブックＡのSheet2のセルB1にある式（上記式）の参照シート（上記式では「国語」の部分）が変更さえるマクロを作りたいのですが、うまくできません。

ちなみに、ドロップダウンリストの"J"を選択した場合は、ブックＢのシート"国語"を参照し、リストの"M"を選択した場合は、ブックＢのシート"数学"を参照し、リストの"A"を選択した場合は、ブックＢのシート"美術"を参照するようにしたいです。

よろしくお願いします。

また、マクロを使わずして出来るようでしたら、その方法も合わせて教えて下さい。

回答No.1

keithin · ベストアンサー

編集メニューの置換を使って数式文字列を直接置換する，といった操作を新しいマクロの記録でマクロに録ってchangeイベントマクロに仕込んでやるといった所でしょうか。

サンプル：
Sheet1のシート名タブを右クリックしてコードの表示を選んだシートに

Private Sub Worksheet_Change(ByVal Target As Range)
　Dim res As String
　If Target.Count > 1 Then Exit Sub
　If Application.Intersect(Target, Range("A1")) Is Nothing Then Exit Sub

　Select Case Target
　 Case "J"
　 res = "国語"
　 Case "M"
　 res = "数学"
　 Case "A"
　 res = "美術"
　 Case Else
　End Select

　Worksheets("Sheet2").Range("B:B").Replace _
　　what:="]*'!", _
　　Replacement:="]" & res & "'!", _
　　lookat:=xlPart
End Sub

＃エラー処理等は特に行っていません
＃その方が簡単だと思えば，選択のたびに「目的の式そのものをセルに記入させる」マクロにしてもいいです。

[別の手]
●「閉じたブック」に対する参照をINDIRECT関数で可変にとる事はできません
○目的のブックを「開いておけば」，シート名を可変にとってINDIRECT関数で参照シートを切り替えできます
○目的のブックの全データを開かれているブック(アドインや隠したブックでも可)に参照し，それを普通に検索したり，INDIRECT関数などを組み合わせて取ってこさせる手もあります

お礼

おお～！思っていた通りのものができました！
すっきりしました！

下手な説明にもかかわらず、ご回答頂きましてありがとうございました★

回答No.2

toshi_2010

マクロを使わない方法で
　ブックＡのSheet2のセルB1に以下を入力

=if(Sheet1'!A1="J",VLOOKUP(A1,'￥￥s_g￥欠席￥[ブックＢ.xls]国語'!$B$4:$V$30,4,FALSE),if(Sheet1'!A1="M",VLOOKUP(A1,'￥￥s_g￥欠席￥[ブックＢ.xls]数学'!$B$4:$V$30,4,FALSE),if(Sheet1'!A1="A",VLOOKUP(A1,'￥￥s_g￥欠席￥[ブックＢ.xls]美術'!$B$4:$V$30,4,FALSE),"")))

お礼

あっなるほどっ！そうですよね、これならできますね★
思いつきませんでした（笑）

ご回答ありがとうございました！すっきりしました！