Dim zz配列 As Variant
'アクティブセルのある行のデータ範囲を配列に代入
zz配列 = Intersect(Range("A1").CurrentRegion, ActiveCell.EntireRow).Formula
'配列データの加工
zz配列(1, 3) = #5/15/2020#
zz配列(1, 4) = 600
zz配列(1, 6) = #5/22/2020#
zz配列(1, 7) = 900
'配列データの出力
Intersect(Range("A1").CurrentRegion, ActiveCell.EntireRow) = zz配列
'配列データの初期化
Erase zz配列
配列データをセルに出力する場合、そのセル範囲は連続している必要があります
連続していない場合は、その途切れた範囲ごとにデータの1個目から入力されていきます
例えば、以下のような表があるとします
ピンクに色付けされた所に数値を入力する処理を作成します
上記にもあるように連続していないセル範囲で飛ばして配列データを出力することはできません
配列データはピンク以外の列のデータも含んでおく必要があります
この表に出力したい配列数が5個のデータを入力したいセルだけを選択状態にして出力するとします
なお、配列データは「100,200,300,400,1000」だと想定します
連続していないセル範囲に配列データを出力すると、画像の様に選択範囲のそれぞれからデータが入力されていくため、思い通りの数値が指定セルに入力されていないのが分かります
AB列は要素0（100）と1（200）が代入され、DE列も要素0と1が代入され、G列は要素0が代入されている状態になっています
本来入力したい形は、客先B_1には「300」、客先B_2には「400」、合計には「1000」を入力したかったわけです
それが実行できていません
また、客先ごとに備考列が必要なため削除して連続セルにすることはできません
この例では客先数が2つですが、これが10や50となってくるとすさまじく面倒な入力になります
これは、どうにもなりませんでした調べても一切出てこない内容で、なかなか悩みましたが、とりあえず解決方法として考えたのが、「入力しないセルも配列データに含める」ということでした
追記：調べても出てこない、というよりそもそもセル範囲を先に取得しておけば事足りるため、その程度のことがあえて解説されていなかったのかもしれません
例コードの前提と動き
コード解説の前にまずは前提条件の説明と動きを確認します
今回の前提として、A列は年月の日付データが入っています
この表は、各月ごとを1行として、客先数分の列データが追加されていくような表データです
B列はその月の合計金額が表示されます
今回は数が少ないので簡単にSUM関数で入力金額を合計しているだけです
これが数十件以上あるような場合はSUMIF関数にする形になるでしょうね
C・D列が客先1つ目の日付と金額データです
E列はなんらかの備考情報です
この3列が客先数分作成される表となります
今回は2つしか作成していません
この画像で、実際の動きを確認してください
今回は5月分のデータを入力します
配列が作成され、それを加工して出力するところまでの動きをなんとなく追ってみてください
コード解説
Dim zz配列 As Variant
最初に配列データを代入する変数宣言を行います
今回はセル範囲を先に取得するので、静的配列は使用できません
動的配列を指定する必要があります、ですがセル範囲代入の場合は自動的に動的配列として認識されるので変数名だけで問題ありません
また、セル自体がどんなデータも受付するのでそれを取得するためにはValiant型で宣言しておきます
zz配列 = Intersect(Range("A1").CurrentRegion, ActiveCell.EntireRow).Formula
宣言した変数にセル範囲のデータを代入します
この時にセルオブジェクトの代入をしてしまわないように、Setステートメントは使用しないように注意してください
この代入は値の代入になるので、省略またはLetステートメントを使用する必要があります
代入元のセル範囲の指定ですが、まず表範囲全体を取得するCurrentRegionプロパティを使用して、さらにIntersectメソッドを使用してその範囲とアクティブセルのある行全体の重複する部分を取得します
つまり、表内のアクティブセル行のデータ範囲を取得します
そして、今回ここが一番重要な部分といってもいいかもしれませんが、取得する際のセル範囲のプロパティには注意が必要です
今回はFormulaプロパティを使用しています
これはセルデータのうちの数式を取得するプロパティです
今回の表データにはSUM関数が入力されていますので、通常のValueプロパティで取得を行うと、SUM関数の結果の数値が取得されてしまい
後で出力する際に、値に振り替えられ関数がなくなってしまいます
そのため、取得するセル範囲に数式が含まれている場合はFormulaプロパティを使用するようにしてください
なお、このプロパティは数式ではない場合は値を取得するので基本的にはどのデータを取得する際も問題はありません
ただ問題があるとすれば空白セルが「””」と取得される点と日付データは実データが文字列で取得されている点です
Valueプロパティで取得した場合、空白はEmpty値という特別な値になりますが、こちらでは「””」という文字列として取得されます
この辺りを処理上、判定が必要な場合は注意してください
ただ、便宜上取得して、それを出力するだけならセルが自動的に判定してくれるので問題はありません
配列データに取得させる場合のセルのプロパティには要注意です
zz配列(1, 3) = #5/15/2020# zz配列(1, 4) = 600 zz配列(1, 6) = #5/22/2020# zz配列(1, 7) = 900
ここは、取得した配列データの加工の部分です
今回は決め打ちの値を代入していますが、実際の処理時はここで色々な計算を行ったり、取得したデータを代入させます
この時、配列はメモリ上に展開されますので画像の様にローカルウィンドウを表示して、その配列データを展開しておくと分かりやすいです
またセル範囲の配列データは2次元配列になります
2次元目が列数になるので、今回は3・4・6・7行のデータを入力するので、その要素数の配列に代入しておきます
Intersect(Range("A1").CurrentRegion, ActiveCell.EntireRow) = zz配列
加工した配列データの出力です
出力は基本的に取得した範囲をそのまま使用するので、取得時の左辺と右辺を入れ替えるだけで処理可能です
そのため、おおよその処理ではセル範囲も変数に代入しておくと処理が実行しやすくなります
Erase zz配列
最後に配列データを初期化します
配列データはデータが大量に入っているため、1つ1つに初期値の代入を行ったりはしません
Eraseステートメントを使用して初期化します
なおこのステートメントでは、値だけでなくオブジェクトを参照していたとしても問題ありませんので、配列データの初期化にはこのステートメントを使用します