VBA ファイルの内容を並べ替えたい

csvファイルを別のファイル(エクセル)に並べ替えて保存したいと考えてVBAでプログラムを
書いてみたのですがうまくできないので解決策をご教示いただけないでしょうか。


960行640列のデータを，「行，列，値」←エクセルでいうとA列＝行，B列＝列，C列=値
の形に並べ替えようとしています。

　行　　列　　値
　1　　　1　 　28
1 　 2 　10
　1　　　3　　　0
　・　　　・　　　・
　・　　　・　　　・
　960　640　 37

といった具合です。


実行すると，行と列は並べ替えて表示されるのですが，値だけはどの行でも同じ値が入ってしまいます。元のデータでは値はランダムです。

書いたプログラムを下に載せます。
配列tmpのインデックスがループを追うごとに変化しないことが原因かと考えていますが，
変数nを追加して　”For tmp = 0 To UBound(tmp) Next n”　を　”For j = 1 To 640　Next j”
の中に入れてみましたが(これでは意図と違う表示がされそうですが)，結果はおなじでした。

配列tmpの中身を，対応するセルに一つ一つ入れていくにはどのようにしたら良いのでしょうか。

本やネットの情報をつまみ食いして書いているので，理屈やスマートな書き方など
よくわかっておらず，見苦しいものかとも思いますがどうぞよろしくお願いします。


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

Sub　並べ替え()

Dim i As Long, j As Long, m As Long
Dim buf As String, tmp As Variant

Open "ファイル" For Input As #1


m = 1

Input #1, buf
tmp = Split(buf, ",")

Cells(1, 1).Value = "X"
Cells(1, 2).Value = "Y"
Cells(1, 3).Value = "Z"


For i = 1 To 960
For j = 1 To 640

m = m + 1
Cells(m, 1).Value = i
Cells(m, 2).Value = j
Cells(m, 3).Value = tmp

Next j
Next i


Close #1

ChDir "新しいファイル"
ActiveWorkbook.SaveAs Filename:="新しいファイル", _
FileFormat:=xlCSV, CreateBackup:=False

End Sub

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

No.2 ベストアンサー 回答者： eden3616 回答日時： 2014/09/26 16:02

CSVのデータは960行640列あるんですよね？

以下の解釈ですと、
それを1行目は項目行として614401行（960×640+1行）×3列（A～C列）に出力します。

残念ながら、ご提示のコードでは目的を果たしておりません。
CSVのデータをbufに読み込んだ後、tmpに1行ずつ格納してから
格納した1行分のデータをsplitで列に分解する必要があります。

標準モジュールに以下のコードを貼り付けて
コード内の「CSVのファイルパス」を適切に変更してください。
出力先のシートを選択してから、マクロ「並び替え」を実行してください。
（614401×3=1843203セルのデータを書出すため、時間がかかります）

最下のマクロ「並べ替え2」は1行分のデータを丸ごと配列変数に格納して、
1行分のデータ（1920セル）を出力時にセル範囲へ配列を書出しますので処理速度が向上しています。

このように毎回セルへデータを書出さず、配列で処理を行った後
出力をまとめてすることで処理速度を上げることが出来ますので試行錯誤してみてください。

参考：『VBA高速化テクニック』
http://officetanaka.net/Excel/vba/speed/index.htm


■VBAコード

Sub 並べ替え()

Dim i As Long, j As Long, m As Long
Dim buf As String, tmp As Variant

'出力先の行番号初期値
i = 1
'1行目（項目）を出力
Range("A1:C1") = Array("X", "Y", "Z")
Exit Sub
'CSVをテキストデータとして読込
Open "CSVのファイルパス" For Input As #1
'画面の更新停止
Application.ScreenUpdating = False
'データ終末まで繰り返し
Do Until EOF(1)
　　'CSVから1行読込
　　Line Input #1, buf
　　'読み込んだ行分のデータを「,」カンマで列に分割
　　tmp = Split(buf, ",")
　　'CSVの行番号をカウントアップ
　　i = i + 1
　　'列の数だけ繰り返し
　　For j = 0 To UBound(tmp)
　　　　'出力行をカウントアップ
　　　　m = m + 1
　　　　'データを出力
　　　　Cells(m, 1) = i
　　　　Cells(m, 2) = j + 1
　　　　Cells(m, 3) = tmp(j)
　　Next j
　　'進行度合いを表示
　　If i Mod 5 = 0 Then Application.StatusBar = i & "行分を出力中": DoEvents
Loop
Close #1
'画面を更新
Application.ScreenUpdating = True
End Sub


■VBAコード（速度向上版）

Sub 並べ替え2()

Dim i As Long, j As Long, m As Long
Dim buf As String, tmp As Variant
Dim myData() As String

'1行目（項目）を出力
Range("A1:C1") = Array("X", "Y", "Z")
'CSVをテキストデータとして読込
Open "CSVのファイルパス" For Input As #1
'画面の更新停止
Application.ScreenUpdating = False
'データ終末まで繰り返し
Do Until EOF(1)
　　'CSVから1行読込
　　Line Input #1, buf
　　'読み込んだ行分のデータを「,」カンマで列に分割
　　tmp = Split(buf, ",")
　　'CSVの行番号をカウントアップ
　　i = i + 1
　　Erase myData
　　ReDim myData(UBound(tmp), 2)
　　'列の数だけ繰り返し
　　For j = 0 To UBound(tmp)
　　　　'出力行をカウントアップ
　　　　m = m + 1
　　　　'データを配列に格納
　　　　myData(j, 0) = i
　　　　myData(j, 1) = j + 1
　　　　myData(j, 2) = tmp(j)
　　Next j
　　'格納したデータを最終行に追加出力
　　With Cells(Rows.Count, "A").End(xlUp)
　　　　Range(.Offset(1, 0), .Offset(UBound(tmp) + 1, 2)) = myData
　　End With
　　'進行度合いを表示
　　If i Mod 5 = 0 Then Application.StatusBar = i & "行分を出力中": DoEvents
Loop
Close #1
'画面を更新
Application.ScreenUpdating = True
End Sub

No.4 回答者： eden3616 回答日時： 2014/09/26 16:36

No2です。

度々申し訳ありません。

コードの修正になります。

1つ目の「■VBAコード」（速度向上版ではない方）において、

　(1) 「i = 1」を「m = 1」にしてください。
　(2) 「Exit Sub」を削除してください。

この回答へのお礼

inputでは全部のデータが読めるわけではないんですね。
line　input　で一行ずつ読みだしてから…
まだプログラムは書き直し，動作させていませんが，この後やってみます！
ありがとうございます！

お礼日時：2014/09/26 18:46

No.3 回答者： eden3616 回答日時： 2014/09/26 16:22

No2です。

処理中に進行度合いを表現するメッセージ「○行分を出力中」を
ステータスバーに表示していますがステータスバーを初期化し忘れていました。
処理後もこのメッセージが残ってしまいます。
（エクセルを再起動すれば治りますが）

両コードとも、最後のEnd Subの手前に以下のコードを追加してください。

Application.StatusBar = False

また実行後の出力結果を添付いたします。

No.1 回答者： FEX2053 回答日時： 2014/09/26 14:40

そりゃ当然です。

だってi,jともに「１から960/680までカウントする変数」でしかないんですから。

やりたいことは、「元のCSVファイルに入ってる行、列の位置に、値を入れたい」
って話ですよね。この場合読み込んだシートには

１．A1セルに1レコード目の行位置
２．B1セルに1レコード目の列位置
３．C1セルに1レコード目の値
４．A2セルに2レコード目の行位置
５．B2セルに2レコード目の列位置
６．C2セルに2レコード目の値
：
：

と入ってる筈。なので、別ブックを開いて相互にデータをコピーしまくるか、
一旦レコードを全件読み込んで、別ファイルに一気に書き出すかしか方法は
無いです。プログラムが書きやすいので、後者の手法を取ると

まず、CSVのレコード件数を取得して、変数「Ｍ」に設定しておきます。

FOR I = 1 TO M
J1=cells(I,1)
J2=cells(I,2)
D1=cells(I,3)
Data(J1,J2)=D1
NEXT

これで、配列Dataに読み込んだレコードが行列位置を合わせて書き出せます。
なので、新しいブックを開いてこいつを

For i = 1 To 960
For j = 1 To 640
cells(i,j)=Data(i,j)
Next j
Next i

これで書き出せるはずです。

この回答への補足

すみません，第一声を間違えていました。
質問にお答えくださりありがとうございました！！

補足日時：2014/09/26 18:47

この回答へのお礼

＞そりゃ当然です。
＞だってi,jともに「１から960/680までカウントする変数」でしかないんですから。

確かに，そりゃ当然ですよね。言われてようやく気づけました。アハ体験だったり情けなかったり…
これからアドバイスを参考に書き直してみます！

お礼日時：2014/09/26 18:44