エクセルVBA 実行時エラー'9'の回避方法について
たいへん困っています。
あるファイルからキーワードを指定してデータを検索し数値項目に-1を掛けてすべて負数にしてから別のファイルにコピー、そのファイルを保存するという処理です。
VBAを作成し実行したところ正常に動作します。しかし、下記の条件下では実行時エラー'9'(インデックスが有効範囲にありません)が発生してしまいます。
(1)VBAを含むエクセルファイル及びデータファイルの保存先をドライブ(S)に置く。
(2)ドライブSは他のPCからアクセスできる同一ネットワークとして定義されている。
(3)ネットワーク下にある他のPCからSドライブにアクセスしVBAを含むエクセルファイルを開いてこの処理を実行する。
分からないのは、インデックス処理に関係しない箇所でエラーが発生していること、同じPC内で実行すると正常動作するのに、上記の条件下ではエラーが発生することです。
作成したVBAは下記のとおりで、Set コピー元 = Workbooks("前回_情報").Worksheets("Sheet1")の箇所でエラーが発生します。エラーを回避する方法を教えてください。
Sub Macro7()
Dim i, j
Dim コピー元 As Worksheet
Dim コピー先 As Worksheet
Dim 検索値 As String
Application.ScreenUpdating = False
Workbooks.Open "S:¥情報出力データ¥前回_情報.xls"
Workbooks.Open "S:¥情報出力データ¥前回_情報コピー.xls"
Worksheets("計算結果").Cells.Clear '結果が格納されるシートを事前にクリア
検索値 = "unit"
Set コピー元 = Workbooks("前回_情報").Worksheets("Sheet1") ← ここでエラー'9'が出る
Set コピー先 = Workbooks("前回_情報コピー").Worksheets("計算結果")
For i = 1 To コピー元.Cells(Rows.Count, 1).End(xlUp).Row
If コピー元.Cells(i, 1).Value = 検索値 Then
For j = 22 To 59
コピー元.Cells(i, j).Value = コピー元.Cells(i, j).Value * -1
Next j
コピー元.Rows(i).Copy コピー先.Cells(Rows.Count, 1).End(xlUp).Offset(1)
End If
Next i
Set コピー元 = Nothing
Set コピー先 = Nothing
Workbooks("前回_情報.xls").Close SaveChanges:=False
Workbooks("前回_情報コピー.xls").Close SaveChanges:=True
End Sub
No.1ベストアンサー
- 回答日時:
>同じPC内で実行すると正常動作するのに、上
これはフォルダーオプションの
「登録されている拡張子は表示しない」のチェックが外れてるとエラーが出ると思いますが、、、
何れにしろ、提示のコードでは
>Set コピー元 = Workbooks("前回_情報").Worksheets("Sheet1")
>Set コピー先 = Workbooks("前回_情報コピー").Worksheets("計算結果")
この2行には拡張子が省略されていますので、拡張子を入れてみたらどうでしょうか。
Set コピー元 = Workbooks("前回_情報.xls").Worksheets("Sheet1")
Set コピー先 = Workbooks("前回_情報コピー.xls").Worksheets("計算結果")
以上です。
myRange様、ありがとうございます。いま、ネットワーク環境下にいないためテストできませんが、あとで確認したいと思います。迂闊でしたがご指摘の通りと思います。このような条件の場合にはエクセルファイルであることを明示しないとだめなのでしょうか。このことと実行時エラー'9'が関係する理屈が分かりませんが。
No.3
- 回答日時:
おそらく #1 さんの回答で合っていると思います。
んで書き方を下のようにするとか。
Dim コピー元Book As Workbook, コピー元Sheet As Worksheet
Dim コピー先Book As Workbook, コピー先Sheet As Worksheet
Set コピー元Book = Workbooks.Open("S:¥情報出力データ¥前回_情報.xls")
Set コピー元Sheet = コピー元Book.Worksheets("Sheet1")
Set コピー先Book = Workbooks.Open("S:¥情報出力データ¥前回_情報コピー.xls")
Set コピー先Sheet = コピー先Book.Worksheets("計算結果")
Worksheet を変数で扱う知識を持っているのなら Workbook も変数で扱うべきでしょう。
Workbooks.Open() メソッドは開いたブックを Workbook オブジェクトとして返してくれるので Workbook 型の変数で参照してしまいましょう。
No.2
- 回答日時:
私なりの書き方に修正してみました。
ダイレクトにシートに入れるよりも、それぞれブックを変数に置き換えてみれば、初歩的なミスは少なくなると思います。Bk1, Bk2 とか使いますが、そうでなければ、以下のようにします。しかし、最後に、Workbooks("前回_情報.xls").Close SaveChanges:=False
Workbooks("前回_情報コピー.xls").Close SaveChanges:=True
としているなら、変数に置き換えたほうが楽ではないでしょうか。
こういう凝った書き方は良いとは思いませんが、あえて近づけてみました。
Dim i As Long, j As Long
Dim コピー元 As Worksheet
Dim コピー先 As Worksheet
Dim 検索値 As String
With Workbooks.Open("S:\情報出力データ\前回_情報.xls")
Set コピー元 = .Worksheets("Sheet1")
End With
With Workbooks.Open("S:\情報出力データ\前回_情報コピー.xls")
Set コピー先 = .Worksheets("計算結果")
End With
Application.ScreenUpdating = False '←下に持ってきた
Worksheets("計算結果").UsedRange.Clear
なお、正しく動くかどうかのチェックはされていません。
Wendy02様、ていねいな回答ありがとうございます。単純な間違いをなくすには変数に置き換えたほうがクレバーなやり方ですね。了解しました。今後とも機会があればよろしくご指導お願いします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) Excelで下記のようにマクロを作ったところ、一回目は実行できたのですが、二回目以降「実行時エラー1 1 2022/03/25 08:08
- Visual Basic(VBA) 【前回の続きです、ご教示ください】VBAの記述方法がわかりません。 2 2022/08/16 16:44
- Visual Basic(VBA) 【ご教示ください】VBAの記述方法がわかりません。 2 2022/08/12 21:28
- Visual Basic(VBA) マクロで最終行を取得したい 4 2023/05/28 12:14
- Visual Basic(VBA) 他のシートからコピーする下記マクロで貼付け位置をWorksheets(1).Range("A3")の 8 2023/01/30 18:48
- Visual Basic(VBA) 【前回の続き続きです、ご教示ください】VBAの記述方法がわかりません。 2 2022/08/24 20:49
- Visual Basic(VBA) VBA 別ブックからの転記の高速化について VBA 別ブックからの転記の高速化についてご教授下さい。 19 2022/07/26 13:07
- Visual Basic(VBA) vbaを早くしたい 5 2022/09/09 10:58
- Excel(エクセル) なぜExit Subがあるのかわかりません 4 2023/02/19 12:34
- Excel(エクセル) マクロで最終行から上に検索を逆にしたい 1 2022/05/17 18:27
このQ&Aを見た人はこんなQ&Aも見ています
-
風水の観点で選ぶ観葉植物とは?置き場所や上げたい運気ごとの注意点を紹介!
観葉植物で運気をアップするコツを、風水デザイン1級建築士の福島昌彦さんに伺った。
-
VBAでcsvファイルもシートもあるのに「インデックスが有効範囲にありません」と表示される2
Visual Basic(VBA)
-
特定のPCだけ動作しないVBAマクロがあります。その理由は?
Visual Basic(VBA)
-
VBAで保存しないで閉じると空のBookが残る
Excel(エクセル)
-
-
4
エクセルのエラーメッセージ「400」って?
Visual Basic(VBA)
-
5
実行時エラー9 インデックスが有効範囲にありません!
Visual Basic(VBA)
-
6
Excel2013で実行時エラー9の対処方法
Perl
-
7
エクセルVBAでセルに入力したパスでブックを開く
Excel(エクセル)
-
8
エクセルVBA で、実行時エラー9 インデックスが有効範囲にありません。の解決方法を教えてください。
Excel(エクセル)
-
9
VBA シートをコピーする際に Copyメソッドは失敗しましたのエラーが出てしまいます
Visual Basic(VBA)
-
10
エクセルエラー13型が一致しませんの直し方教えて下さい。
その他(Microsoft Office)
-
11
VBA 存在しないシートを選択した時にエラーメッセージを表示するには?
Visual Basic(VBA)
-
12
エクセルVBAで開いているファイルをコピーする方法
Visual Basic(VBA)
-
13
エクセルVBA テキストボックスに3桁ごとにコンマ
Visual Basic(VBA)
-
14
VBAでの結合セルのコピー&ペースト
Excel(エクセル)
-
15
実行時エラー9:インデックスが有効範囲にありません」ができてた。調べた
Visual Basic(VBA)
-
16
リンク先のファイルを開かなくても、値が読み込めるようにできますか?(SUMIFSを使ってます)
Excel(エクセル)
-
17
Application.ScreenUpdating = Falseが効きません
Visual Basic(VBA)
-
18
VBA Cのセルが空白でなかったら、Aのセルに順番に数値を入力
Visual Basic(VBA)
-
19
実行時エラー 438になった時の対処法を教えて下さい。
Visual Basic(VBA)
-
20
Worksheets メソッドは失敗しました。のエラー処理のやり方
Visual Basic(VBA)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
DataTableに入っているデータを...
-
Eclipseのコード入力時の、行コ...
-
Access VBAから使用したExcelプ...
-
C++言語で、構造体のコピーは可...
-
Ctrl + Cなど複数の入力キー...
-
マウントしたディスクにcpで、...
-
Activesheet.Pasteで困っています
-
文字列の切り出し
-
他プロジェクトのFormを自プロ...
-
arduino unoからデータを読み出...
-
一行おきに貼り付ける 可能でし...
-
c#で何か作ってみたいのですが
-
jakarta poiを使用し、EXCELの...
-
バッチで当日日付で作成される...
-
ブラウザからコピペすると文字...
-
エクセルのマクロについて教え...
-
Windows上のマウス操作をプログ...
-
エクセルVBA 実行時エラー'...
-
Delphi Form&sourceコピーについて
-
VBScript でサブフォルダも含め...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Access VBAから使用したExcelプ...
-
エクセルのマクロについて教え...
-
DataTableに入っているデータを...
-
Eclipseのコード入力時の、行コ...
-
C++言語で、構造体のコピーは可...
-
プログラムについて。
-
エクセルVBA 実行時エラー'...
-
Ctrl + Cなど複数の入力キー...
-
マウントしたディスクにcpで、...
-
Activesheet.Pasteで困っています
-
一行おきに貼り付ける 可能でし...
-
Listviewに表示された文字のコ...
-
文字列の切り出し
-
arduino unoからデータを読み出...
-
シートに張り付けたボタンがシ...
-
ブラウザからコピペすると文字...
-
ROBOCOPYをスペース付きのフォ...
-
バッチで当日日付で作成される...
-
jakarta poiを使用し、EXCELの...
-
ACCESS VBAでExcelを開き行をコ...
おすすめ情報