エクセルVBA 配列の書き方とセルへの一括表示方法

質問

エクセルのVBAで
下記のようなプログラムを作成しています。

1行目はタイトル行で

E列が空白になるまで、
各行のE列～H列の値を変数に入れて、
最後に一括で別の列・行にそれぞれの値を表示・書込したいと思ってますが、
どうもVBAは初めてでよくわかりません。

Dim aaa As String
Dim bbb As String
Dim ccc As String
Dim eee As Double

intRow = 2
Do Until Cells(intRow, 5).Value = ""
aaa = Cells(intRow, 5).Value)
bbb = Cells(intRow, 6).Value)
ccc = Cells(intRow, 7).Value)
ddd = Cells(intRow, 8).Value)

intRow = intRow + 1
Loop

aaaの各変数を2行目のA1～intRowまで
bbbの各変数を2行目のB1～intRowまで
cccの各変数を2行目のC1～intRowまで
dddの各変数を2行目のD1～intRowまで

セルに一括して表示したいのです。

配列の書き方と、セルの範囲に表示・書込する方法を
どうかご教示下さい。お願いいたします。

回答No.4

eden3616 · ベストアンサー

>できましたら、loopした場合に配列をどのように入れて、
>最終的に、指定した列の2行目から値が入っている最大行(空白行)までの間に
>配列を放り込む方法を教えて頂けたらと思います。

先ほどの回答では味気ないため、「Sample3」において具体的に作成しました。
データの数が変動するかと思います。大きな配列を用意してもいいのですが、
ループ内で配列のサイズをReDimで広げています。

ReDimのPreserveで広げれる要素は多次元配列の最後の次元だけですので、
仮に1次元目に列、2次元目に行を格納する配列変数myDataを用意して
加工したデータをDo～Loop内で拡張しながら格納していきます。

ループを抜けたら、ワークシート関数の「Transpose」にて行列を入れ替えて
出力用の配列変数outDataに再度格納します。
これで1、2次元の要素数が入れ替わりますので、outData(行,列)として
セル範囲に書出すことが出来ます。

書出し先のセルは変数tarColにて列記号を指定することで
データの開始行番号と組み合わせたCells(intRow,tarCol)を基準セルとして
Resizeで配列の大きさにセル範囲を拡張し、配列outDataを書出しています。

>原因は私のプログラムにあるかとは思いますが、
>なぜか、デバッグ実行時には、きちんと値が入るセルが
>通常実行ではなぜか値が入らない場合があるためです。
>ネットに接続して値を取得している為、
>処理が重いのかどうなのかはわからないのですが。

aaa～dddの変数に加工した内容が正しくセットされているかは
本件とは異なる質問かと思いますので、デバックにて確認ください。

■VBAコード

Sub Sumple3()
Dim intRow As Long, cnt As Long, tarCol As String
Dim aaa As String, bbb As String, ccc As String, ddd As String
Dim myData() As String, outData() As Variant

'配列初期化
ReDim myData(0, 0)
'開始行番号指定
intRow = 2
'出力列指定
tarCol = "A"

'データ取得及び配列格納
Do Until Cells(intRow + cnt, 5).Value = ""
　　'配列の要素数加算
　　If cnt > 0 Then
　　　　ReDim Preserve myData(0, UBound(myData, 2) + 1)
　　End If
　　'データの取得
　　aaa = Cells(intRow + cnt, 5).Value
　　bbb = Cells(intRow + cnt, 6).Value
　　ccc = Cells(intRow + cnt, 7).Value
　　ddd = Cells(intRow + cnt, 8).Value
　　'データの加工及び配列格納
　　myData(0, cnt) = aaa & "-" & bbb & "-" & ccc & "-" & ddd
　　'カウントアップ
　　cnt = cnt + 1
Loop

'配列の行列入替
outData = Application.WorksheetFunction.Transpose(myData)
'配列をセル範囲に出力
Cells(intRow, tarCol).Resize(UBound(outData, 1), UBound(outData, 2)) = outData
End Sub

補足

素晴らしく丁寧な回答ありがとうございます。
よく理解出来ました。

初めてVBAを使いましたが、すごく便利そうでこれからも活用していこうかなと思います。

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

回答No.3

eden3616

配列に格納後セル範囲を指定して配列変数を指定することで書き出すことが出来ます。
事前に用意した配列変数myDataに個々に格納する場合は以下のようになります。
（書出すセル範囲の数と配列の数は一致させてください）

以下のコードでは1行4列のデータ(セル範囲E2:H2)を・・・
　Sample1は4行1列（A4：A7）に書き出し、
　Sample2は1行4列（A4：D4）に書き出し　しています。

■VBAコード
Sub Sumple1()
Dim myData(3, 0) As String
Dim intRow As Long
Dim i As Long

　　'配列に値を格納
　　intRow = 2
　　myData(0, 0) = Cells(intRow, 5).Value
　　myData(1, 0) = Cells(intRow, 6).Value
　　myData(2, 0) = Cells(intRow, 7).Value
　　myData(3, 0) = Cells(intRow, 8).Value

　　'ここに配列に対する処理を記述
　　For i = 0 To 3
　　　　myData(i, 0) = myData(i, 0) & "セルの処理結果"
　　Next i

　　'(A4セルから、3+1行・0+1列に拡張したセル範囲に配列を)書き出し
　　Range("A4").Resize(UBound(myData, 1) + 1, UBound(myData, 2) + 1) = myData

End Sub

Sub Sumple2()
Dim myData(0, 3) As String
Dim intRow As Long
Dim i As Long

　　'配列に値を格納
　　intRow = 2
　　myData(0, 0) = Cells(intRow, 5).Value
　　myData(0, 1) = Cells(intRow, 6).Value
　　myData(0, 2) = Cells(intRow, 7).Value
　　myData(0, 3) = Cells(intRow, 8).Value

　　'ここに配列に対する処理を記述
　　For i = 0 To 3
　　　　myData(0, i) = myData(0, i) & "セルの処理結果"
　　Next i

　　'(A4セルから、0+1行・3+1列に拡張したセル範囲に配列を)書き出し
　　Range("A4").Resize(UBound(myData, 1) + 1, UBound(myData, 2) + 1) = myData

End Sub

お礼

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

回答No.2

eden3616

添付画像のようなA1セルからC10セルの範囲を配列変数に格納し、
E5セルからG14セルに書出しています。

Sub sumple()
Dim myData As Variant
　　'A1：C10を配列変数myDataに格納
　　myData = Range("A1:C10")
　　'D1セルを左上として、10行・3列のセル範囲にmyDataを書出し
　　Range("E5").Resize(UBound(myData, 1), UBound(myData, 2)) = myData
End Sub

また、セル書き出しの処理において作画で処理に時間がかかる場合があります。
大量のデータであれば上記のように配列に格納して一括で書き出すほうが早いんですが、
多少（数千程度）のデータであれば個々にループで書き出しても良いかと思います。

その際にかかる時間については、以下のコードで一時的に作画を停止し
再度処理の後に作画してあげれば高速化できます。

'作画の停止
Application.ScreenUpdating = False

'Do ～ Loop または For～Next による繰り返し処理

'作画の再開
Application.ScreenUpdating = True

数式が大量に有るシートですと計算式が更新される時間も停止させる
以下の処理も組み合わせると良いかと思います。

'自動計算OFF
Application.Calculation = xlCalculationManual

'自動計算ON
Application.Calculation = xlCalculationAutomatic

補足

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

aaaなどの変数には実際には編集した値が入るので

intRow = 2
Do Until Cells(intRow, 5).Value = ""
aaa = Cells(intRow, 5).Value)を元に編集した値が入ります
bbb = Cells(intRow, 6).Value)を元に編集した値が入ります
ccc = Cells(intRow, 7).Value)を元に編集した値が入ります
ddd = Cells(intRow, 8).Value)を元に編集した値が入ります

intRow = intRow + 1
Loop

myData = Range("A1:C10")
のようなパターンではなく、
できましたら、loopした場合に配列をどのように入れて、
最終的に、指定した列の2行目から値が入っている最大行(空白行)までの間に
配列を放り込む方法を教えて頂けたらと思います。

原因は私のプログラムにあるかとは思いますが、
なぜか、デバッグ実行時には、きちんと値が入るセルが
通常実行ではなぜか値が入らない場合があるためです。

ネットに接続して値を取得している為、
処理が重いのかどうなのかはわからないのですが。

作画の停止、再開は組み込んでみましたが、そんなに早さは変わらなくて。。
Application.ScreenUpdating = False/True

何卒宜しくお願い致します。

回答No.1

dogs_cats

>各行のE列～H列の値を変数に入れて、最後に一括で別の列・行にそれぞれの値を表示・書込したいと思ってます

変数に格納しなくても直接、入力したいセルにデータを記入する方法で良いのでは。

一例です。I列以降に同一データを入力する方法です。
（別のBOOｋや別シートにを指定も可能です。）

データをコピーしたいセル番地が記載されていないのでここまでしか記載出来ません。
全データを変数に格納した後にデータを入力したいのでしたら変数を一次配列又は２次配列で宣言して格納する必要があります。

Sub test()
Dim introw As Integer

introw = 2

Do Until Cells(introw, 5).Value = ""
Cells(introw, 10).Value = Cells(introw, 5).Value
Cells(introw, 11).Value = Cells(introw, 6).Value
Cells(introw, 12).Value = Cells(introw, 7).Value
Cells(introw, 13).Value = Cells(introw, 8).Value

introw = introw + 1
Loop

End Sub

お礼

ご回答頂きありがとうございました。

補足

ご回答有難うございます。

>変数に格納しなくても直接、入力したいセルにデータを記入する方法で良いのでは。
上記方法を試しましたが、内部でいろいろやっている為に、処理(表示)として遅かったので、変数に一旦入れて、一括で表示できたらという希望です。

>変数を一次配列又は２次配列で宣言して格納する必要があります。
というところと、一括で配列からセルへの表示仕方をご教示お願いできればと思います。

何度も申し訳ございませんが、宜しくお願い致します。