こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

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

やりたい事は↓

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

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

よろしくお願いします。

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

投稿日時 - 2010-03-12 18:15:17

QNo.5746451

困ってます

質問者が選んだベストアンサー

編集メニューの置換を使って数式文字列を直接置換する,といった操作を新しいマクロの記録でマクロに録って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関数などを組み合わせて取ってこさせる手もあります

投稿日時 - 2010-03-12 19:00:33

お礼

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

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

投稿日時 - 2010-03-15 18:00:35

ANo.1

このQ&Aは役に立ちましたか?

4人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.2

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

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

投稿日時 - 2010-03-13 12:58:27

お礼

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

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

投稿日時 - 2010-03-15 18:02:37

あなたにオススメの質問