エクセルＶＢＡの構文を教えてください。

質問

こんばんは。マクロ初心者です。
ＶＢＡの構文を教えてください。

■シート(1)には項目が５０ある５年分（60ヶ月）の市場統計データーがあります。５０個ある項目名はＢ列にありＤセル2008年1月からＢＫセル2012年12月まで横に60ヶ月分のデーターを入力しています。
■シート(2)には５０の項目名のデータがあります。項目名はＢ列にあります。
■シート(3)にはコンボボックスを作成し、コンボボックスの下に表を作成しました。

シート(3)のコンボボックスの50ある項目から▼ボタンで項目を選びコマンドボタンを押すと
シート(3)の下記のセル範囲でシート(1)のデーターを参照させたいのです。
Ｂ８セルからＭ８セルまで（2008年度12か月分のデータ）
Ｂ11セルからＭ11セルまで（2009年度12か月分のデータ）
Ｂ14セルからＭ14セルまで（2010年度12か月分のデータ）
Ｂ17セルからＭ17セルまで（2011年度12か月分のデータ）
Ｂ20セルからＭ20セルまで（2012年度12か月分のデータ）

コンボボックスは設定できたのですが、構文がうまくできないため参照できていません。
エクセルＶＢＡに詳しい方、どうぞご教授ください。よろしくお願い致します。

回答No.2

KURUMITO · ベストアンサー

前回のご質問で答えたのですが追加のご質問を見ておりませんで失礼をいたしました。
コンボボックスの設定はうまくできているとのことですがコンボボックスには「フォームコントロール」からのものと「ActiveXコントロール」からのものと2種類があります。おそらく前者で作られたコンボボックスではないでしょうか？
ここでは後者の「ActiveXコントロール」からコンボボックスを作ります。
シート3でその操作を行ってシート3にコンボボックスを作ります。
コンボボックスを右クリックして「コードの表示」を選択します。
表示の画面に次のマクロを入力します。

Sub リスト設定()
Application.ScreenUpdating = False
Worksheets("Sheet3").ComboBox1.Clear
With ComboBox1
For i = 2 To 50
.AddItem Worksheets("Sheet2").Range("B" & i)
Next
End With
Application.ScreenUpdating = True
End Sub

また同時に次のマクロを入力します。

Private Sub ComboBox1_Change()
Dim n, i As Integer
Set WS1 = Worksheets("Sheet1")
Set WS3 = Worksheets("Sheet3")
With WS1.Range("B:B")
For n = 1 To 50
If WS1.Cells(n, 2).Value = ComboBox1.Value Then Exit For
Next
For i = 1 To 12
WS3.Cells(8, 1 + i) = WS1.Cells(n, 3 + i).Value
WS3.Cells(11, 1 + i) = WS1.Cells(n, 15 + i).Value
WS3.Cells(14, 1 + i) = WS1.Cells(n, 27 + i).Value
WS3.Cells(17, 1 + i) = WS1.Cells(n, 39 + i).Value
WS3.Cells(20, 1 + i) = WS1.Cells(n, 51 + i).Value
Next
End With
End Sub

このマクロではコンボボックスで指定した50項目のうちの一つの項目についてシート1の3＋i列、つまりはi=1の時はD列、から51+i列、つまりi=1の時はBK列までをシート3に12が月ごとに分けて表示することになります。

マクロの入力が終わったら「開発」タブの「デザインモード」をクリックしてアクティブ状態を解除します。
次の「マクロ」から「Sheet3リスト設定」を選択して実行ボタンをクリックします。この操作によってシート3のコンボボックスにはリストが表示されるようになります。
リストからある項目を選択することで自動的にシート3にデータが表示されます。

データー元であるシート(1)のＢ３からＢＫ５２の範囲データーはどこに書かれておりますでしょうかなどのご質問の内容からすればマクロの初心者にしてはまだまだ努力が足りないような気がしますね。
この種の内容はマクロを使うことなく関数のみを使って十分対応できますので上記のようなマクロではなくそちらで対応されてはいかがでしょう。

お礼

こんばんは。
問題なく作動しました。ありがとうございます。

ＶＢＡは融通のきくツールだと直感的に感じています。
ＶＢＡを習得するために、コードを書いて覚え実行し、
一日でも早く『便利』と感じるよう向上心を持って習得したいと思います。
ありがとうございました。

回答No.1

shino719

こんばんわ。

当方エクセル2000ですが、文章での説明は難しいので
実際にサンプルを作成してみました。

参照URLに作成したサンプルのURLを載せてみますので、
ダウンロードできればサンプルエクセルが見えると思います。

しかしこのサンプルには下記制限があります。
・マクロは使用せずに数式だけで作っています。
・2008年1月～2012年12月固定で、2013年1月など増えた場合は未対応。
・コンボボックスは入力規則で作っています。
・Sheet2の項目名だけあるシートの存在が良く分からなかったので未使用。

こんなものでいいのかどうか分かりませんが、ご参考までに。。

参考URL：

http://www.geocities.jp/shinopo/test4.zip

お礼

こんばんは。
上記リンクアドレスにアクセスしましたが、『ページが利用できなくなっているというメッセージ』が
あり参照することができませんでした。
そのような状態ではありますが、実際にサンプルまで作って下さってありがとうございました。