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

解決済みの質問

Excel VBAで表組みしたらデバック発生

Excel VBAの初心者です。Windows Vistaで
Excel2007を使っています。

表をマクロの実行で作成したいと思っています。

何もないエクセルブックより

「開発」→「マクロの記録」→「相対参照」
→「表の作成」→「記録終了」→「相対参照で記録の解除」
→「エクセルマクロ有効ブックで保存」

ところがこのマクロ記録が入ったブックを再度立ち上げ、
表をオールクリアにし、マクロボタンより表作成を実行
させようとすると、次のエラーメッセージがでました。

『実行時エラー'9'
インデックスが有効範囲にありません。』

デバックからModule1をみると以下の記述となっていました。

Sub 表組み()
'
' 表組み Macro
'

'
ActiveCell.Range("A1:E5").Select
Selection.Copy
Windows("Book1").Activate
ActiveSheet.Paste
ActiveCell.Columns("A:A").EntireColumn.Select
ActiveCell.Rows("1:1").EntireRow.RowHeight = 11.25
ActiveCell.Rows("1:5").EntireRow.Select
Selection.RowHeight = 21.75
ActiveCell.Columns("A:A").EntireColumn.ColumnWidth = 14.88
ActiveCell.Offset(0, 4).Range("A1").Select
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.Offset(1, -3).Range("A1:D4").Select
Selection.NumberFormatLocal = "#,##0_ "
ActiveCell.Select
ActiveCell.FormulaR1C1 = "78000"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "102000"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "9800"
ActiveCell.Offset(-2, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "65000"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "204000"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "500"
ActiveCell.Offset(-2, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "86000"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "151000"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "10200"
ActiveCell.Offset(-2, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
ActiveCell.Offset(0, -3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
ActiveCell.Offset(-4, -2).Range("A1:D1").Select
Selection.AutoFilter
End Sub

上から9行目(?)のWindows("Book1").Activateに
黄色い矢印が示され、また行全体が黄色く四角に
覆われていました。

おそらくこの記述に問題があると思いますが、
どんな記述に変えたらいいのか分かりません。

Excel VBAにお詳しい方ご教示願います。
なお、マクロで作成したい図を添付いたします。
参考にしていただければ幸いです。

投稿日時 - 2011-05-11 09:37:43

QNo.6729354

すぐに回答ほしいです

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

前回はエラー原因だけで、処理はちゃんと見てませんでした^^;

最前面のブックのA1~E5を選択
ActiveCell.Range("A1:E5").Select
選択範囲をコピー
Selection.Copy
別のブック"Book1"を最前面に開く
Windows("Book1").Activate
最前面のブックに貼り付け
ActiveSheet.Paste
以下、表の中の数字・計算式を入力する処理


>補足
1&2.上記の通り、2つのブック(Excelファイル)間で表をコピーして、表の中身だけ書き換えるマクロになっています。どうも期待する処理じゃなさそうですね…
3.オールクリアした=コピー元が無いので、表の数値入力だけ行われているのかな。

記録機能は、あくまで同じ操作を繰り返すだけです。コピーをする場合、当然ながらコピー元のデータが必要です。ファイルを切り替える場合は、ファイル名が固定になってしまうので、改造が必要になります。
…ということで、添付図通りの表をボタンひとつで作りたいなら、「まっさらなシートに表を作る」過程を記録しないとダメです。作ってみました↓
※読み易いよう、記録に手を加えてあります。

Sub Macro1()
 '表の範囲選択。Selectionは選択範囲
 Range("A1:E5").Select
 '罫線を設定
 Selection.Borders(xlDiagonalDown).LineStyle = xlNone
 Selection.Borders(xlDiagonalUp).LineStyle = xlNone
 Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
 Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
 Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
 Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
 Selection.Borders(xlInsideVertical).LineStyle = xlContinuous
 Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous
 '書式設定
 Range("A1:E5").VerticalAlignment = xlCenter '縦位置
 Range("B1:E1").HorizontalAlignment = xlCenter '横位置
 Range("B2:E5").NumberFormatLocal = "#,##0_ " '数値書式
 Range("B1:E4").AutoFilter 'オートフィルタ
 Columns("A:A").ColumnWidth = 14.88 '幅
 Rows("1:5").RowHeight = 21.75 '高さ
 '表の中(Formulaは計算式)
 Range("A1") = "品名"
 Range("A2") = "乳製品"
 Range("A3") = "栄養ドリンク"
 Range("A4") = "清涼飲料"
 Range("A5") = "合計"
 Range("B1") = "営業一課"
 Range("C1") = "営業二課"
 Range("D1") = "営業三課"
 Range("E1") = "合計"
 Range("B5").Formula = "=SUM(B2:B4)"
 Range("C5").Formula = "=SUM(C2:C4)"
 Range("D5").Formula = "=SUM(D2:D4)"
 Range("E5").Formula = "=SUM(E2:E4)"
 Range("E2").Formula = "=SUM(B2:D2)"
 Range("E3").Formula = "=SUM(B3:D3)"
 Range("E4").Formula = "=SUM(B4:D4)"
 '値の入力も必要?
 Range("B2") = 78000
 Range("B3") = 102000
 Range("B4") = 9800
 Range("C2") = 65000
 Range("C3") = 204000
 Range("C4") = 500
 Range("D2") = 86000
 Range("D3") = 151000
 Range("D4") = 10200
End Sub

こんな感じですか?このまま使うワケじゃないでしょうし、補足いただければ直しますよ。

投稿日時 - 2011-05-12 11:15:52

補足

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

>1&2.上記の通り、2つのブック(Excelファイル)間で表をコピーして、
表の中身だけ書き換えるマクロになっています。どうも期待する処理じゃ
なさそうですね…

1&2.ご指摘の通りです。
『10日で覚えるExcelVBA入門教室』という本の最初の方の
例題をやっているため、別のブックを立ち上げて
そこからでも同じマクロを実行する記述ではないようですね。
それはこれから勉強してできるようにします。

3.ご回答のコードをコピペしてご説明の部分を
削除したら、表ができました。また違うシートにも
マクロを実行することができました。ありがとうございます。

最後に一つ質問です。

コードをそのままコピーしたときご説明の日本語部分は
エディターの中で緑色になっていましたが、これは
削除した方がいいですよね?

三度にわたる質問ですが、よろしくお願いします。

投稿日時 - 2011-05-12 13:05:30

ANo.2

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

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

回答(3)

ANo.3

なるほど、勉強目的でしたか。10日で覚える懐かしいですね、僕も立ち読みした記憶がありますw

「'」からその行の最後までは、コメント(緑色)になります。残しておいて構いません。複雑なマクロを作る場合は、コメントがあると何の処理かわかりやすいので、積極的に書き足していくといいですよ。

このさき、記録だけじゃなく、コードを自分で書くようにもなっていくと思います。慣れるまでは悩みも多いと思いますが、VBAはそれほど難しいモノじゃないので、がんばってください!
|_・)ノシ またお会いしましょう

投稿日時 - 2011-05-12 23:40:25

お礼

たびたびご丁寧なアドバイスありがとうございます。
毎日ちょっとずつ勉強して、早く表計算の自動処理が
できるようになりたいです。

投稿日時 - 2011-05-13 07:42:44

ANo.1

> インデックスが有効範囲にありません
データや、シートが見つからない時に発生するエラーです。

> 上から9行目(?)のWindows("Book1").Activateに
ということは、"Book1"が無いんじゃないかな?
セルをコピーし、Book1に切り替えて、貼り付けを行ってます。この処理が不要なら削除、必要ならブック名を直してください。

1操作毎に記録内容を見ることも、F8キーで1行ずつ実行することもできます。各行の意味を調べるのは難しくないと思うので、不要な部分は削除しちゃうといいですよ。

投稿日時 - 2011-05-12 00:59:11

補足

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

ご回答について3点質問事項があります。
まだはじめて4日目なのでap_2さんの
ご回答の内容をよく理解できず恐縮です。

1.「セルをコピーし、Book1に切り替えて、貼り付けを行ってます」
この作業は、単にエクセルを立ち上げて、「表をコピペする」
という意味でしょうか?

2.1で「表をコピペする」した後、シートの内容をオールクリアに
した後、マクロを実行することによって同一の表を作成するには
どうすれば良いでしょうか?

3.Module1のWindows("Book1").Activateを削除した後、
シートの内容をオールクリア、再度マクロを実行すると
フィルタ機能と表の数字の部分だけが残り、品目名や
課名、罫線は作成されませんでした(質問の画像を参照
してください)。これはなぜでしょうか?

投稿日時 - 2011-05-12 08:53:49

あなたにオススメの質問