VBAによる配列足し算

Question

VBAでエクセルAから配列2つを抜き出して、その2つを
足し合わせたものをエクセルBに貼り付けるということをしたいです。

下記のように書きましたが、ダメでした。
(ア)(イ)などを試しました。
これはセルを1つ1つ抜き出してきて計算しなければならないのでしょうか？

Set x = Workbooks(A).Worksheets(sheet).Range("K5:T19")
Set y = Workbooks(A).Worksheets(sheet).Range("K21:T35")

(ア)Set q = x + y
(イ)Set q = x + y

Set b = Workbooks(fileB).Worksheets(sheet1).Range("D4")

q.copy(a)

Answer 1

まず、Set x = の x はRangeなので、配列ではないです。
そして、VBAにはRange、配列に対して、
q = x + y 等という記述で一括処理はできません。

なので、For文等で１つずつ計算するしかないです。

Answer 2

こういうの、あまり詳しくはないのですが配列の要素同士を足しあうベクトル演算とかいうものですかね。

今回やりたいことは、VBAを使わなくても実現できます。
ちょっと普段使わない操作になりますが。

シート操作で実現する

【手順】

①ファイルBのD4:M18を選択する
②式`=[ブックAの名前]シート名!K5:T19 + [ブックAの名前]シート名!K21:T35`を入力
※ブック名やシート名の部分は対象ブック・シート名に置き換えてください
③【重要】式を入力したら、[Ctrl]+[Shift]+[Enter]キーで確定する

ここまでの操作でファイルBのD4:M18にはそれぞれの範囲のセルごとの合計値が表示されると思います。
このような範囲＋範囲のような数式は、結果を出力するセル範囲を指定して計算結果の配列を反映させるため「配列数式」と呼ばれます。
③で特殊な操作をしましたが、これは「配列数式」を反映させる操作です。

これで求めたい計算結果は得られると思いますが、このままだとD4:M18には別ブックのセルを参照する数式のままになってしまいますので、もうひと手間必要です。

④ファイルBのD4:M18を選択して「コピー」
⑤ファイルBのD4:M18に「値の貼り付け」

以上で完了となります。

これをVBAで行うと

上記操作をマクロの記録で記録すればVBA化できます。
以下はそれをもとに少し手を加えたサンプルです。

Sub Sample()
    Dim rngPaste As Range
    Set rngPaste = Workbooks(fileB).Worksheets(sheet1).Range("D4:M18")
    rngPaste.FormulaArray = "=[ブックAの名前]シート名!K5:T19 + [ブックAの名前]シート名!K21:T35"
    
    '式の結果を値として貼り付けなおす
    rngPaste.Value = rngPaste.Value
End Sub

このサンプルは、マクロの記録とは少し違う実装ですがやっていることはほとんど同じですが、

・式の入力部分は範囲選択せずに直接セル範囲指定しています。
・また値の貼り付けの部分はクリップボードを経由しない形に変えています。

の２点だけ修正してあります。
マクロ処理ではもともとの選択範囲やクリップボードの内容は極力変えたくないため、この対応を行いました。

参考になれば幸いです。

Answer 3

単に全体の合算なら、以下で良いのでは。

q = WorksheetFunction.Sum(Range("K5:T19"),Range("K21:T35"))

Comments

[rvsezuki]

全体の合算ではなくセルを1つ1つ足し算していきます

[sazi]

やってみて、意図に合わないってことですか？

[rvsezuki]

そうですね
範囲内の全部の足し算ではなく　
範囲Aと範囲Bを一気に足し合わせることをしたかったので

[sazi]

提示しているのが、
q = 範囲Aの合計 + 範囲Bの合計
なのですけど？
シートでオートSUM(=SUM("A1:A5")みたいな）がありますよね。
あれをVBAで記述したものなんですけど。

[arasi]

意図している動作としては k5+K21 K6+K22 T19+T35 のようなものですか?

[rvsezuki]

意図している動作としては k5+K21 K6+K22 T19+T35 のようなものですか?

→そうです

[rvsezuki]

for文を使わずにセルどうしを足し合わせる方法がないかとおもいまして

[sazi]

２つの表をその表形式は崩さずに合算したものということですか？

[rvsezuki]

そうです　各cellどうしを合算ということです。
レンジ内すべてを足し合わせるということではありません。

[arasi]

ExcelVBAerさんの回答にあるfor文での作成がいいと思います。

[rvsezuki]

加算貼り付けなどは可能でしょうか？

[sazi]

機能的には統合で行える感じですけど、具体的なデータ提示も無いし、ループ処理の方が確実だと思います。

Answer 4

設定先に計算式を埋め込むのはどうですか?

Sheet2.Range("K15:T19").Formula = "='Sheet1'!K5+'Sheet1'!K21"

Answer 5

ExcelVBAerさんの回答通りにfor文が一番いいと思いますが
名前を付けて計算して出力して削除

Sub range_sum()
    '
    ' Macro1 Macro
    '
    Names.Add Name:="test1", RefersTo:="=Sheet1!$K$5:$T$19"
    Names.Add Name:="test2", RefersTo:="=Sheet1!$K$21:$T$35"
    
    Range("K37:T51").FormulaArray = "=test1+test2"
    Worksheets("Sheet1").Range("K37:T51").Copy
    Worksheets("Sheet1").Range("K37:T51").PasteSpecial Paste:=xlPasteValues
    
    Application.Names.Item("test1").Delete
    Application.Names.Item("test2").Delete

'
End Sub