こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

VBA データの統合機能

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

以前、データの統合機能というのをこちらで教わり、
その構文を使用させて頂いているのですが、
下記の、方法を集計のところの、Rnage("A7")のところに、変数 rnを使用したいのですが、
エラーコード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

投稿日時 - 2018-06-26 17:27:21

QNo.9512433

困ってます

質問者が選んだベストアンサー

> 下記の、方法を集計のところの、Rnage("A7")のところに、変数 rnを使用したいのですが、
> エラーコード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個、……を入れてコードを動かしてみてください。

投稿日時 - 2018-06-26 18:04:44

お礼

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列....X列
10月 11月 12月 1月 2月....9月
と並んでしまいます。
今年は4~12月までの集計になるので、
B列 D列 F列 H列 J列......X列
4月 5月 6月 7月 8月......12月
となる様にするにはどの様に修正すればよろしいでしょうか?
(来年は1月、2月.....最後が12月になります。)

投稿日時 - 2018-06-27 10:47:31

ANo.1

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.2

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

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

投稿日時 - 2018-06-28 09:42:26

お礼

mt2015様

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

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

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

投稿日時 - 2018-06-28 10:10:59

あなたにオススメの質問