VBA データの統合機能

質問

Winは7、Excelは2013を使用しています。

以前、データの統合機能というのをこちらで教わり、
その構文を使用させて頂いているのですが、
下記の、方法を集計のところの、Rnage("A7")のところに、変数 ｒｎを使用したいのですが、
エラーコード438が出てしまいます。
あと、年間集計のところにデータを書きだすところで、画像の青枠の様に1列おきに書き出したいのですが、可能でしょうか？
以上、2点ご教示頂けますようお願い致します。

Sub test_データの統合機能()

Dim sArray() As String
ReDim sArray(Sheets.Count - 2) As String

Sheets("年間集計").Select
Cells.ClearContents

'--------------------------------------------
'科目年間集計
'--------------------------------------------

For i = 2 To Sheets.Count
sShtName = Sheets(i).Name
sShtAddress = Sheets(i).Range("M2").CurrentRegion.Address(, , xlR1C1)
sArray(i - 2) = sShtName & "!" & sShtAddress
Next i
Sheets(1).Range("A1").Consolidate Sources:=sArray, _
Function:=xlSum, _
TopRow:=True, _
LeftColumn:=True, _
CreateLinks:=False
'--------------------------------------------
'合計
'--------------------------------------------

Dim maxCol As Long
Dim maxRow As Long
Dim c As Integer
Dim r As Integer

maxCol = Range("A2").End(xlToRight).Column
maxRow = Range("A2").End(xlDown).Row

Cells(1, maxCol + 1) = "合計回数"
Cells(1, maxCol + 2) = "合計時間"

For r = 2 To maxRow
For c = 2 To maxCol Step 2

Cells(r, maxCol + 1) = Cells(r, maxCol + 1) + Cells(r, c)
Cells(r, maxCol + 2) = Cells(r, maxCol + 2) + Cells(r, c + 1)

Next c
Next r

'--------------------------------------------
'方法を年間集計
'--------------------------------------------
Dim rn As Range

Set rn = Cells(maxRow + 2, 1)

For i = 2 To Sheets.Count
sShtName = Sheets(i).Name
sShtAddress = Sheets(i).Range("Q2").CurrentRegion.Address(, , xlR1C1)
sArray(i - 2) = sShtName & "!" & sShtAddress
Next i

Sheets(1).Range("A7").Consolidate Sources:=sArray, _
Function:=xlSum, _
TopRow:=True, _
LeftColumn:=True, _
CreateLinks:=False
'--------------------------------------------
'このあとに合計を計算する
'--------------------------------------------
'（略）
End Sub

回答No.1

mt2015 · ベストアンサー

> 下記の、方法を集計のところの、Rnage("A7")のところに、変数 ｒｎを使用したいのですが、
> エラーコード438が出てしまいます。

エラーが出るコードを載せてもらった方が話が早いのですが、もしかして以下の様なコードでしょうか？
Set rn = Range("A7")
Sheets(1).rn.Consolidate Sources:=sArray,(以下省略）

だとしたら、この様に変えてください
Set rn = Sheets(1).Range("A7")
rn.Consolidate Sources:=sArray,(以下省略）

--
> あと、年間集計のところにデータを書きだすところで、画像の青枠の様に1列おきに書き出したいのですが、可能でしょうか？

基本的には出来ません。
ただ、裏技的な方法として各集計元シートのデータに空白をシート毎に違った個数入れておくと似たような事が出来ます。
貴方のコードで言うなら、Sheet2のS1に半角スペース1個、Sheet3のS1に半角スペース2個、Sheet4のS1に半角スペース3個、……を入れてコードを動かしてみてください。

お礼

mt2015様

いつもご回答いただきありがとうございます。

エラーになる構文を載せていなくて申し訳ありません。
mt2015さんが想定して下さった構文でエラーになっていました。

教えて下さったのを参考に書き換えさせて頂き、S1にスペースを入れたら、
1列おきに書き出しになりました！
ありがとうございます。

Sub test_データの統合機能()

Dim sArray() As String
ReDim sArray(Sheets.Count - 2) As String

Sheets("年間集計").Select
Cells.ClearContents

'--------------------------------------------
'科目年間集計
'--------------------------------------------

For i = 2 To Sheets.Count
sShtName = Sheets(i).Name
sShtAddress = Sheets(i).Range("M2").CurrentRegion.Address(, , xlR1C1)
sArray(i - 2) = sShtName & "!" & sShtAddress
Next i
Sheets(1).Range("A1").Consolidate Sources:=sArray, _
Function:=xlSum, _
TopRow:=True, _
LeftColumn:=True, _
CreateLinks:=False

'--------------------------------------------
'合計
'--------------------------------------------

Dim maxCol As Long
Dim maxRow As Long
Dim c As Integer
Dim r As Integer

maxCol = Range("A2").End(xlToRight).Column
maxRow = Range("A2").End(xlDown).Row

Cells(1, maxCol + 1) = "合計回数"
Cells(1, maxCol + 2) = "合計時間"

For r = 2 To maxRow
For c = 2 To maxCol Step 2

Cells(r, maxCol + 1) = Cells(r, maxCol + 1) + Cells(r, c)
Cells(r, maxCol + 2) = Cells(r, maxCol + 2) + Cells(r, c + 1)

Next c
Next r

'--------------------------------------------
'方法を年間集計
'--------------------------------------------
Dim rn As Range

'下記を修正
Set rn = Sheets(1).Cells(maxRow + 2, 1)

For i = 2 To Sheets.Count
sShtName = Sheets(i).Name
sShtAddress = Sheets(i).Range("Q2").CurrentRegion.Address(, , xlR1C1)
sArray(i - 2) = sShtName & "!" & sShtAddress
Next i

’下記を修正
rn.Consolidate Sources:=sArray, _
Function:=xlSum, _
TopRow:=True, _
LeftColumn:=True, _
CreateLinks:=False

'--------------------------------------------
'このあとに合計を計算する
'--------------------------------------------

'（略）

End Sub

それで、12ヶ月シートを作って、各月S1にスペースも1ずつ加算して仮のデータを作って、12ヶ月分を処理してみたら、年間集計シートの書き出しのところで、
左から
.B列　D列　　F列　H列　J列....Ｘ列
10月　11月　12月　1月　2月....9月
と並んでしまいます。
今年は4～12月までの集計になるので、
B列　D列　F列　H列　J列......Ｘ列
4月　5月　6月　7月　8月......12月
となる様にするにはどの様に修正すればよろしいでしょうか？
(来年は1月、2月.....最後が12月になります。）

回答No.2

mt2015

> それで、12ヶ月シートを作って、各月S1にスペースも1ずつ加算して仮のデータを作って、12ヶ月分を処理してみたら、年間集計シートの書き出しのところで、
> 左から
> .B列　D列　　F列　H列　J列....Ｘ列
> 10月　11月　12月　1月　2月....9月
> と並んでしまいます。

各月のシート名が「1月」「2月」…「11月」「12月」の様になっているのでしょうか。
でしたらシート名の月の数字を「01月」「02月」…「11月」「12月と二桁にしてみてください。
恐らくこれで統合元の順番が希望通りになります。

お礼

mt2015様

ご回答ありがとうございます。

シート名、想定された通りで、
01月、02月...にしたら順番通りになりました！

想定がいつも的確ですごいです。
いつもいつも本当にありがとうございます！！