Excel VBAに関してのトラブルの相談
Q&A
解決したいこと
Excelのマクロに関して、
Windowsで動くマクロが、Macで行おうと思った際に実行するとエクセルが落ちてしまう（作業内容を回復し、再起動しますのエラーメッセージが出る）のですがどのような理由でしょうか？
発生している問題・エラー
作業内容を回復し、再起動します
というエラーメッセージが出てしまいます
該当するソースコード
Excel VBA
Sub AllDataAnalysis()
Dim cnt As Long
Dim i As Long
Dim TopRow As Long
Dim Top As String
Dim BottomRow As Long
Dim Number As Long
Dim p As Long
Dim q As Long
cnt = Sheets.Count
'変数・cntには、シートの総数が代入されている
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Data List"
Cells(1, 1) = "WellName"
Cells(1, 2) = "No. of CH"
Cells(1, 3) = "総細胞数"
Cells(1, 4) = "陽性細胞数"
Cells(1, 5) = "陽性率"
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Extract"
Worksheets(1).Columns(7).Copy
Worksheets("Extract").Columns(1).PasteSpecial
Application.CutCopyMode = False
Worksheets(1).Columns("O:W").Copy
Worksheets("Extract").Columns(2).PasteSpecial
Application.CutCopyMode = False
TopRow = 2
p = 0
q = 0
While Cells(TopRow, 1).Row < Cells(1, 1).End(xlDown).Row
i = 1
Top = Cells(TopRow, 1)
While Cells(TopRow + i, 1) = Top
i = i + 1
Wend
BottomRow = TopRow + i - 1
Number = BottomRow - TopRow + 1
q = q + 1
Range(Rows(TopRow), Rows(BottomRow)).Copy
Worksheets.Add after:=Worksheets(Worksheets.Count)
Cells(2, 1).PasteSpecial
Application.CutCopyMode = False
Worksheets(1).Cells(1, 1).Copy
Worksheets(Worksheets.Count).Cells(1, 1).PasteSpecial
Worksheets(1).Activate
Range(Cells(1, 15), Cells(1, 23)).Copy
Worksheets(Worksheets.Count).Cells(1, 2).PasteSpecial
Worksheets(Worksheets.Count).Name = "Data" & q
'ここまでで新しいシートにデータを1つ切り出し
'ここからはCH2のデータの解析を行う
p = p + 1
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Result " & p
ActiveSheet.Previous.Columns("A:B").Copy
Worksheets(Worksheets.Count).Columns("A:B").PasteSpecial
Application.CutCopyMode = False
ActiveSheet.Previous.Columns(5).Copy
Worksheets(Worksheets.Count).Columns(3).PasteSpecial
Application.CutCopyMode = False
Range("A3").Sort _
Key1:=Range("C1"), Order1:=xlAscending, _
Orientation:=xlSortColumns, Header:=xlYes
Worksheets(Worksheets.Count).Activate
Range("C1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
'ここまででグラフの作成終わり
Cells(3, 7) = "総細胞数"
Cells(4, 7) = "陽性細胞数"
Cells(5, 7) = "陽性基準値"
Cells(6, 7) = "陽性率"
Cells(5, 8) = "1000000"
Cells(3, 8).Value = Cells((Rows.Count) - 1, 3).End(xlUp).Row - 1
Worksheets(Worksheets.Count).Cells(3, 8).Copy
Worksheets("Data List").Cells(p + 1, 3).PasteSpecial
Application.CutCopyMode = False
Worksheets(Worksheets.Count).Activate
Cells(4, 8) = WorksheetFunction.CountIf(Columns(3), ">" & Cells(5, 8))
Worksheets(Worksheets.Count).Cells(4, 8).Copy
Worksheets("Data List").Cells(p + 1, 4).PasteSpecial
Application.CutCopyMode = False
Worksheets(Worksheets.Count).Activate
Cells(6, 8) = Cells(4, 8) / Cells(3, 8) * 100
Worksheets(Worksheets.Count).Cells(6, 8).Copy
Worksheets("Data List").Cells(p + 1, 5).PasteSpecial
Application.CutCopyMode = False
Worksheets(Worksheets.Count).Activate
Worksheets("Extract").Cells(TopRow, 1).Copy
Worksheets("Data List").Cells(p + 1, 1).PasteSpecial
Application.CutCopyMode = False
Worksheets("Extract").Cells(1, 5).Copy
Worksheets("Data List").Cells(p + 1, 2).PasteSpecial
Application.CutCopyMode = False
Worksheets(Worksheets.Count).Activate
' ここまででCH2の計算と出力が終了
'ここからはCH3のデータの解析を行う
p = p + 1
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Result " & p
Worksheets("Data" & q).Columns("A:B").Copy
Worksheets(Worksheets.Count).Columns("A:B").PasteSpecial
Application.CutCopyMode = False
Worksheets("Data" & q).Columns(6).Copy
Worksheets(Worksheets.Count).Columns(3).PasteSpecial
Application.CutCopyMode = False
Range("A3").Sort _
Key1:=Range("C1"), Order1:=xlAscending, _
Orientation:=xlSortColumns, Header:=xlYes
Worksheets(Worksheets.Count).Activate
Range("C1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
'ここまででグラフの作成終わり
Worksheets(Worksheets.Count).Activate
Cells(3, 7) = "総細胞数"
Cells(4, 7) = "陽性細胞数"
Cells(5, 7) = "陽性基準値"
Cells(6, 7) = "陽性率"
Cells(5, 8) = "1000000"
Cells(3, 8).Value = Cells((Rows.Count) - 1, 3).End(xlUp).Row - 1
Worksheets(Worksheets.Count).Cells(3, 8).Copy
Worksheets("Data List").Cells(p + 1, 3).PasteSpecial
Application.CutCopyMode = False
Worksheets(Worksheets.Count).Activate
Cells(4, 8) = WorksheetFunction.CountIf(Columns(3), ">" & Cells(5, 8))
Worksheets(Worksheets.Count).Cells(4, 8).Copy
Worksheets("Data List").Cells(p + 1, 4).PasteSpecial
Application.CutCopyMode = False
Worksheets(Worksheets.Count).Activate
Cells(6, 8) = Cells(4, 8) / Cells(3, 8) * 100
Worksheets(Worksheets.Count).Cells(6, 8).Copy
Worksheets("Data List").Cells(p + 1, 5).PasteSpecial
Application.CutCopyMode = False
Worksheets(Worksheets.Count).Activate
Worksheets("Extract").Cells(1, 6).Copy
Worksheets("Data List").Cells(p + 1, 2).PasteSpecial
Application.CutCopyMode = False
Worksheets(Worksheets.Count).Activate
' ここまででC3の計算と出力が終了
'ここからはCH4のデータの解析を行う
p = p + 1
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Result " & p
Worksheets("Data" & q).Columns("A:B").Copy
Worksheets(Worksheets.Count).Columns("A:B").PasteSpecial
Application.CutCopyMode = False
Worksheets("Data" & q).Columns(7).Copy
Worksheets(Worksheets.Count).Columns(3).PasteSpecial
Application.CutCopyMode = False
Range("A3").Sort _
Key1:=Range("C1"), Order1:=xlAscending, _
Orientation:=xlSortColumns, Header:=xlYes
Worksheets(Worksheets.Count).Activate
Range("C1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
'ここまででグラフの作成終わり
Worksheets(Worksheets.Count).Activate
Cells(3, 7) = "総細胞数"
Cells(4, 7) = "陽性細胞数"
Cells(5, 7) = "陽性基準値"
Cells(6, 7) = "陽性率"
Cells(5, 8) = "1000000"
Cells(3, 8).Value = Cells((Rows.Count) - 1, 3).End(xlUp).Row - 1
Worksheets(Worksheets.Count).Cells(3, 8).Copy
Worksheets("Data List").Cells(p + 1, 3).PasteSpecial
Application.CutCopyMode = False
Worksheets(Worksheets.Count).Activate
Cells(4, 8) = WorksheetFunction.CountIf(Columns(3), ">" & Cells(5, 8))
Worksheets(Worksheets.Count).Cells(4, 8).Copy
Worksheets("Data List").Cells(p + 1, 4).PasteSpecial
Application.CutCopyMode = False
Worksheets(Worksheets.Count).Activate
Cells(6, 8) = Cells(4, 8) / Cells(3, 8) * 100
Worksheets(Worksheets.Count).Cells(6, 8).Copy
Worksheets("Data List").Cells(p + 1, 5).PasteSpecial
Application.CutCopyMode = False
Worksheets(Worksheets.Count).Activate
Worksheets("Extract").Cells(1, 7).Copy
Worksheets("Data List").Cells(p + 1, 2).PasteSpecial
Application.CutCopyMode = False
Worksheets(Worksheets.Count).Activate
' ここまででC4の計算と出力が終了
Sheets("Extract").Activate
TopRow = TopRow + i
Wend
End Sub
自分で試したこと
Macのエクセルで他のマクロを動かす分には問題ありません。
マクロも有効になっています。
アンインストール、再インストール
プログラムの更新などは一通り試しました
0 likes
