コンボボックスの記述の簡素化

質問

下記の記述でコンボボックスにデーターを表示するようにしたのですが
大量に記述が必要になります。
もっと簡素化できないでしょうか。
よろしくお願いします。

If Worksheets("Sheet2").Range("C3").Value = Worksheets("Sheet2").Range("C2").Value Then
品名リスト.RowSource = "Sheet2!D3：D18"
ElseIf Worksheets("Sheet2").Range("C4").Value = Worksheets("Sheet2").Range("C2").Value Then
品名リスト.RowSource = "Sheet2!E3：E20"
―　―　省略　―　―
ElseIf Worksheets("Sheet2").Range("C20").Value = Worksheets("Sheet2").Range("C2").Value Then
品名リスト.RowSource = "Sheet2!U3：U20"
ElseIf Worksheets("Sheet2").Range("C21").Value = Worksheets("Sheet2").Range("C2").Value Then
品名リスト.RowSource = "Sheet2!V3：V20"

回答No.2

ponta1971 · ベストアンサー

お礼見ました。
説明ですね。

a[X][2] = {{"C3","Sheet2!D3：D18"},{"C4","Sheet2!E3：E20"},・・・,{"20","Sheet2!U3：U20"},{"21","Sheet2!V3：V20"}}

この部分で、それぞれのIf文の中で値が変わる部分を配列に入れています。
｛｝の中にさらに｛｝があります、この中の値と書かれているソースを見比べてもらえれば、どの値を入れているのか分かるかと思います。
ただ、見直したところ、少し間違えていました。

a[X][2] = {{"C3","Sheet2!D3：D18"},{"C4","Sheet2!E3：E20"},・・・,{"C20","Sheet2!U3：U20"},{"C21","Sheet2!V3：V20"}}

こちらの記述になります。
ここでXの値ですが、If～ElseIf～ElseIf・・・を何回繰り返すかによります。
5回繰り返せばX=5になります。
配列に格納される値ですが、この場合。
a[0][0] = "C3"、a[0][1] = "Sheet2!D3：D18"
a[1][0] = "C4"、a[1][1] = "Sheet2!E3：E20"
・・・
a[X-2][0] = "C20"、a[X-2][1] = "Sheet2!U3：U20"
a[X-1][0] = "C21"、a[X-1][1] = "Sheet2!V3：V20"
このようになります。

For i = 0 To X-1
　If Worksheets("Sheet2").Range(a[i][0]).Value = Worksheets("Sheet2").Range("C2").Value Then
　　品名リスト.RowSource = a[i][1]
　　break
　End If
Next i

For文とIf文の書き方は調べていただくとして。
For文でi=0からi=X-1までiを1増やしながら繰り返しています。
Xは配列の数になります。
i=1からi=Xでないのは、配列を利用するときにキー（配列の[]の中の数字）は0から始まるためです。
If文を見てもらえば、書かれていたソースのIf文とほとんど同じだと分かると思います。
違う箇所はa[i][0]とa[i][1]の部分です。
i=0の場合、上の配列の部分で書いたa[0][0]とa[0][1]の値が入ることになります。
If文がTRUEになって処理が実行されれば、breakの処理が行われ、For文のループを抜け、以後の処理は実行されません。

どうも、説明が苦手なので分かりにくいかと思いますが・・・。
こんな感じでどうでしょうか？

お礼

ponta1971さん
親切なご回答いただきましてありがとうございました。
感謝申し上げます。
解決できました。

回答No.3

ki-aaa

こんにちわ

Dim n As Long

With Worksheets("Sheet2")
For n = 3 To 21 'c3 to c21
If .Cells(n, 3).Value = .Cells(2, 3).Value Then
'品名リスト.RowSource = "Sheet2!D3：D18"
'Debug.Print "Sheet2!" & Cells(3, n + 1).Resize(16, 1).Address(0, 0)
品名リスト.RowSource = "Sheet2!" & Cells(3, n + 1).Resize(16, 1).Address(0, 0)
Exit For
End If
Next n
End With

お礼

ki-aaaさん
回答いただきましてありがとうございます。
勉強させていただきます。

回答No.1

ponta1971

>If Worksheets("Sheet2").Range("C3").Value = Worksheets("Sheet2").Range("C2").Value Then
品名リスト.RowSource = "Sheet2!D3：D18"

このIF文の中で"C3"の部分と"Sheet2!D3：D18"以外は全て同じようですので。
この部分を配列に入れておいてFOR文で処理を回すのでどうでしょう。

--------------------------------------------

a[X][2] = {{"C3","Sheet2!D3：D18"},{"C4","Sheet2!E3：E20"},・・・,{"20","Sheet2!U3：U20"},{"21","Sheet2!V3：V20"}}

For i = 0 To X-1
If Worksheets("Sheet2").Range(a[i][0]).Value = Worksheets("Sheet2").Range("C2").Value Then
品名リスト.RowSource = a[i][1]
break
End If
Next i

--------------------------------------------
文法がまちがってるかもしれませんが、こんな感じでどうでしょう。
Xは配列のサイズの応じて適切な値を入れてください。

お礼

ponta1971さんありがとうございます。

高度過ぎて私には理解できませんが、説明文を付けていただけないでしょうか。

また、この適切な値とは何を指すのでしょうか。

勉強不足ですみません。
よろしくお願いします。