マクロを修正できないものか、統合シートについて

下記のようなマクロでシートの統合をしました。但し、同じファイルに別のシートもあり(例えば、リスト・集計など５つほどあります)それも統合されてしまいます。また、本来の統合してほしいシートについても、抽出した時にJ列～P列まででいいのです。しかし、統合シートのA列～G列にJ列～P列まで表示れればよいのですが、H列～P列までが、#N/Aのエラーが記録されます。これを解消したいことと、前述の必要シートのデータのみを統合したい。
　教え貰いこと
　１．(前提条件)４０程度のアンケートのシートを集計する下記のマクロがある。
　２．４０以外の別シートを統合シートに統合させたくない。
　３．統合結果に#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 ベストアンサー 回答者： fujillin 回答日時： 2021/12/07 15:21

こんにちは

コードは読んでいませんけれど・・・

＞教え貰いこと
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回オーバーフローさせてみるとおかしなことが起こるかも知れません。（まぁ、そういう意図なのかも知れませんけれど…）

この回答へのお礼

ありがとうございます。再度やり直してみます、不都合なところが分かりました。

お礼日時：2021/12/08 14:18

No.2 回答者： Qchan1962 回答日時： 2021/12/07 12:50

連投すみません、読み直しました。

＞統合シートの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については不明不問ですね。

この回答へのお礼

ありがとうございました、やり直してみます。

お礼日時：2021/12/08 14:19

No.1 回答者： Qchan1962 回答日時： 2021/12/07 11:56

こんにちは

＞４０以外の別シートを統合シートに統合させたくない。
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

こんな感じかな。

この回答へのお礼

ありがとうございます。やり直してみます。

お礼日時：2021/12/08 14:19