エクセルでのマクロが作れません

質問

簡単なマクロ構文が作れなくて困っています。
セルＡ1、Ｂ1、Ｃ1・・・と約5000個続く１行のデータを９列のデータに並べ直すだけなのですが（Ａ1、Ｂ1Ｃ1・・・Ｉ1、Ａ2、Ｂ2、Ｃ2・・・Ｉ2、Ａ3、Ｂ3、Ｃ3・・・というふうに）、当方マクロなど組んだこともなく、途方に暮れております。一応ネット等を参考にして、以下のようにある程度組んでみたのですが、全然動きません（泣）。

Sub Sample()

Dim i
Dim ｊ
Dim k

For i= 1 to 5000
j= INT(i \ 10)
k=(i Mod 10)
Worksheets("Sheet1").[1,i] Copy
Destination=Worksheets("Sheet2").Paste(1+J,k)
Next i

Sub.End

どなたか、ご教示頂けませんか。
よろしくお願いいたします。

回答No.1

kkkkkm · ベストアンサー

コピペだと時間がかかるので代入したほうが早いです。

Sub Example()

Dim i As Long
Dim j As Long

j = 1
For i = 1 To 5000 Step 9
Worksheets("Sheet2").Cells(j, 1).Resize(1, 9).Value = Worksheets("Sheet1").Cells(1, i).Resize(1, 9).Value
j = j + 1
Next i

End Sub

お礼

早速のご対応、ありがとうございます。

明日は会社休みとっていますので、明後日お答え頂いた方法で試してみます。

回答No.6

WindFaller

こんにちは。

すでに回答はついていますが、あくまでも、VBAとしてお答えしたいと思います。
#3で、おっしゃるように、本来は、関数で解決ということでしょう。

確認ですが、ご質問は、横に一行に並んでいるデータ(シーケンシャル？)を、9列ごとに分けてシートに貼り付けることですね。

基本の基本ですが、Excel VBAというのは、[標準モジュール]を使わせようとしているという点です。その点で、おやっ？と感じる人は、他のVBAやVBを使っている人かもしれません。

また[1,i]　という書き方は、本来、[A1]と書くのですが、これは、Evaluateメソッドの省略形ですから、それを踏まえた使い方があるのですが、基本的には、VBAでは、使わないほうがよいです。

もし、基本テクニックだけで解決しようとすると、このようなスタイルになると思います。
バグの起こしやすい所はあっても、一応、通るはずです。

'//
Sub Sample01()
Dim i As Long　'入門編では、Long等のデータ型は無視してよいです。
Dim j As Long
Dim k As Long
Worksheets("Sheet1").Select　'ちょっとした工夫（意味があります)
For i = 1 To 5000
　j = k + 1
　k = i * 9
　 Range(Cells(1, j), Cells(1, k)).Copy Destination:= _
　　Worksheets("Sheet2").Cells(i, 1)
　If k > 5000 Then Exit For　'入門編では気が付きませんが……
Next i
End Sub
'//

上記のマクロを少し手直しすると、以下のようになり、すっきりとしてきます。
#1,#2で書いていらっしゃる方のように、「Resize」がミソなのですが、掲示板ではおなじみです。しかし、あまりテキストには出てこないような気がします。以下のように、横に「9」ではなく、自分のセルを数えてしまうので、「8」になってしまいます。

'//
Sub Sample02()
　Dim i As Long
　Dim j As Long
　Worksheets("Sheet1").Select
　For i = 1 To 5000 Step 9　'←あらたにStepを覚えると
　　j = j + 1
　　Range(Cells(1, i), Cells(1, i + 8)).Copy Destination:= _
　　Worksheets("Sheet2").Cells(j, 1)
　Next i
End Sub
'//

簡単なようで、やはり書き慣れていないと、難しいと感じていただけたでしょうか。

さて、エキスパートの人なら、どう書くであろうか、ちょっと考えたくなりますね。
配列？この種の内容で、配列というのは大げさかもしれませんね。
こんなことを書く人はいないとは思いますが……。

'//
Sub SampleEx()
　Dim myData1 As Variant
　Dim myData2 As Variant
　Dim i As Long
　Dim j As Long
　Dim k As Long
　Dim u As Long
　With Worksheets("Sheet1")
　myData1 = .Range("A1", .Cells(1, Columns.Count).End(xlToLeft)).Value　'*
　End With
　myData1 = Application.Index(myData1, 1, 0)　'古い書き方 '**
　u = Int(UBound(myData1) / 9 + 0.9)　'切り上げ
　ReDim Preserve myData1(1 To u * 9)　'エラー対策
　ReDim myData2(1 To u, 1 To 9)
　i = 1
　For k = 1 To u
　　For j = 1 To 9
　　　myData2(k, j) = myData1(i)
　　　i = i + 1
　　Next j
　Next k
　Worksheets("Sheet2").Range("A1").Resize(u, 9).Value = myData2
End Sub
'元データが縦(1-5000)の場合
'*　　myData1 = .Range("A1", .Cells(Rows.Count, 1).End(xlUp)).Value
'**　　myData1 = Application.Transpose(myData1)
'//

実は、この種の問題で、何年やっても、私は実現しなかったのは、実務でのデータが手に入らないという単純な理由なのです。掲示板では、たびたび登場する質問のものの、この種のデータには、テキストデータにしか見えないものの、隠れたバイナリの信号が混じっていたりすることがあります。

お礼

様々な観点から丁寧な解説をありがとうございます。

まだ私はほんの１週間前にマクロに出会った超初心者なので、

まだまだわからないところが多々ありますが、これからじっくりと勉強していこうと思います。

皆様のお助けにより、最初の時点での挫折がなくなったような気がします。

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

回答No.5

weboner

No3，4回答者です

質問内容を読み違えていました
1列のデータを9列のデータに変換（縦を横）
ではなく
1行のデータを9列のデータに変換（横を分割）
だったのですね

それを踏まえて再回答

No3は

Sheet2の名前ボックスに
A1：I556　　・・・・・・Sheet1のデータ個数に合わせて
と入力して範囲を指定
A1セルに
=INDEX(Sheet1!$1:$1,(ROW()-1)*9+COLUMN()))
↑
この式を入力して【Ctrl】+【Enter】
そのまま右クリックでコピー
再度右クリックで貼り付け（数値のみ）

この動作で完了

この操作をマクロの記録で保存すると

Sub Macro3()
Application.Goto Reference:="R1C1:R600C9"
Selection.FormulaR1C1 = "=INDEX(Sheet1!R1,(ROW()-1)*9+COLUMN())"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

となります

No4の方は

配列を使って1行ずつ処理する場合

Sub Sample1()
Dim tmp As Variant
l = 1

For i = 1 To 5000 Step 9

tmp = Worksheets("Sheet1").Cells(1, i).Resize(1，9).Value

Worksheets("Sheet2").Cells(l, 1).Resize(1, 9) = tmp

l = l + 1
Next
End Sub

最初に一括で配列に収納する方法

Sub sampleX()
Dim tmp(556, 9) As Variant

n = 1

For l = 0 To 556
For i = 0 To 8
tmp(l, i) = Worksheets("Sheet1").Cells(1, n).Value
n = n + 1
Next i
Next l

Worksheets("Sheet2").Cells(1, 1).Resize(556, 9) = tmp()
End Sub

こんな感じですかね

お礼

ご丁寧にありがとうございます。

より良い方法を考えて頂いていますことに深謝申し上げます。

回答No.4

weboner

ついでに配列を使った場合

Sub Sample1()
Dim tmp As Variant
l = 1

For i = 1 To 5000 Step 9

tmp = Worksheets("Sheet1").Cells(i, 1).Resize(9, 1).Value

Worksheets("Sheet2").Cells(l, 1).Resize(1, 9) = WorksheetFunction.Transpose(tmp)

l = l + 1
Next
End Sub

最初に一括で配列に収納する方法

Sub sampleX()
Dim tmp(556, 9) As Variant

n = 1

For l = 0 To 556
For i = 0 To 8
tmp(l, i) = Worksheets("Sheet1").Cells(n, 1).Value
n = n + 1
Next i
Next l

Worksheets("Sheet2").Cells(1, 1).Resize(556, 9) = tmp()
End Sub

データ数が多くなればなるほど配列を利用したほうが処理は早くなると思います

回答No.3

weboner

この質問内容ならマクロ使わなくとも

Sheet2の名前ボックスに
A1：I556　　・・・・・・Sheet1のデータ個数に合わせて
と入力して範囲を指定
A1セルに
=INDEX(Sheet1!$A:$A,(ROW()-1)*9+COLUMN())
↑
この式を入力して【Ctrl】+【Enter】
そのまま右クリックでコピー
再度右クリックで貼り付け（数値のみ）

この動作で完了

この操作をマクロの記録で保存すると

Sub Macro3()
Application.Goto Reference:="R1C1:R600C9"
Selection.FormulaR1C1 = "=INDEX(Sheet1!C1,(ROW()-1)*9+COLUMN())"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

こんな感じ

その他にも配列を使ったり
まあ、いろんな方法があるよね

お礼

ありがとうございます。

色々な方法があるのですね。

早速明日、会社で試してみます。

回答No.2

kkkkkm

No1です

ちなみにコピペだと以下のようになります。
書式設定とかも同じにしたい場合はコピペになりますね。
あとの方ほど遅いです。

例１
Sub Example2()

Dim i As Long
Dim j As Long

j = 1
For i = 1 To 5000 Step 9
Worksheets("Sheet1").Cells(1, i).Resize(1, 9).Copy Worksheets("Sheet2").Cells(j, 1).Resize(1, 9)
j = j + 1
Next i

End Sub

例２
Sub Example3()

Dim i As Long
Dim j As Long

j = 1
For i = 1 To 5000 Step 9
Worksheets("Sheet1").Cells(1, i).Resize(1, 9).Copy
Worksheets("Sheet2").Cells(j, 1).Resize(1, 9).PasteSpecial
j = j + 1
Next i

End Sub

お礼

様々な手法をご教示頂き、まことに有難うございます。

参考にさせて頂きます。

これを機にマクロについて勉強をしてみたいと思っております。