[[20120513133527]]　『セル参照式のまとめ方』（えま）　ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

　

『セル参照式のまとめ方』（えま）

いつもお世話になっています。
アクティブシートに"data_sheet"のセルを参照したいです。
数が多いのでマクロを使いたいと思い、途中まで作りましたが、
ものすごく長いのでまとめたいです。
それに加え、下に書いた式は1ブロック分です。
この後左辺は8行下から始まり列はそのまま、右辺は1行下から始まり列はそのまま、
これを200回繰り返したいです。

Sub セルの参照()

    Dim DS As Worksheet
    Set DS = ThisWorkbook.Worksheets("data_sheet")
    Dim r As Long

    With DS
        For r = 3 To 10
            Cells(r, 2) = .Cells(5, 2)
            Cells(r, 6) = .Cells(5, 7)
            Cells(r, 7) = .Cells(5, 8)
        Next

            Cells(3, 13) = .Cells(5, 17)
            Cells(4, 13) = .Cells(5, 25)
            Cells(5, 13) = .Cells(5, 33)
            Cells(6, 13) = .Cells(5, 41)
            Cells(7, 13) = .Cells(5, 49)
            Cells(8, 13) = .Cells(5, 57)
            Cells(9, 13) = .Cells(5, 65)
            Cells(10, 13) = .Cells(5, 73)

            Cells(3, 14) = .Cells(5, 18)
            Cells(4, 14) = .Cells(5, 26)
            Cells(5, 14) = .Cells(5, 34)
            Cells(6, 14) = .Cells(5, 42)
            Cells(7, 14) = .Cells(5, 50)
            Cells(8, 14) = .Cells(5, 58)
            Cells(9, 14) = .Cells(5, 66)
            Cells(10, 14) = .Cells(5, 74)

            Cells(3, 15) = .Cells(5, 19)
            Cells(4, 15) = .Cells(5, 27)
            Cells(5, 15) = .Cells(5, 35)
            Cells(6, 15) = .Cells(5, 43)
            Cells(7, 15) = .Cells(5, 51)
            Cells(8, 15) = .Cells(5, 59)
            Cells(9, 15) = .Cells(5, 67)
            Cells(10, 15) = .Cells(5, 75)

            Cells(3, 17) = .Cells(5, 81)
            Cells(4, 17) = .Cells(5, 89)
            Cells(5, 17) = .Cells(5, 97)
            Cells(6, 17) = .Cells(5, 105)
            Cells(7, 17) = .Cells(5, 113)
            Cells(8, 17) = .Cells(5, 121)
            Cells(9, 17) = .Cells(5, 129)
            Cells(10, 17) = .Cells(5, 137)

            Cells(3, 18) = .Cells(5, 82)
            Cells(4, 18) = .Cells(5, 90)
            Cells(5, 18) = .Cells(5, 98)
            Cells(6, 18) = .Cells(5, 106)
            Cells(7, 18) = .Cells(5, 114)
            Cells(8, 18) = .Cells(5, 122)
            Cells(9, 18) = .Cells(5, 130)
            Cells(10, 18) = .Cells(5, 138)

            Cells(3, 19) = .Cells(5, 83)
            Cells(4, 19) = .Cells(5, 91)
            Cells(5, 19) = .Cells(5, 99)
            Cells(6, 19) = .Cells(5, 107)
            Cells(7, 19) = .Cells(5, 115)
            Cells(8, 19) = .Cells(5, 123)
            Cells(9, 19) = .Cells(5, 131)
            Cells(10, 19) = .Cells(5, 139)

これが1ブロックで、次は左辺は8行下、
For r = 11 To 18になります。
左辺のCellsの（）内の行が11〜18、列は同じです。
右辺の.Cellsの（）内の行が1行下の6、列は同じです。

その次は左辺が16行下、
For r = 19 To 26
左辺の（）内の行が19〜26、列は同じです。
右辺の.Cellsの（）内の行が2行下の7、列は同じです。

この1ブロックずつの左辺が8行下、右辺が1行下、という式を
200回繰り返したいです｡

上の式にはパターンがあるので、何か代入できないかと考えました。
左辺のCellsの（）内は、行にrを入れ、列は13。
右辺の.Cellsの（）内は行は5、列は17列目から8列置きに8回参照します。
下の式にしてみました。

 Sub セルの参照2()
    Dim DS As Worksheet
    Set DS = ThisWorkbook.Worksheets("data_sheet")
    Dim r As Long
    Dim Col As Long

    With DS
        For r = 3 To 10
            Cells(r, 2) = .Cells(5, 2)
            Cells(r, 6) = .Cells(5, 7)
            Cells(r, 7) = .Cells(5, 8)

        For Col = 17 To 17 * (8 - 1) + 1 Step 8
            Cells(r, 13) = .Cells(5, Col)
        Next
        Next
    End With
End Sub

Cells(r,13)には同じ数字が入りました。
どこを参照しているのかと("data_sheet")でCtrl+Fで探しましたが、
文字列の検索のためか見つかりませんでした。
オートフィルタをかけてありそうな場所を探しましたが、やはり見つかりませんでした。
参照先の範囲が"A1:EK147"と大きいため見落としたかもしれません。

参照式のまとめ方をご教授いただけるとうれしいです。
どう祖、よろしくお願いします。
Excell2007
Windows Vista

---

 試して無いけど、こんなのでは？
 まちがったらごめん

 Sub セルの参照()

    Dim DS As Worksheet
    Dim r As Long
    Dim i As Long
    Dim j As Long

    Set DS = ThisWorkbook.Worksheets("data_sheet")

    With DS
        For r = 3 To 10
            Cells(r, 2).Value = .Cells(5, 2).Value
            Cells(r, 6).Value = .Cells(5, 7).Value
            Cells(r, 7).Value = .Cells(5, 8).Value
        Next r
        For i = 0 To 199
            For j = 0 To 7
                Cells((3 + j) + 8 * i, 13).Resize(, 3).Value _
                        = .Cells(5 + i, 17 + 8 * j).Resize(, 3).Value
                Cells((3 + j) + 8 * i, 17).Resize(, 3).Value _
                        = .Cells(5 + i, 81 + 8 * j).Resize(, 3).Value
            Next j
        Next i
    End With

 End Sub

 (Bun)

---

        For r = 3 To 10
            Cells(r, 2).Value = .Cells(5, 2).Value
            Cells(r, 6).Value = .Cells(5, 7).Value
            Cells(r, 7).Value = .Cells(5, 8).Value
        Next r
 此れも200回に含めるなら、こんなかな？
 位置計算を変数に格納して

 Sub セルの参照_2()

    Dim DS As Worksheet
    Dim i As Long
    Dim j As Long
    Dim lngCol As Long
    Dim lngRowR As Long
    Dim lngRowL As Long

    Set DS = ThisWorkbook.Worksheets("data_sheet")

    With DS
        For i = 0 To 199
            For j = 0 To 7
                lngRowL = 3 + j + 8 * i
                lngRowR = 5 + i
                lngCol = 8 * j
                Cells(lngRowL, 2).Value = .Cells(lngRowR, 2).Value
                Cells(lngRowL, 6).Value = .Cells(lngRowR, 7).Value
                Cells(lngRowL, 7).Value = .Cells(lngRowR, 8).Value
                Cells(lngRowL, 13).Resize(, 3).Value = .Cells(lngRowR, 17 + lngCol).Resize(, 3).Value
                Cells(lngRowL, 17).Resize(, 3).Value = .Cells(lngRowR, 81 + lngCol).Resize(, 3).Value
            Next j
        Next i
    End With

 End Sub

 (Bun)

---

Bunさん、ありがとうございます！
セルの参照2の方で出来ました。
もっと短い式になるのではないかと思ったのですが、
まさかこんなに短い式になるとは＠＠/~~~
すごいです。
ぱっと見て理解ができないので、今からじっくりどういう構造なのか
読ませてもらいます。
取り急ぎ、お礼を言いに来ました。
（それから、どう祖、は打ち間違いです…お恥ずかしい）
本当にありがとうございました。

（えま）

---

 これで上手く行きましたか？
 善かったですね？
 一応、纏め方に就いて書いて起きます

 質問で、コードの先頭のループの以降の部分は、以下の様に書き換える事が出来ます

            Cells(3, 13) = .Cells(5, 17)
            Cells(3, 14) = .Cells(5, 18)
            Cells(3, 15) = .Cells(5, 19)

            Cells(4, 13) = .Cells(5, 25)
            Cells(4, 14) = .Cells(5, 26)
            Cells(4, 15) = .Cells(5, 27)

            Cells(5, 13) = .Cells(5, 33)
            Cells(5, 14) = .Cells(5, 34)
            Cells(5, 15) = .Cells(5, 35)

            Cells(6, 13) = .Cells(5, 41)
            Cells(6, 14) = .Cells(5, 42)
            Cells(6, 15) = .Cells(5, 43)

            Cells(7, 13) = .Cells(5, 49)
            Cells(7, 14) = .Cells(5, 50)
            Cells(7, 15) = .Cells(5, 51)

            Cells(8, 13) = .Cells(5, 57)
            Cells(8, 14) = .Cells(5, 58)
            Cells(8, 15) = .Cells(5, 59)

            Cells(9, 13) = .Cells(5, 65)
            Cells(9, 14) = .Cells(5, 66)
            Cells(9, 15) = .Cells(5, 67)

            Cells(10, 13) = .Cells(5, 73)
            Cells(10, 14) = .Cells(5, 74)
            Cells(10, 15) = .Cells(5, 75)

	'★

            Cells(3, 17) = .Cells(5, 81)
            Cells(3, 18) = .Cells(5, 82)
            Cells(3, 19) = .Cells(5, 83)

            Cells(4, 17) = .Cells(5, 89)
            Cells(4, 18) = .Cells(5, 90)
            Cells(4, 19) = .Cells(5, 91)

            Cells(5, 17) = .Cells(5, 97)
            Cells(5, 18) = .Cells(5, 98)
            Cells(5, 19) = .Cells(5, 99)

            Cells(6, 17) = .Cells(5, 105)
            Cells(6, 18) = .Cells(5, 106)
            Cells(6, 19) = .Cells(5, 107)

            Cells(7, 17) = .Cells(5, 113)
            Cells(7, 18) = .Cells(5, 114)
            Cells(7, 19) = .Cells(5, 115)

            Cells(8, 17) = .Cells(5, 121)
            Cells(8, 18) = .Cells(5, 122)
            Cells(8, 19) = .Cells(5, 123)

            Cells(9, 17) = .Cells(5, 129)
            Cells(9, 18) = .Cells(5, 130)
            Cells(9, 19) = .Cells(5, 131)

            Cells(10, 17) = .Cells(5, 137)
            Cells(10, 18) = .Cells(5, 138)
            Cells(10, 19) = .Cells(5, 139)

 この様に並べ帰ると解ると思いますが、★印以前と以降では、セルの位置が関連無く代わるので
 一つこコードをループで回すには無理な様な気がします、因って此処で二つに分けて考えます
 前半部の

            Cells(3, 13) = .Cells(5, 17)
            Cells(3, 14) = .Cells(5, 18)
            Cells(3, 15) = .Cells(5, 19)

 は、連続した1行3列の範囲の値を1行3列の範囲に代入と考えられますので

 Range(Cells(3, 13), Cells(3, 15)).Value = .Range(.Cells(5, 17), .Cells(5, 19)).Value

 若しくは

 Cells(3, 13).Resize(, 3).Value = .Cells(5, 17).Resize(, 3).Value

 と書けます（ResizeはHelpで調べて下さい）、因って

 Cells(3, 13).Resize(, 3).Value = .Cells(5, 17).Resize(, 3).Value
 Cells(4, 13).Resize(, 3).Value = .Cells(5, 25).Resize(, 3).Value
 　　・
 Cells(10, 13).Resize(, 3).Value = .Cells(5, 17).Resize(, 73).Value

 と成りますので、此れをループにすると

    For j = 0 To 7
        Cells(3 + j, 13).Resize(, 3).Value = .Cells(5, 17 + 8 * j).Resize(, 3).Value
    Next j

 カウンタjが0〜7に変化するなら
 左辺は、行の基数3に対して変化すjの値を加算しています
 また右辺は列が8列づつ変化すれば善いのですから列の基数17にｊの値×8を加算して行きます

 同様に★印以降は

 Cells(3, 17).Resize(, 3).Value = .Cells(5, 81).Resize(, 3).Value
 　　・
 Cells(10, 17).Resize(, 3).Value = .Cells(5, 137).Resize(, 3).Value

 と書けますので

    For j = 0 To 7
        Cells(3 + j, 17).Resize(, 3).Value = .Cells(5, 81 + 8 * j).Resize(, 3).Value
    Next j

 と成ります、上のループと一緒にして

    For j = 0 To 7
        Cells(3 + j, 13).Resize(, 3).Value = .Cells(5, 17 + 8 * j).Resize(, 3).Value
        Cells(3 + j, 17).Resize(, 3).Value = .Cells(5, 81 + 8 * j).Resize(, 3).Value
    Next j

 此れで1ブロックが転記出来ます（For r = 3 To 10のループ部は除いて）

 次に、此れを200回回すので「For j =」の外側に「For i = 0 To 199」のループを付けます
 この時、一回回る度に、左辺は8行下に、右辺は1行右に移動させるので

        For i = 0 To 199
            For j = 0 To 7
                Cells((3 + j) + 8 * i, 13).Resize(, 3).Value = .Cells(5 + i, 17 + 8 * j).Resize(, 3).Value
                Cells((3 + j) + 8 * i, 17).Resize(, 3).Value = .Cells(5 + i, 81 + 8 * j).Resize(, 3).Value
            Next j
        Next i

 と成ります、此れに「For r = 3 To 10」のループ部を組み込み、同じ計算を何度も書かない様に
 計算結果を変数に入れて使っているのが「Sub セルの参照_2()」と成ります

 (Bun)

---

Bunさん、丁寧な解説をありがとうございます。
ひとつひとつ突き合わせながら、なるほど、こう考えるのかと読んで行きました。
読み進むたびに、なるほどと納得できるまで時間がかかったのですが^^;

ところで、0ってすごいですね。
足してよし、掛けてよし、1からではなく0から始まるのはこういう理由かと感心しました。

昨日何とか理解できた気がする、というときにこれに似たような別の仕事が来たので、
やってみたのですが、難しい方(Resizeが入っている方）はできたのに、
簡単な方(数値は違いますが）
For r = 3 To 10

            Cells(r, 2) = .Cells(5, 2)
            Cells(r, 6) = .Cells(5, 7)
            Cells(r, 7) = .Cells(5, 8)

は一塊りしか入りませんでした。
どういうこと？？？と思った時点で終業時間がきたので、今日の朝一でリベンジです。
別のパターンでもできました、と報告できないのが残念ですが
基本は理解できたと思います。
本当にありがとうございました。

（えま）

---

 >ところで、0ってすごいですね。 
 >足してよし、掛けてよし、1からではなく0から始まるのはこういう理由かと感心しました。 

 確かに、0の発見ですね(私も、0偉いと思います？)

 >昨日何とか理解できた気がする、というときにこれに似たような別の仕事が来たので、 
 >やってみたのですが、難しい方(Resizeが入っている方）はできたのに、 
 >簡単な方(数値は違いますが） 
 >For r = 3 To 10 
 >
 >            Cells(r, 2) = .Cells(5, 2)
 >            Cells(r, 6) = .Cells(5, 7)
 >            Cells(r, 7) = .Cells(5, 8)
 > 
 >は一塊りしか入りませんでした。 
 >どういうこと？？？と思った時点で終業時間がきたので、今日の朝一でリベンジです。 
 >別のパターンでもできました、と報告できないのが残念ですが 
 >基本は理解できたと思います。 

 こう言うのは、何がどういう法則で変化しているのかを見つけられるのかが肝心な所です
 この為には人に教えられる因り、自分で試して納得するしか無い様な気がします
 ですので、えまさんも頑張って試して下さい

 後、今回の質問と離れるかもしれませんが、一列のデータを方形に変換する方法等も有りますので
 暇が在ったら試して見ると面白いかも？

 (Bun)

---

Bunさん、ありがとうございました。
無事リベンジできましたv

ところで、

For i = 0 To 199

            For j = 0 To 7

のうち、j を入れなくても出来たような気がします。
またもや時間切れで元のデータとの突き合わせができなかったので
ぱっと見だけの印象ですが。
明日もう一度試してみます。

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

（えま）

---

コメント返信：

---

[ 一覧(最新更新順) ]

---

YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.