エクセルVBAのIF文のときのFOR文は？

自宅学習でエクセルVBAの勉強をしています。

コンボボックスを切り替えるとテキストボックスも連動して切り替わるものを作っています。
最初はコンボボックスのリストインデックスの番号を使って
Private Sub ComboBox1_Change()
If ComboBox1.ListIndex = 0 Then TextBox1 = Range("B3").Text
If ComboBox1.ListIndex = 0 Then TextBox2 = Range("C3").Text
If ComboBox1.ListIndex = 0 Then TextBox3 = Range("D3").Text
……
If ComboBox1.ListIndex = 99 Then TextBox40 = Range("AL102").Text
と入力するんですが、リストインデックスが100、テキストボックスが40で
さすがに全部IF文の力業は現実的じゃないということで繰り返し分のFOR文を
使おうとしたんですがコンボボックスとテキストボックスを組み合わせているときの
FOR文のやり方がわかりません。諸先輩方、どうか知恵をお貸しいただけませんか？お願いします。

No.4 ベストアンサー 回答者： Qchan1962 回答日時： 2020/08/12 15:15

こんにちは、

どの様な事を行いたいのかうまく理解できていないかも知れませんが、
例えば、
If ComboBox1.ListIndex = 0 Then TextBox1 = Range("B3").Text
If ComboBox1.ListIndex = 0 Then TextBox2 = Range("C3").Text
If ComboBox1.ListIndex = 0 Then TextBox3 = Range("D3").Text
を考えた時に

Private Sub ComboBox1_Change()
　　Dim i As Long, n As Long
　　If ComboBox1.ListIndex = 0 Then
　　　　n = 3 + ComboBox1.ListIndex
　　　　For i = 1 To 3
　　　　　　Controls("TextBox" & i).Value = Sheets(1).Cells(n, i + 1).Text
　　　　Next i
　　End If
End Sub
のように書くことが出来ます。B3~D3
（シートは暫定でインデックス１のシートにしています）
For i = 1 To 3はTextBoxの添え字なので1 To 40　とすれば、TextBox1～TextBox40が対象となります。
これは、If ComboBox1.ListIndex = 0 Thenに限っていますが、これを外し

Private Sub ComboBox1_Change()
Dim i As Long, n As Long
n = 3 + ComboBox1.ListIndex
For i = 1 To 40
Controls("TextBox" & i).Value = Sheets(1).Cells(n, i + 1).Text
Next i
End Sub

のみにした場合、（IF文を外す）
For i = 1 To 40にしたので
ComboBox1.ListIndex＝0の時　B3~AO3
ComboBox1.ListIndex＝1の時　B4~AO4
ComboBox1.ListIndex＝2の時　B5~AO5
・
・
のようにセルのアドレスを変えていくように出来ます。

＞If ComboBox1.ListIndex = 99 Then TextBox40 = Range("AL102").Text
ALになっていますが、B列から40となるとAO列のような気がしますが、、、

更にシート名などを変更する場合も（該当しないかも知れませんが）
String型の変数を加え
strShname="時間割" & ComboBox1.ListIndex +1
Sheets(strShname).Cells(n, i + 1).Text
ComboBox1.ListIndex＝0の時 シート名　時間割1　を対象にする事が出来ます
この時　nは定数で良いかもしれませんが。。

この回答へのお礼

ほぼ答えを頂いた上にわかりやすい解説ありがとうございます。
IF文にこだわりすぎてFor文だけのほうが簡単だったんですね。
私の説明不足な質問に返事を頂きありがとうございました！

お礼日時：2020/08/13 12:15

No.3 回答者： naktak 回答日時： 2020/08/12 13:58

恐らくですが、時間割タブの各テキストボックスに反映される値を

Excelシート上から取ってくるわけですよね？

ということは、Excelシートでは、時間割1～4、選択したコンボボックスの種類に
応じて整形された形で定義がされているかと思います。
例えば時間割1の0時はA1、時間割2の0時はB1、のように。
更に時間割やコンボボックスごとに、Excelシートが複数あるとか。

そのルールが定まっているならば、基準となるセル位置から、コンボボックスの種類などによって位置増減をさせることで、目的のセル位置を確定させることはできませんか？

そうすると多分、ifもforもいらないと思いますね。
Excelシートの実際の状態、コンボボックスの内容の例などがなければ正解を回答することはできません。

この回答へのお礼

私のエクセルのルール等が後出しになってすみませんが、
まずは回答ありがとうございます。


番号（コンボボックスインデックス０）
名前（テキストボックス１セルB3）
0時（テキストボックス２セルC３）
……
23時（テキストボックス25セルZ3）

コンボボックスを変更することで名前と時間割セルB3～Z3を
B4～Z4、B5～Z5、……B100～Z100と切り替えています

いまはIF文の力業でやっているんですが現実的じゃないってことで
For文でできないかと思ってたんですが、コンボボックスで目的のセル位置増減や確定なんて方法もあるんですね。

For文とあわせて自分にできるやり方を検討していきたいと思います。
ありがとうございました。

お礼日時：2020/08/12 15:21

No.2 回答者： めぐみん_ 回答日時： 2020/08/12 06:03

それ以前にまず

If ComboBox1.ListIndex = 0 Then TextBox1 = Range("B3").Text
If ComboBox1.ListIndex = 0 Then TextBox2 = Range("C3").Text
If ComboBox1.ListIndex = 0 Then TextBox3 = Range("D3").Text

これって

If ComboBox1.ListIndex = 0 Then TextBox1 = Range("B3").Text:TextBox2 = Range("C3").Text:TextBox3 = Range("D3").Text

こうゆう事？
それもだけどリストインデックスと『TextBox　・　代入するセル番地』の関係がわかりません。
リストインデックスによってどこかのセルを起点にOffsetするのならそれもFor~Nextとかに組み込まないとダメでしょ？
ルールを明確にすべきではないかな。

この回答へのお礼

起点のセルを決めてからのOffset、For～Nextですか！
ヒントありがとうございます。
命令文の組み合わせをどうすればいいのかまだわかっていませんが検索しながら
勉強していきたいと思います。ありがとうございました！

お礼日時：2020/08/12 12:49

No.1 回答者： Tacosan 回答日時： 2020/08/12 05:22

ぐはっ, 配列は作れないのか. あ, でも「Controls」で代替できるか?

あとは ListIndex の値と右辺の Range の中をどう連動させるかだが... それはこの質問文じゃわからんな.

この回答へのお礼

まだVBAを始めたばかりでどう伝えればいいのかよくわかっていませんでした。
すみませんでした。

お礼日時：2020/08/12 12:51