下記のようなマクロでシートの統合をしました。但し、同じファイルに別のシートもあり(例えば、リスト・集計など5つほどあります)それも統合されてしまいます。また、本来の統合してほしいシートについても、抽出した時にJ列~P列まででいいのです。しかし、統合シートのA列~G列にJ列~P列まで表示れればよいのですが、H列~P列までが、#N/Aのエラーが記録されます。これを解消したいことと、前述の必要シートのデータのみを統合したい。
教え貰いこと
1.(前提条件)40程度のアンケートのシートを集計する下記のマクロがある。
2.40以外の別シートを統合シートに統合させたくない。
3.統合結果に#N/Aのエラーが記録されるを解消したい。
以上、お教え願いませんでしょうか。よろしくお願い致します。
Sub 統合()
Dim J As Long
Dim r As Long
Dim s As Long
Dim Sh As Worksheet
Dim MaxRow As Long
Dim MaxCol As Long
Dim MyArray As Variant
Dim JoinSh As Worksheet
Set JoinSh = Worksheets("統合") '統合シートを変数に格納
JoinSh.Cells.Delete 'すでに統合シートが存在する場合は一旦セルを削除
s = 1 '最大行を超えた場合次の統合シートを作成するための番号
For i = s + 1 To Worksheets.Count 'シートを統合シートの次~末尾までループ
With Worksheets(i) '各月シート
If J = 2 Then
r = 1 '最初だけ項目も取得
Else
r = 1 '最初以外は2行目から取得
End If
MaxRow = .Cells(Rows.Count, 10).End(xlUp).Row '10列目で最終行を取得
MaxCol = .Cells(1, Columns.Count).End(xlToLeft).Column '1行目で最終列を取得
MyArray = Range(.Cells(r, 10), .Cells(MaxRow, MaxCol)) 'A1~データ末尾まで配列に格納
End With
With JoinSh '統合シート
MaxRow = .Cells(Rows.Count, 10).End(xlUp).Row '統合シートの10列目で最終行取得
If MaxRow + UBound(MyArray) > Rows.Count Then '最大行を超える場合の処理
s = s + 1 '統合シートの番号を加算
Worksheets.Add Before:=Worksheets(s) '新規に統合シートを追加
ActiveSheet.Name = "統合" & s '名前が同じにならないように番号を追加
Set JoinSh = ActiveSheet '統合シートを変数に格納
MaxRow = JoinSh.Cells(Rows.Count, 10).End(xlUp).Row '統合シートの10列目で最終行取得
End If
If .Cells(1, 1) = "" Then
'最初だけ1行目から貼り付け
Range(.Cells(1, 1), .Cells(UBound(MyArray), MaxCol)) = MyArray
Else
'最初以外は最終行の次に貼り付け
Range(.Cells(MaxRow + 1, 1), .Cells(MaxRow + UBound(MyArray), MaxCol)) = MyArray
End If
End With
Next i
End Sub
No.3ベストアンサー
- 回答日時:
こんにちは
コードは読んでいませんけれど・・・
>教え貰いこと
1.質問ではなさそうなので、回答も不要でしょう。
2.除きたいシート数にもよりますが、通常ならそれらのシート名を配列等に入れておいて、
>For i = s + 1 To Worksheets.Count
で、各シートの処理を始める前に、当該シート名とリストを比べて、リストにあれば処理をスキップする(=つぎのシートに処理を飛ばす)ような制御にしておけばよいと思われます。
3.#N/A表示になる原因はいくつか考えられますので、それを確かめる必要があります。
ありそうな原因を挙げてみると、一番は元のセルに数式が入っている場合ですが、ご提示の内容ではValue値を代入しているようですので、こちらは関係なさそうですね。
次にありそうなのは、配列で代入する際にセル範囲と一致していない場合。
配列に代入する際に間違えているのか、セルに移す際に間違えているのか不明ですけれど、配列のUBOUNDを利用することで、後者の間違えは防ぐことが可能です。
Cell().Resize(Ubound(Ary),Ubound(ary,2))
などとして代入するとかでしょうか。
これによって、元のセルの値が#N/Aの場合を除けば、#N/A表示になることはなくなると思います。
※ ご質問には関係ありませんけれど、「統合シート」がオーバーフローした場合の処理があるようですけれど、少々怪しげです。
3、4回オーバーフローさせてみるとおかしなことが起こるかも知れません。(まぁ、そういう意図なのかも知れませんけれど…)
No.2
- 回答日時:
連投すみません、読み直しました。
>統合シートのA列~G列にJ列~P列
理解しましたすみません。
コードを読んでいて もう1,2点違うのではと思う点です。
With JoinSh '統合シート
MaxRow = .Cells(Rows.Count, 10).End(xlUp).Row '統合シートの10列目で最終行取得
10列目は出力されないので、A列に必ず値が出力されるのであれば、これは
With JoinSh '統合シート
MaxRow = .Cells(Rows.Count, 1).End(xlUp).Row '統合シートの1列目で最終行取得
とするべきと思います。
シート分岐ですが、インデックスを使用する場合、シート追加の可能性がコード上にあるので工夫する必要がありそうです。
もっとも、この Worksheets.Add Before:=Worksheets(s) 部分のロジックは意図する機能はしないように思います。(ループ変数sの最大値は確定されている。同様にwith オブジェクトも確定しているので、、、違ったかな?
新規作成時のシートインデックスやシートオブジェクト、変数などを検証して作り直す必要があるように思います。 多分
変数 Jについては不明不問ですね。
No.1
- 回答日時:
こんにちは
>40以外の別シートを統合シートに統合させたくない。
For i = s + 1 To Worksheets.Count 'シートを統合シートの次~末尾までループ
の直下でシートインデックスやシート名でIF分岐する
>統合結果に#N/Aのエラーが記録されるを解消したい。
配列に入れる範囲を目的範囲に設定する。
もしくは逆に配列サイズで出力範囲を設定する
>統合シートのA列~G列にJ列~P列まで表示れればよい
MaxCol = .Cells(1, Columns.Count).End(xlToLeft).Column '1行目で最終列を取得
MyArray = Range(.Cells(r, 10), .Cells(MaxRow, MaxCol))
すべて(表組て変わる)になっているので
MaxCol=16にすれば10~16です
1番右の列がP列なら良いのですが、、、
最終列は不明ですが、欲しい列は1~7 10~16 です
この意味が良く分かりませんが、、
'最初だけ1行目から貼り付け
Range(.Cells(1, 1), .Cells(UBound(MyArray), MaxCol)) = MyArray
'最初以外は最終行の次に貼り付け
Range(.Cells(MaxRow + 1, 1), .Cells(MaxRow + UBound(MyArray), MaxCol)) = MyArray
この出力サイズが配列サイズと違っています。
例えば、A列から出力する場合
Range(.Cells(1, 1), .Cells(UBound(MyArray), MaxCol)) = MyArray
は
.Cells(1, 1).Resize(UBound(MyArray, 1), UBound(MyArray, 2)) = MyArray
こんな感じかな。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) コード名シートA列と集計シートA列のコードが一致したら、コード名シートA5からk12の範囲をコピーし 1 2022/08/29 23:46
- Visual Basic(VBA) 【VBA】特定のワードが入っている行全体を塗りつぶしたい 4 2022/04/20 15:22
- Visual Basic(VBA) 別シートから年齢別の件数をカウントしたい 6 2023/01/23 12:00
- Visual Basic(VBA) VBAコードが作動せず、どこに問題があるのか教えて下さい。 3 2023/06/13 13:20
- Visual Basic(VBA) まとめシートから集計シートへA列のコードが一致したら1行コピーするマクロをネット上で見つけました。こ 1 2022/08/30 14:11
- Visual Basic(VBA) 列と行の名前(重複あり)が交差するセルに、データを入力したい 2 2022/06/25 22:42
- Visual Basic(VBA) VBAで、シート間の転記するコードをFOR~NEXTで教えてください。 9 2023/04/30 20:04
- Visual Basic(VBA) 別シートから年齢別の件数をカウントしたいの続き 5 2023/01/24 00:16
- Visual Basic(VBA) VBAで質問ですが、皆さんはどの様に導き出しているのでしょうか? 6 2022/05/03 21:53
- Visual Basic(VBA) いつもお世話になっております、VBAで教えて頂きたいのですが 2 2022/05/05 22:20
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
別シート参照のセルをシート毎...
-
エクセルでファイル保存時に複...
-
複数シートの特定の位置に連番...
-
EXCEL:同じセルへどんどん足し...
-
エクセルでファイルを開いたと...
-
エクセルで前のシートを連続参...
-
excelでシート毎の最終更新日を...
-
Excel、同じフォルダ内のExcel...
-
エクセルの複数シートの保護を...
-
特定のシートの削除を禁止した...
-
VBAでシートコピー後、シート名...
-
Excelで同じシートのコピーを一...
-
EXCELで1ヶ月分の連続した日付...
-
VBA・オートフィルタで抽出・貼...
-
エクセル:ブックを開くとき、...
-
VBA セルの値と同じ名前のシー...
-
前の(左隣の)シートを連続参...
-
複数シートの固定のセルの値を...
-
エクセルVBAでパスの¥マークに...
-
EXCELで同一フォーマットのシー...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelで同じシートのコピーを一...
-
エクセルの複数シートの保護を...
-
エクセルVBAでパスの¥マークに...
-
前の(左隣の)シートを連続参...
-
Excelで金銭出納帳。繰越残高を...
-
EXCEL:同じセルへどんどん足し...
-
EXCELで1ヶ月分の連続した日付...
-
シートの保護のあとセルの列、...
-
別シート参照のセルをシート毎...
-
エクセルでファイルを開いたと...
-
EXCELで同一フォーマットのシー...
-
エクセルで前のシートを連続参...
-
エクセルで前シートを参照して...
-
VBAでシートコピー後、シート名...
-
Accessのスプレッドシートエク...
-
エクセルのシート名をリスト化...
-
複数シートの特定の位置に連番...
-
Excelのシートを、まとめて表示...
-
エクセル 計算式も入っていない...
-
スプレッドシートの関数VLOOKUP...
おすすめ情報