【Excel VBA】ループについて

質問

Excel2003を使用しています。

2つのBook間でのループはできるのでしょうか？
下記のようにBook1のデータをBook2へ値のみコピーしたいのですが。。。

　Book1のSheet1　　　　　Book2のSheet1
　　T7：T23　　　　　　　　　　L19
　　U7：U23　　　　　　　　　　L59
　　V7：V23　　　　　　　　　　L99
　　　：　　　　　　　　　　　　　　：
　　　：　　　　　　　　　　　　　　：
　　AM7：AM23　　　　　　　　L779

マクロ勉強中ですので、まず、マクロの記録をとってみたのですが、同じ処理の繰り返しなので、ループかな？と思い、参考書や似たような処理のコードを見たりしながら、あれこれ考えていたら、頭が混乱してきてしまいました(>_<)
2つのBook間では難しい(面倒)ならば、同じBookに変更することも考えています。

よろしくお願いします。

回答No.7

Wendy02 · ベストアンサー

こんにちは。Wendy02です。

証明にはなりませんが、#2 で書いた元は、最初は値コピーで作っていたのですが、戻してしまいました。（^^;　
一応、こちらも書いておきますので、参考になるのでしたら、見てください。PasteSpecial でも可能ではあるのですが、Value = Value 方式（中身は配列）は、Copy と比較すると、右と左が入れ替わるのですね。

Sub CopyTest2()
Dim BaseRng As Range
Dim Bk1 As Workbook
Dim Bk2 As Workbook
Dim Ccnt As Integer
Dim Rcnt As Integer
Dim i As Integer

Set Bk1 = Workbooks("Book1.xls")
Set Bk2 = Workbooks("Book2.xls")

With Bk1.Worksheets("Sheet1")
Set BaseRng = .Range("T7:T23")

Ccnt = .Range("T7", .Range("IV7").End(xlToLeft)).Columns.Count
Rcnt = BaseRng.Rows.Count
End With

For i = 0 To Ccnt
　Bk2.Worksheets("Sheet1").Range("L19").Offset(i * 40).Resize(Rcnt).Value = _
　　BaseRng.Offset(, i).Resize(Rcnt).Value

Next i
Set BaseRng = Nothing
Set Bk1 = Nothing
Set Bk2 = Nothing

End Sub

お礼

Wendy02 さん、こんにちは。
再度の回答ありがとうございます。

>PasteSpecial でも可能ではあるのですが、Value = Value 方式（中身は配列）は、Copy と比較すると、右と左が入れ替わるのですね。

Value = Value 方式は、Copy と比較すると、右と左が入れ替わるというところに気づいていませんでした(^^ゞ
No.4とNo.5の回答でもそうなっていますよね…。

回答No.11

ka_na_de

rx-z5815さん、Wendy02さん、おはようございます。

ka_na_de です。

大変丁寧な説明ありがとうございました。

＞たぶん、Range と Cells は、お互いに似たもの兄弟で、ほぼ対等な関係で、

なるほど、とても良く理解できました。
RangeとRangeの中のCellsは同じ親に決まっているという思い込みが
ありました。
また、一つ勉強になりました。

やはり、エラーや疑問は放置してはいけませんね。
結果往来では何の進歩も無いということを痛感しました。

ところで、
＞私のオリジナリティは何一つ生きてはいません。決まったパターンのひとつです。
私にはそうは思えません。
いつも拝見していますが、すっきり、明解、理路整然で無理がありません。
私も少しでも近づけるようにこれからも勉強していきたいと思います。

ありがとうございました。

回答No.10

Wendy02

こんばんは。　rx-z5815　さん、ka_na_de　さん。

Wendy02です。

>完璧なまでに汎用的に作られたコードですね。

私の書いたものは、一般のVBA掲示板で流通している書き方で、私のオリジナリティは何一つ生きてはいません。決まったパターンのひとつです。私は、最初に、こういう書き方のパターンを見たときに、なぜ、そう書くのか理解できませんでした。その時、疑問に思って考えたのは良く覚えています。

自己解決してしまったようですが、そういう私も経験あります。最初は、なぜかさっぱり分からなかったです。

Workbooks("Book1.xls").Worksheets("Sheet1").Range(Cells(7, c), Cells(23, c)).Value

Range オブジェクトには、親のSheetがあり、そのまた親のWorkbook が、ひとつながり続いていますね。（Applicationの一番上の親は隠れています）ところが、Cells は、どうですか？どこにも繋がっていません。Range は、Cells の面倒を見てくれないのです。たぶん、Range と Cells は、お互いに似たもの兄弟で、ほぼ対等な関係で、Range が、引数としてサポートする関係にはないのでしょうね。

Cells の親は、標準モジュールの場合、その開いているシート(ActiveSheet)　になるので、別の場所だと、Cells の親とRange の親の整合性が合わないので、エラーが出ます。もちろん、両方の親を Select で一致させてあげてもよいのです。なお、シートモジュールの時は、そのシートが親になります。こちらは融通が利きません。

というわけで、こういうパターンの時には、こんな書き方になります。これも典型的ですね。ですが、長い等号式の時は、ちょっと書きづらいかもしれません。

With Workbooks("Book1.xls").Worksheets("Sheet1")
　　.Range(.Cells(7, c), .Cells(23, c)).Value
End With

回答No.9

ka_na_de

＃４のka_na_deです。

すみません。
＃８に対する自己レスです。

質問の件ですが、解決致しました。

Range(Cells(),Cells())という表現を利用する場合、
Rangeの前に
Workbooks("Book1.xls").Worksheets("Sheet1").
という指定をしましたが、
Rangeの中の２つのCells()の前にも
Workbooks("Book1.xls").Worksheets("Sheet1").
の指定がいるのですね～

実際記述する場合は、わずらわしくなるので、
別の変数を宣言してSetするか
With～End Withで囲んで、見やすくしないといけないですね。

重ね重ねお詫びします。

回答No.8

ka_na_de

＃４のka_na_deです。

Wendy02さんに質問させてください。

（rx-z5815さん、大変失礼なのですが、
　横道にそれることをお許し下さい）

Wendy02さん、コード拝見しました。
完璧なまでに汎用的に作られたコードですね。
改めて、感激しました。

一つ質問させてください。

私は、＃４の回答をするとき、
まず、以下のコードをBook2の標準モジュールに登録して試しました。
しかし、エラーとなるため、しかたなく、
Workbooks("Book1.xls").Worksheets("Sheet1").Activate
の一文を追加して回避しました。
Wendy02さんのコードでは、どこにも別のシートをアクティブに
する記述がありませんが、何故、問題なく値の代入ができるの
でしょうか？

ぜひ、ご教示ください。
よろしくお願い致します。

Sub TEST2()
　Dim c As Long

　For c = 20 To 39 'T列～AM列
　　Workbooks("Book2.xls").Worksheets("Sheet1").Range("L" & c * 40 - 781).Resize(17, 1).Value = _
Workbooks("Book1.xls").Worksheets("Sheet1").Range(Cells(7, c), Cells(23, c)).Value
　Next c

End Sub

回答No.6

onlyrom

onlyromです。

＞別のセルというのは、Book1のセルがT列～AM列がAX列～BQ列(行は同じで7～23行)、Book2のセルがD19～D779です。

Ｔ　　－－＞　ＡＸ
ＡＭ　－－＞　ＢＱ
Ｌ　　－－＞　Ｄ
に変更したＦＯＲブロックを追加するだけですね。

'---------------------------------------------------------
　For C = Range("AX7").Column To Range("BQ7").Column
　　　R = R + 1
　　　myRow = (R - 1) * 40 + 19
　　　mySheet.Cells(myRow, "D").Resize(17, 1).Value = _
　　　　　　　　　　　　Range(Cells(7, C), Cells(23, C)).Value
　Next C
'---------------------------------------------------------

それから、アドバイス。
余計な一言でした、すみません。(^_^;;;

お礼

再度の回答ありがとうございます。

Forブロックを追加すれば良いというのは合っていましたが、また別に変数を取らなくてはいけないのかと思っていました。
同じ変数(onlyrom さんのコードで言う“C”ですが)でするのですね(^_^;)

これで、前回と今回の質問でいただいた回答を元に、目的が達成できそうで、嬉しい限りです♪ありがとうございました！

回答No.5

onlyrom

kanadeさんと、同じですが。。。(^^;;;

列番号は可能なかぎりアルファベットを使う、
変数も適宜使用する、
ようにするとコードが読みやすくなります。

'-------------------------------------------------
Sub MoveTest()

　Dim R As Long
　Dim C As Integer
　Dim myRow As Long
　Dim mySheet As Worksheet

　Workbooks("AAA.xls").Sheets("Sheet1").Activate

　Set mySheet = Workbooks("BBB.xls").Sheets("Sheet1")

　For C = Range("T7").Column To Range("AM7").Column
　　　R = R + 1
　　　myRow = (R - 1) * 40 + 19
　　　mySheet.Cells(myRow, "L").Resize(17, 1).Value = _
　　　　　　　　　　　　Range(Cells(7, C), Cells(23, C)).Value
　Next C

End Sub
'------------------------------------------------------

【アドバイス】
解決した質問はちゃんと閉じるように心掛けることをお勧めします。(^_^;;;

お礼

回答ありがとうございます。

>列番号は可能なかぎりアルファベットを使う、
変数も適宜使用する、
ようにするとコードが読みやすくなります。

確かに、後々コードを見たときに、わかりやすいですよね。
まだまだ初心者で、以前教えていただいたコードを参考にしたり、見直したりすることが多いので、onlyrom さんがおっしゃっていることを心がけています。

書き込みしてくださったついで…というわけではないのですが、No.3の回答のお礼欄に、再質問しています。
もし、お時間がありましたら、見ていただけると嬉しいです。

あ、あと最後の【アドバイス】ですが、前回の質問と今回の質問は関連があって、というか、同じデータの処理なんです。今回の質問が解決したときに、一緒に締め切るつもりでいました。
基本的に質問が解決しても即締め切らず、質問投稿日から1週間ぐらいはそのままにしています。
マナーは守っているつもりですので、ご安心(？)ください(^^ゞ

回答No.4

ka_na_de

＃４のka_na_deです。

Book1は一回一回アクティブにしなくてもできますね。
例２です。

Sub TEST2()
　Dim c As Long

　Application.ScreenUpdating = False

　Workbooks("Book1.xls").Worksheets("Sheet1").Activate
　For c = 20 To 39 　　'T列～AM列
　　Workbooks("Book2.xls").Worksheets("Sheet1").Range("L" & c * 40 - 781).Resize(17, 1).Value = Range(Cells(7, c), Cells(23, c)).Value
　Next c

　Application.ScreenUpdating = True

End Sub

お礼

再度の回答ありがとうございます。
こちらのコードでもうまくいきました。

回答No.3

ka_na_de

一例です。

Sub TEST()
　Dim c As Long

　Application.ScreenUpdating = False

　For c = 20 To 39 'T列～AM列
　　　Workbooks("Book1.xls").Worksheets("Sheet1").Activate
　　　Range(Cells(7, c), Cells(23, c)).Copy
　　　Workbooks("Book2.xls").Worksheets("Sheet1").Activate
　　　Range("L" & c * 40 - 781).PasteSpecial　Paste:=xlPasteValues
　Next c

　Application.ScreenUpdating = True

End Sub

お礼

回答ありがとうございます。

早速、教えていただいたコードでマクロを実行してみたところ、うまくいきました。
No.1の回答者様が書いていたように、二重ループになるのかと思っていたのですが、予想していたより、かなりシンプルなものでした。

それで、質問文に書いた内容と同じことを、別のセルでもしたかったので、教えていただいたコードに続けて書いたのですが、エラーメッセージは出ないものの、付け加えた部分が実行されませんでした。
別のセルというのは、Book1のセルがT列～AM列がAX列～BQ列(行は同じで7～23行)、Book2のセルがD19～D779です。

ただ単に、付け加えるだけではダメで書き方があるのでしょうか？
もしよろしければ、教えていただけると助かります。

回答No.2

Wendy02

こんばんは。

ちょっと、他のことをしながら作ったので、うまくできているかわかりません。（^^;
私の頭は混乱はしていないとは思いますが……。間違っていたらすみません。落ち着いて作ればよいのですが。
ActiveWorkbook は、実際のブック名を入れたほうがよいかもしれません。
Bk2 側は、もちろん、実態のある名前にしてくださいね。

Sub CopyTest1()
Dim BaseRng As Range
Dim Bk1 As Workbook
Dim Bk2 As Workbook
Dim Ccnt As Integer
Dim i As Integer

Set Bk1 = ActiveWorkbook
Set Bk2 = Workbooks("Book2.xls")

With Bk1.Worksheets("Sheet1")
Set BaseRng = .Range("T7:T23")

Ccnt = .Range("T7", .Range("IV7").End(xlToLeft)).Columns.Count
End With

For i = 0 To Ccnt

　BaseRng.Offset(, i).Copy _
　Bk2.Worksheets("Sheet1").Range("L19").Offset(i * 40)

Next i
Set BaseRng = Nothing
Set Bk1 = Nothing
Set Bk2 = Nothing

End Sub

お礼

回答ありがとうございます。

他のことをしながらでも作れちゃうなんてすごいですね！
早速、試してみたところ、うまくいきました。

Book2は、すでに作成してある書式で、Book1からは“値のみ”コピーしたかったので、値のみ貼り付けのコードを付け加えればいいかと思い、やってみたのですが、エラーが出てしまいました…(^_^;)
せっかくなので、“値のみ”コピーする場合は、どこを変えたらいいか教えていただけないでしょうか。

お手数かけてすみませんが、よろしくお願いします。

回答No.1

zap35

＞2つのBook間でのループはできるのでしょうか？
Book1のデータをBook2へ値をコピーするなら、Book1のT列～AM列のループと各列７行目～２３行目までの二重ループになると思うのですが…
「2つのBook間でのループ」の意味が？？です。

でもそれ以前に質問の意味するところが理解できません
T列～AM列で２０列あります。各列１７行ありますから３４０個のセルデータがあることになります。

これをBook2のL列に４０行おきにどのように貼り付けるのですか？
L19からL779まで４０行おきに貼り付けるとしたら２０個のデータしか貼り付けられませんね

お礼

アドバイスありがとうございます。

Book2は、1ページが40行の書式で、そのL列に40行おきに値のみを貼り付けていきたいのです。
T列～AM列の20列(各列17行)は、1列が1ページ分のデータで、L19からL779まで40行おきに貼り付けるセルの位置を書いていたのですが、わかりづらかったようで…。

また機会がありましたら、よろしくお願いします。