[[20200427220811]]　『Rangeで指定したセル範囲の転記』（アイス）　ページの最後に飛ぶ

『Rangeで指定したセル範囲の転記』（アイス）

よろしくお願いいたします。
Sheet1のようなデータがあります。
A列は1,2,3とリピートした縦持ちデータです。
これをSheet2のように転記したいと思います。
すなわち、
Sheet1のA1:B3セル範囲→Sheet2のA1:B3セル範囲
Sheet1のA4:B6セル範囲→Sheet2のC1:D3セル範囲
Sheet1のA7:B9セル範囲→Sheet2のE1:F3セル範囲
というように3行2列分のセル範囲を順に転記します。

●Sheet1

 ______A列＿＿＿B列_____
 1|	1	a
 2|	2	b
 3|	3	c
 4|	1	d
 5|	2	e
 6|	3	f
 7|	1	g
 8|	2	h
 9|	3	i

●Sheet2

 _______A列＿＿B列＿＿＿C列＿＿D列＿＿＿E列＿＿F列＿＿
 1|	1	a	1	d	1	g
 2|	2	b	2	e	2	h
 3|	3	c	3	f	3	i

そこで、ごく単純な下記コードをトライしました。
Sub macro()

    Set ws1 = Sheets("Sheet1")  'ws1：元データ格納シート
    Set ws2 = Sheets("Sheet2")  'ws2：データの転記先
    For i = 1 To 3
        ws2.Range(Cells(1, i * 2 - 1), Cells(3, i * 2)).Value = ws1.Range(Cells(i * 3 - 2, 1), Cells(i * 3, 2)).Value
    Next i
End Sub

これが、「アプリケーション定義またはオブジェクト定義のエラーです。」というコメントでエラーとなります。
ステップインでみるとws2.Range～～の行でハイライトされます（というよりこの行くらいしかないですが）。
一体何を見落としているのでしょうか？

＜使用 Excel：Excel2013、使用 OS：Windows8 ＞

 参考に
 Sub macro()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim i As Long, j As Long

    Set ws1 = Sheets("Sheet1")                                  'ws1：元データ格納シート
    Set ws2 = Sheets("Sheet2")                                  'ws2：データの転記先
    For i = 1 To ws1.Cells(Rows.Count, "A").End(xlUp).Row Step 3
        j = (i \ 3 + 1) * 2 - 1
        ws2.Cells(1, j).Resize(3, 2).Value = ws1.Cells(i, 1).Resize(3, 2).Value
    Next i
 End Sub

（ピンク） 2020/04/27(月) 22:35

 >ws2.Range～～の行でハイライトされます（というよりこの行くらいしかないですが）。

 ws2.Range(ws2.Cells(1, i * 2 - 1), ws2.Cells(3, i * 2)).Value = ws1.Range(ws1.Cells(i * 3 - 2, 1), ws1.Cells(i * 3, 2)).Value

（ピンク） 2020/04/27(月) 22:41

 こんばんは！
被りまくりクリスティーですけども、、、(^^;
これはExcelあるあるでほとんどの人が躓く代表的なエラーですね。
Rangeの中のCellsにも　.　を付けてシート指定しないといけません。
一つ目は、Withで　二つ目は直接になりますかな？？？

 Sub macro()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim i As Long
Set ws1 = Sheets("Sheet1")  'ws1：元データ格納シート
Set ws2 = Sheets("Sheet2")  'ws2：データの転記先
For i = 1 To 3
    With ws2
        .Range(.Cells(1, i * 2 - 1), .Cells(3, i * 2)).Value = ws1.Range(ws1.Cells(i * 3 - 2, 1), ws1.Cells(i * 3, 2)).Value
    End With
Next i
End Sub
（SoulMan） 2020/04/27(月) 22:45

 参考に

    For i = 1 To 3
        ws2.Cells(1, i * 2 - 1).Resize(3, 2).Value = ws1.Cells(i * 3 - 2, 1).Resize(3, 2).Value
    Next i

（ピンク） 2020/04/27(月) 22:51

ピンク様　Soulman様

ご回答下さり誠にありがとうございます。
For next文でstep刻みにすることで、
i=1,4,7と変化し、それに対応して、j=1,3,5と変化します。
割り算の商を利用することで、Sheet1での3行リピートがSheet2での2列リピートへと変換されるわけですね！いつもながら大変勉強になるところです。ありがとうございます！

シート間操作する場合、Rangeの中にはcellsの前にもシート指定・・
VBA歴1ヶ月ほどで初めて経験してしまいました。忘れないようによくよく覚えておきます。
もしそれが煩雑な場合は先にWith文で指定するのもありということですね。ありがとうございます。

Resizeを使えば、Rangeを使わなくてもセル範囲の1箇所だけ指定して拡張させることが出来るので、
Rangeの中でCellsの前にシート指定することから解放されるというメリットもあるのでしょうか。
今後セル範囲指定するときもResizeの拡張で出来てしまうので、
そうなるとRangeの存在意義って、、なんて初心者の私は思ってしまいました。
使いこなせばそれくらい有効なのだと感じた次第でした。

お二方とも様々なTipsご紹介くださりありがとうございました。

（アイス） 2020/04/27(月) 23:18

話はおわってるようですが、Valueプロパティを直接参照することに拘らなければ、コピペで対応できますね。

    Sub 別案1()
        Dim i As Long

        For i = 0 To 2
            Worksheets("Sheet1").Range("A1:B3").Offset(i * 3, 0).Copy _
            Worksheets("Sheet2").Range("A1").Offset(0, i * 2)
        Next
    End Sub
    '===========================================================
    Sub 別案2()
        Dim i As Long

        For i = 0 To 2
            Worksheets("Sheet1").Range("A1:B3").Offset(i * 3, 0).Copy
            Worksheets("Sheet2").Range("A1").Offset(0, i * 2).PasteSpecial Paste:=xlPasteValues
        Next
    End Sub

（もこな２） 2020/04/28(火) 00:26

もう一つオマケで。

    Sub 別案3()
        Dim i As Long

        For i = 0 To 2
            With Worksheets("Sheet1").Range("A1:B3")
                Worksheets("Sheet2").Range("A1").Offset(0, i * 2).Resize(.Rows.Count, .Columns.Count).Value = .Offset(i * 3, 0).Value
            End With
        Next
    End Sub

（もこな２） 2020/04/28(火) 11:41

　＞そうなるとRangeの存在意義って、、なんて初心者の私は思ってしまいました。

いろいろな言葉を知っていれば、さまざまな場面で応用が効きます。

Option Explicit

Sub test()

    Dim rngTable As Range
    Dim rngCopyFrom As Range
    Dim rngCopyTo As Range
    Dim ixRow As Long
    Dim ixCol As Long
    Const n As Long = 3

    Set rngTable = Worksheets("Sheet1").Range("A1").CurrentRegion
    Set rngCopyFrom = rngTable.Resize(n)
    Set rngCopyTo = Worksheets("Sheet2").Range(rngCopyFrom.Address)

    For ixRow = 1 To rngTable.Rows.Count Step n
        rngCopyTo.Offset(, ixCol).Value = rngCopyFrom.Offset(ixRow - 1).Value
        ixCol = ixCol + rngCopyTo.Columns.Count
    Next
End Sub

それぞれのセルは、それぞれ、どのシートに属するか情報を持ってます。
（今回はそれでエラーになった）
なので１回変数に代入しておいて、
「それ」に対する相対位置を示めせば、度々シートの指定をしなくて済みますし、
シートの指定を間違うことが無くなります。
（Withを使うことは、名前の無い変数を用意したと考えることが出来ます。）
そして、今回の件の場合は、Offsetプロパティを使うことで毎度毎度Resizeする
必要もなくなるかもです。（この辺はお好みで）

（まっつわん） 2020/04/28(火) 12:27

コメント返信：

[ 一覧(最新更新順) ]

[[20200427220811]] 『Rangeで指定したセル範囲の転記』（アイス） ページの最後に飛ぶ

[[20200427220811]]　『Rangeで指定したセル範囲の転記』（アイス）　ページの最後に飛ぶ