• ベストアンサー
  • 困ってます

VBA データの統合機能

  • 質問No.9512433
  • 閲覧数164
  • ありがとう数2
  • 回答数2

お礼率 95% (85/89)

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

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

  • 回答No.1
  • ベストアンサー

ベストアンサー率 49% (256/521)

> 下記の、方法を集計のところの、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個、……を入れてコードを動かしてみてください。
お礼コメント
6338-tm

お礼率 95% (85/89)

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

その他の回答 (全1件)

  • 回答No.2

ベストアンサー率 49% (256/521)

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

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

お礼率 95% (85/89)

mt2015様

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

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

想定がいつも的確ですごいです。
いつもいつも本当にありがとうございます!!
投稿日時:2018/06/28 10:10
関連するQ&A

その他の関連するQ&Aをキーワードで探す

ページ先頭へ