VBA For To Next 文でComboBoxを指定？

質問

VBAでお世話になっています。
UserForm上のComboBox1～30の各値を元に
Sheet2上のデータをSheet4上に貼り付けるVBAを組んであります。

ComboBox1の値を元に行なう作業(VBA)とComboBox30を元に行なう作業は同じなのですが、
但し記述は50行程×30あるので全記述をすると60ｋを超えてエラーになります。
　ですので

i = 0
For a = 1 To 30
従業員番号 = UserForm1.ComboBox a.Text + 10 'ここでエラー .CombBox a 反転表示

With Sheet4
.Cells(15 + i, 3).Value = Sheet2.Cells(従業員番号, 1).Value
.Cells(17 + i, 3).Value = Sheet2.Cells(従業員番号, 2).Value
.Cells(16 + i, 6).Value = Sheet2.Cells(従業員番号, 3).Value
　　　　・
　　　　・
　　　　・
End with
i=i+4
Next a

としたら、「コンパイルエラー　メソッド・データメンバが見つかりません」が返ります。
ComboBoxの番号を指定しないとわからんわい、との事と思います。
(a) "a" _a としてもだめですし、cmd=CombBox+aを足してみたら cmd=1になるし、
cmd="CombBox"+a は「型が一致しない」エラー・・・等々
なんとかCombBoxの1～30をFor文でループしたいのです。ご教授をお願いします。

回答No.6

noname#223623 · ベストアンサー

補足を読んでかなりの初心者さんだと思いますので(自分もそう変わらないのですが)、もう少し詳しく説明します。

#4でも書きましたが、一度に複数のことをやろうとしているので原因がわかりにくいんだと思いますよ。
　従業員番号 = UserForm1.Controls("ComboBox" & a).Text + 10
は1行ですが、実は4つの処理をしていることに気づきましょう。4つの処理とは
　1. "ComboBox"という文字列とFor文のカウンタ変数"a"を連結する
　2. 1の文字列を名前とするコンボボックスのTextプロパティの値を取得する
　3. 2で取得した値に10を足す
　4. 3の結果を変数"従業員番号"に代入する

#3の補足を見ると値が確認できたということなので、1と2はOKなわけです。したがって、ここはこれ以上いじる必要がないです。"を付けたり外したりするのは1に影響することで、せっかくうまくいってる所をいじっても意味がないです。

で、次は3を確認しましょう。#2のFor文の中身を次のように書き換えます。デバッグ用の変数vを使うのでVariantで宣言しといてください。
　v = UserForm1.Controls("ComboBox" & a).Text + 10
　MsgBox v

これでエラーがでなければ(もちろん値が正しいかも確認できれば)、3はOKですね。OKなら4に進みます。もう一度For文の中を書き換えます。3までがOKだったら、ここでエラーが出るはずです。
　v = UserForm1.Controls("ComboBox" & a).Text + 10
　MsgBox v
　従業員番号 = v

以上を試して結果を教えてください。教えて欲しいことは
　* 1～4のどこでエラーが出たか。
　* エラーは常にこるのか、特定のコンボボックスで起きるのか
　* エラーが起きたときコンボボックス.Textの値はなんなのか
　* 計算結果(vの値)はどうなっているか
　* 従業員番号の型は？

他にもあるかしれないけどとりあえずそれだけ教えてください。

#4の補足ですが、プロシージャ全体としてどうすればいいのかということなら残念ながら回答できません。ちょっと情報不足です。プロシージャの組み立て方に関しては別に質問されたほうがいいでしょう。

お礼

masa pee さん　何度も有難う御座います。

下記で解決致しました。

Dim v As Integer 'Variant
Dim a As Integer
Dim 従業員番号 As Integer
Dim I As Integer

I = 0
For a = 1 To 30

If UserForm1.Controls("ComboBox" & a).Text = "" Then GoTo 100
v = UserForm1.Controls("ComboBox" & a).Text + 10
'MsgBox v
従業員番号 = v

With Sheet4
.Cells(15 + I, 3).Value = Sheet2.Cells(従業員番号, 1).Value 'ふりがな
.Cells(17 + I, 3).Value = Sheet2.Cells(従業員番号, 2).Value
・
・
・
End With

100:
I = I + 4
Next a

詳細は下記です。

(1)
Dim v As Variant　'1回目はOKだが、ループ途中でエラー
　　↓
Dim v As Integer

(2)
もしもComboBox1～30のうち未入力箇所があると、その回数目の時（Box5が未入力なら5回目のループの時）
v = UserForm1.Controls("ComboBox" & a).Text + 10　'ここでエラー
　　↓
If UserForm1.Controls("ComboBox" & a).Text = "" Then GoTo 100 '追記
・
・
・
100：　　　　　　　　　　　　　　　　　　　　　　　　　　　　　'追記

お世話を掛けました。有難う御座いました。

回答No.9

noname#22222

s_husky です。

確かに、

? "123" + 10
133

で、エラーが出ません。
しかし、「解決しました！」は、「うーん！」です。

? Val("123") + 10
133

? CInt("123") + 10
133

というのを標準作法にされることを勧めておきます。

お礼

s_husky さん　有難う御座います。

＞「解決しました！」は、「うーん！」です。

申し訳ありません。ごもっともですよね。
なにぶん、VBAを勉強し始めて日が浅いので皆さんにはご迷惑をかけます。

ですが、「解んないので教えて～」と言っても、やさしく教えてくれる「教えて！goo」を私は気に入っています。
「そんなのは自分で調べろ！！検索すればあるだろっ」ってサイトもありますよね。
「解決したら、全員に報告しろっ、」ってサイトもありました。

＞というのを標準作法にされることを勧めておきます。
有難う御座います。勉強したいと思いました。
疑問点があったらまた質問させて頂きたいので、その節には宜しくお願い致します。

回答No.8

Wendy02

こんにちは。Wendy02です。

全体が見えていませんので、良く分りませんが、変数の「従業員番号」は、一旦、どこかで置き換えたほうがよいです。

例えば、
　j = 従業員番号

With Sheet4以下は、General プロシージャにしました。

'-------------------------------------------
Private Sub CommandButton1_Click()
　 Dim a As Integer
　 Dim num As Variant
　 Dim i As Long
　 Dim j As Long
　 i = 5
　　j = 従業員番号
　 For a = 1 To 30
　　　num = UserForm1.Controls("ComboBox" & CStr(a)).Text
　　　If IsNumeric(num) Then
　　　　 If num > 0 And num < 31 Then
　　　　　　j = CLng(num) + 10
　　　　　　TransferData i, j
　　　　 End If
　　　End If
　 Next a
End Sub

Sub TransferData(ByVal i As Long, ByVal j As Long)
With Sheet4
　　　　　　.Cells(15 + i, 3).Value = Sheet2.Cells(j, 1).Value
　　　　　　.Cells(17 + i, 3).Value = Sheet2.Cells(j, 2).Value
　　　　　　.Cells(16 + i, 6).Value = Sheet2.Cells(j, 3).Value
　　　　　　'・
　　　　　　'・
End With
End Sub

お礼

補足の後、急遽用事が入り、時間が取れなくなりましたので、Wendy02　さんのご回答を最後まで確認出来なくなりました。（本日）
時間がないので、取りあえず、Wendy02　さんのコードをコピーしただけでは、

With Sheet4
.Cells(15 + i, 3).Value = Sheet2.Cells(従業員番号, 1).Value　'ここでエラー発生　多分私の記述位置ミスか、なにか？
.Cells(17 + i, 3).Value = Sheet2.Cells(従業員番号, 2).Value
.Cells(16 + i, 6).Value = Sheet2.Cells(従業員番号, 3).Value
　　　　・
　　　　・
　　　　・
End With

ですが、
＞Sub TransferData(ByVal i As Long, ByVal j As Long)
への記述など、参考になりそうです。　勉強致します。

中途半端な感じになってしまい申し訳ありませんが、今回はここで締め切らせてもらいます。
ほんとに今ちょっと急いでいるんです。

また疑問があったら質問させて頂きますので、その際にはまた、宜しくお願い致します。

補足

Wendy02　さん　いつも有難う御座います。

masa pee　さんへのお礼にも書かせてもらいましたが、無事に解決いたしました。
プログラム改修したり、皆さんへのお礼・補足をしたり、電話がかかって来たりで
今へとへとです。　で、これから少しの間だけ外出しないと・・。
改めて、Wendy02　さんのコードを確認させて頂いた上で、Wendy02　さんへのお礼と、
皆さんへのポイントをつけてから締め切りたいと思います。
解決したからといって、Wendy02　さんのご回答を見ないのは失礼と考えますので・・。

有難う御座いました。

回答No.7

noname#223623

#6の訂正と追加です。
[訂正]
誤: * エラーは常にこるのか、特定のコンボボックスで起きるのか
正: * エラーは常に起こるのか、特定のコンボボックスで起きるのか
[追加]
エラーが起きたとき、実際の値と期待している値は同じか。違うなら何が入って欲しいのか教えてください。
[確認]
#5の補足で「作業員名簿」は「従業員番号」の間違い？これまで作業員名簿の話は出てなかったので質問とどう関連するのかわからない。

お礼

masa pee さんのNo.6のお礼にも書かせてもらいましたが無事解決致しました。
（　プログラム改修に没頭している内に、さらにご回答がNo.8のWendy02　さんまで増えていてびっくり！）

補足してから自分でも言葉が足らなかったと気が付きましたが
「作業員名簿」はSheet4のシート名というか、貼付先の作成する表？の名前です。
貼り付ける際に貼り付ける欄(行)を変数iで指定しているので・・。
「作業員番号」はVBA上での記述の際の変数名。
貼り付ける、という表現も違うかな・・。う～ん、国語力不足？

有難う御座いました。

回答No.5

noname#22222

? CLng("111" & "10")
11110
? CLng("111") + 10
121

他には、

? Val("111" & "10")
11110
? val("111") + 10
121

など使えます。

※左辺と右辺の型は合わせる必要があります。

お礼

再度のご回答有難うございます。
＃4さんに補足しましたが、ComboBox1～30　を順に指定していく箇所でつまづきました。

ConboBox　の1、2、3、～30　を a で代入するには左辺=右辺の記述のどこに気を付ければよいのでしょう？

作業員名簿＝？？？？ComboBox？？？？

あるいは作業員名簿をアルファベットにする・・？
個人的には　作業員名簿　に問題でなくて、ComboBox　と　a をどうつなぐか、と思っていますが・・。

回答No.4

noname#223623

> メッセージボックス( OkOnly )が３回表示されておしまいでした・・。
はいその通りです。質問としてはFor文の中でコンボボックスを指定したいということだったのでそう書きました。いきなりいろいろ書いてしまうとエラーの原因がわかりにくくなるので単純にしました。テストは単純なことから始めないとはまります。

#1の補足についてですが、コード全体を見ていないので推測で答えます。今回の原因になりそうなのは
　1. 文字と数字を足し算しようとしている
　2. 代入しようとしている値と変数の型が合わない
のどちらかでしょう。
エラーが起きたとき、コンボボックスのTextには何が入っているのか。足し算は正確に行われているか。代入のときの型があっているか、を確認してください。やり方がわからなければ補足ください。

補足

'1人目データの貼り付け
If ComboBox1.Text = "" Then GoTo 2
If ComboBox1.Text < 1 Or ComboBox1.Text > 30 Then
GoTo case1　'行き先は「1～30の範囲の整数を入力下さい。」旨のMsgBoxです。
End If

従業員番号 = UserForm1.ComboBox1.Text + 10
i = 0

With Sheet4
.Cells(15 + i, 3).Value = Sheet2.Cells(従業員番号, 1).Value
　　　　　・
　　　　　・
　　　　　・
End With

2: '2人目データの貼り付け
If ComboBox2.Text = "" Then GoTo 3
If ComboBox2.Text < 1 Or ComboBox2.Text > 30 Then
GoTo case1
End If

従業員番号 = ComboBox2.Text + 10
i = 4 'i=4←sheet4(作業員名簿)1人目の欄から4行下が2人目の欄
With Sheet4
.Cells(15 + i, 3).Value = Sheet2.Cells(従業員番号, 1).Value
　　　　　・
　　　　　・
　　　　　・
End With

3: '3人目データの貼り付け
　　　　　・
　　　　　・
　　　以下10：まで続く

と記述していて問題なく使えました。

ただ、人数を30人にする必要になったのですが、このまま30：まで記述すると64ｋを超えてしまいます。
で、For to Next 文を使ったところ、ComboBox　の指定でつまづいた次第です。

回答No.3

noname#22222

Private Sub CommandButton1_Click()
　　Dim I As Integer

　　For I = 1 To 3
　　　　MsgBox UserForm1.Controls("ComboBox" & Trim(Str$(I))).Text & "10"
　　Next I
End Sub

一応、これで ComboBox1、ComboBox2、ComboBox3　のテキストは表示されます。

お礼

ご回答有難うございます。
＞For I = 1 To 3
　　＞MsgBox UserForm1.Controls("ComboBox" & Trim(Str$(I))).Text
＞Next I

ご回答のとおり、ComboBox1～3 のテキスト表示は確認出来ました。
ですが、MsgBox の記述では"CombBox" でエラーになりませんが、
質問で述べたVBAの従業員番号= の箇所に代入すると「型が一致しません」エラーになります。

従業員番号 = UserForm1.Controls("ComboBox" & Trim(Str$(a))).Text + 10

"ComboBox" が文字列型、I　が整数型と認識されるからか・・
"" を外すと「指定されたオブジェクトは見つかりません」エラーが返ります。
型宣言しても、しなくとも一緒です。

For To Next 文でコントロールの指定は無理ですか？

回答No.2

noname#223623

'ComboBox1～ComboBox3のTextプロパティを表示
For a = 1 To 3
MsgBox UserForm1.Controls("ComboBox" & a).Text
Next

これで値が確認できると思います。こちらはExcel2000で確認。バージョン違いで動かなかったらごめんなさい。

お礼

ご回答有難うございます。
＞For a = 1 To 3
＞MsgBox UserForm1.Controls("ComboBox" & a).Text
＞Next

メッセージボックス( OkOnly )が３回表示されておしまいでした・・。

質問で言い忘れてましてすみません。XP　Excel2003 です。

回答No.1

chie65536

従業員番号=UserForm1.Controls("ComboBox" & a).Text+10
ではどうか？（未確認）

お礼

ご回答有難うございます。
＞従業員番号=UserForm1.Controls("ComboBox" & a).Text+10
「型が一致しません」エラーが返ります。
="○○○○" + a　や　="○○○" & a　　も同エラーです。
文字列型や整数型を一緒に記述しない事、の意味と考えています。
（　試しに型宣言しても、型宣言しなくとも同エラーになります。）

=UserForm1.Controls(ComboBox & a).Text+10　と""を外すと今度は
「指定されたオブジェクトは見つかりません」エラーが返ります。