Excel（エクセル）活用コミュのVBA　くり返し処理で纏めれるでしょうか？

お世話になります。
あるデータベースに表がありユーザーフォームへ抜き出すコードを記述しています。

そこで規則性のありそうな場所を見つけたのでくり返し処理でできないかなーと考えたのですがいまいちすっきりする案が浮かばず、質問させてください。

くり返し処理を追記したいのは以下のコードです。

----------------------------------------------------
Dim myRoom As Integer, intRow As Integer
myRoom = TextBox1 '部屋番号検索
Columns("A:A").Find(What:=myRoom, LookIn:=xlFormulas, LookAt:=xlWhole).Activate
intRow = ActiveCell.Row

UserForm1.TextBox22.Value = Cells(intRow, 15) & " " & Cells(intRow, 16).Value
UserForm1.TextBox23.Value = Cells(intRow, 20) & Cells(intRow, 21) & Cells(intRow, 22).Value
UserForm1.TextBox24.Value = Cells(intRow, 23).Value
UserForm1.TextBox25.Value = Cells(intRow, 24).Value
UserForm1.TextBox26.Value = Cells(intRow, 17).Value

UserForm1.TextBox27.Value = Cells(intRow, 25) & " " & Cells(intRow, 26).Value
UserForm1.TextBox28.Value = Cells(intRow, 30) & Cells(intRow, 31) & Cells(intRow, 32).Value
UserForm1.TextBox29.Value = Cells(intRow, 33).Value
UserForm1.TextBox30.Value = Cells(intRow, 34).Value
UserForm1.TextBox31.Value = Cells(intRow, 27).Value

UserForm1.TextBox32.Value = Cells(intRow, 35) & " " & Cells(intRow, 36).Value
UserForm1.TextBox33.Value = Cells(intRow, 40) & Cells(intRow, 41) & Cells(intRow, 42).Value
UserForm1.TextBox34.Value = Cells(intRow, 43).Value
UserForm1.TextBox35.Value = Cells(intRow, 44).Value
UserForm1.TextBox36.Value = Cells(intRow, 37).Value

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

TextBox22～26、27～31、32～36がそれぞれ一つのグループで構成され、シートの同類項目を抜き出しています。
TextBox22～26の4行の処理をを繰り返す形でできればベストだと思っています。
「Controls("TextBox" & i).value=。。。」でオブジェクト変数で考えてみたのですがiを1、5、10と増やすには
For i = 1 to 10 Step 5でよかったでしょうか？

他に方法があればご教授ください。

Win7 2010　です。

コメント(14)

最初
全て
最新の40件

[1] mixiユーザー 04月07日 15:22

すいません。ちょっと補足です。
>0のコードはUserForm2に配置したCommandButton1の動作です。別にUserForm1も開いています。
TextBox1はUserForm2に、TextBox22～36はUserForm1に配置されています。

[2] mixiユーザー 04月07日 16:09

>For i = 1 to 10 Step 5でよかったでしょうか？
やってみれば分かるのでは？？
単純なVBAを書いて試してみれば
良いように思いますがやっていますか？

私ならTextBox22～36までをループ処理させてしまうと思いますね
グループの最初の2行と残り3行を別に考えるほうが簡単だと思うので
ぱっと思いつくのはMod演算子を使い、If関数で分岐して処理させる
という形ですね

頭の中でやっているので動くか分かりませんが
だいたい10行くらいで出来るように思います

http://mixi.jp/view_bbs.pl?id=73359986&comm_id=2106
の進み具合はどうですか？

[3] mixiユーザー 04月07日 17:18

Danielさん
こんにちは。

WithとやはりControlsオブジェクトを使用した方が良さそうですね。
Formのテキストボックスは5単位、セルは10単位なのでちょっとはスッキリすると思います。

以下、例です。

　Dim I　　　As Integer
　For I = 0 To 10 Step 5
　　With UserForm1
　　　.Controls("TextBox" & 22 + I).Value = Cells(intRow, 15 + I * 2) & _
　　　　　　　" " & Cells(intRow, 16 + I * 2).Value
　　　.Controls("TextBox" & 23 + I).Value = Cells(intRow, 20 + I * 2) & _
　　　　　　　Cells(intRow, 21 + I * 2) & Cells(intRow, 22 + I * 20).Value
　　　.Controls("TextBox" & 24 + I).Value = Cells(intRow, 23 + I * 2).Value
　　　.Controls("TextBox" & 25 + I).Value = Cells(intRow, 24 + I * 2).Value
　　　.Controls("TextBox" & 26 + I).Value = Cells(intRow, 17 + I * 2).Value
　　End With
　Next I

[4] mixiユーザー 04月07日 17:20

>>2 ありがとうございます。
Forのコードについては間違っていることが判明しました。
因みにテストした記述です。

For i = 1 To 10 Step 5
Range("AF" & Format(i)).Value = "TEST"
Next

これだとAF1、AF6のみにTESTと入力されおそらくiの変動値は1、6だけになってますね。
しかし1 to 10 Step 4にした場合iの変動値は1、5、9となりこれも私の望むものとは違います。
1,5,10とステップを刻むにはどうすればいいか不明です。

>私ならTextBox22～36までをループ処理させてしまうと思いますね
グループの最初の2行と残り3行を別に考えるほうが簡単だと思うので
ぱっと思いつくのはMod演算子を使い、If関数で分岐して処理させる
という形ですね

このコメントを見る限りではマリ男さんはすでに解決の糸口を見つけておられる様子なので、教えていただけるなら記述を書いていただきたいものです。私にはその方法が皆目検討もつきませんので。

最後の別トピの進行具合はこのトピには関係ないことなので割愛させて頂きます。

[6] mixiユーザー 04月07日 17:30

>>[4]
>最後の別トピの進行具合はこのトピには関係ないことなので割愛させて頂きます。

ついでに聞いたまでなので、まあ良いですが
あれからの進捗状況が分からず、また別の質問をされているので
どうなったのかなと気になったもので

[7] mixiユーザー 04月07日 17:38

>>[4]
>私にはその方法が皆目検討もつきませんので。

いや、書いたそのままですよ
For-Nextでループし、剰余が1のとき、1行目の処理
剰余が2のとき、2行目の処理、それ以外のとき、1,2行目以外の処理をさせる

列番号を上手く求めるようにすれば、処理の部分はそれぞれ1行で書けると思います

因みにとりあえず書いてみたところ、私はForからNextまでで9行になりました

[8] mixiユーザー 04月07日 18:04

>>[3]
ありがとうございます。
なるほど。Forの回数(？)を0～にしてやれば良かったんですね。思いつきませんでした(笑)
しかもセルの変数までIを使ってっていうのはすごく為になる考え方ですね。

ちょっとコードを弄るのに時間がかかりますが試してみます！
因みに参考程度に質問なんですが、WITHを使わない場合はそれぞれのオブジェクトに「UserForm1.」ってのを追記してやればいいですかね？

[9] mixiユーザー 04月07日 18:12

>>[7]
ありがとうございます。
教えて頂いた文章をコードになおす知識が乏しいのでよくわからないです。

>列番号をうまく求めると。。。
どううまく求めたらいいか分からないのでこのトピを作りました。お察しください。

[10] mixiユーザー 04月07日 18:24

>>[009]
>どううまく求めたらいいか分からないので
変数の値を利用するなどして、どうにか目的とする値にする

今までの質問で同じような考え方のものがあったように思いますが

既に解決できるコードが出ているようですが、今出先なので
後でになりますが私のコードも載せておきます

[11] mixiユーザー 04月07日 19:05

私は出来るだけ同じような処理はまとめたいので
多少分かり辛く、面倒な書き方ではありますが一応・・・

For i = 1 To 15
　　If i Mod 5 = 1 Then
　　　 UserForm1.Controls("TextBox" & 21 + i).Value = Cells(intRow, 15 + Int((i - 1) / 5) * 10) & " " & Cells(intRow, 16 + Int((i - 1) / 5) * 10)
　　ElseIf i Mod 5 = 2 Then
　　　 UserForm1.Controls("TextBox" & 21 + i).Value = Cells(intRow, 20 + Int((i - 1) / 5) * 10) & Cells(intRow, 21 + Int((i - 1) / 5) * 10) & Cells(intRow, 22 + Int((i - 1) / 5) * 10)
　　Else
　　　 UserForm1.Controls("TextBox" & 21 + i).Value = Cells(intRow, 20 + (i Mod 5) + Int((i - 1) / 5) * 10 + (((i Mod 5) = 0) * 3))
　　End If
Next i

まあ、もう少し短くすることも可能ではあると思います
例えば

For i = 1 To 15
　　j = 3 + (i Mod 5 = 1) * 2 + (i Mod 5 = 2) * 1
　　UserForm1.Controls("TextBox" & 21 + i).Value = Choose(j, Cells(intRow, 15 + Int((i - 1) / 5) * 10) & " " & Cells(intRow, 16 + Int((i - 1) / 5) * 10), _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 Cells(intRow, 20 + Int((i - 1) / 5) * 10) & Cells(intRow, 21 + Int((i - 1) / 5) * 10) & Cells(intRow, 22 + Int((i - 1) / 5) * 10), _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 Cells(intRow, 20 + (i Mod 5) + Int((i - 1) / 5) * 10 + (((i Mod 5) = 0) * 3)))
Next i

こんな感じでも良いのですが、読み解くのは慣れないと面倒かもしれません

[12] mixiユーザー 04月07日 19:27

>>[8]

Withはなるべく使うようにした方が良いかと思いますが…
もし使わないのなら、以下のようになりますね。

　Dim I　　　As Integer
　For I = 0 To 10 Step 5
　　UserForm1.Controls("TextBox" & 22 + I).Value = Cells(intRow, 15 + I * 2) & _
　　　 " " & Cells(intRow, 16 + I * 2).Value
　　UserForm1.Controls("TextBox" & 23 + I).Value = Cells(intRow, 20 + I * 2) & _
　　　 Cells(intRow, 21 + I * 2) & Cells(intRow, 22 + I * 20).Value
　　UserForm1.Controls("TextBox" & 24 + I).Value = Cells(intRow, 23 + I * 2).Value
　　UserForm1.Controls("TextBox" & 25 + I).Value = Cells(intRow, 24 + I * 2).Value
　　UserForm1.Controls("TextBox" & 26 + I).Value = Cells(intRow, 17 + I * 2).Value
　Next I

プログラムのコーディングは以下を意識して作成すべきだと思っています。
　・ステップ数を少なく
　・プログラムを見やすく
　・メンテナンス（修正）をしやすく
　・処理速度を早く
　等々だと思います。

　・ステップ数が短くても、プログラムがわかりにくくなったり、メンテナンス性が
　　悪くなったるするのも良くないので【バランス】だと思います。
　・Withはメンテナンス性が(UserForm1→UserForm1に変更する場合等は楽）良いのと
　　処理スピードが早いと言われていますが、ほんのわずかなので気にすることでは
　　ないですが…

以上を意識して、プログラムを作成して下さい。

[13] mixiユーザー 04月07日 19:31

>>[8]
一点、入力ミスしました。

　・Withはメンテナンス性が(UserForm1→UserForm1に変更する場合等は楽）良いのと
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　↓
　・Withはメンテナンス性が(UserForm1→UserForm2に変更する場合等は楽）良いのと

[14] mixiユーザー 04月07日 21:21

>>[11]
ありがとうございます。
確かに読み解くにはそれなりに慣れがいるかもしれませんね。
でも、参考にさせていただきます。

>>[12] たいらさん
ありがとうございます。そうですね、確かにメンテナンス性を考えるとWithを使う方がシンプルに収まりそうです。
わざわざありがとうございました。

ログインすると、みんなのコメントがもっと見れるよ