エクセル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も見ています
-
性格の違いは生まれた順番で決まる?長男長女・中間子・末っ子・一人っ子の性格の傾向
同じ環境で生まれ育っても、生まれ順で性格は違うものなのだろうか。家庭教育研究家の田宮由美さんに教えてもらった。
-
VBAでcsvファイルもシートもあるのに「インデックスが有効範囲にありません」と表示される2
Visual Basic(VBA)
-
特定のPCだけ動作しないVBAマクロがあります。その理由は?
Visual Basic(VBA)
-
VBA シートをコピーする際に Copyメソッドは失敗しましたのエラーが出てしまいます
Visual Basic(VBA)
-
-
4
エクセルのエラーメッセージ「400」って?
Visual Basic(VBA)
-
5
VBAでの結合セルのコピー&ペースト
Excel(エクセル)
-
6
実行時エラー9 インデックスが有効範囲にありません!
Visual Basic(VBA)
-
7
Excel2013で実行時エラー9の対処方法
Perl
-
8
エクセルVBA で、実行時エラー9 インデックスが有効範囲にありません。の解決方法を教えてください。
Excel(エクセル)
-
9
VBAで保存しないで閉じると空のBookが残る
Excel(エクセル)
-
10
エクセルVBAでセルに入力したパスでブックを開く
Excel(エクセル)
-
11
エクセル:マクロ「Application.CutCopyMode = False」って?
Excel(エクセル)
-
12
VBA 存在しないシートを選択した時にエラーメッセージを表示するには?
Visual Basic(VBA)
-
13
エクセルVBAで開いているファイルをコピーする方法
Visual Basic(VBA)
-
14
Exel VBA 別ブックから該当データを検索し、必要なデータを取得する方法について
Visual Basic(VBA)
-
15
リンク先のファイルを開かなくても、値が読み込めるようにできますか?(SUMIFSを使ってます)
Excel(エクセル)
-
16
VBA Cのセルが空白でなかったら、Aのセルに順番に数値を入力
Visual Basic(VBA)
-
17
エクセルエラー13型が一致しませんの直し方教えて下さい。
その他(Microsoft Office)
-
18
繰り返し1行~28行までを順順にコピーする方法
Visual Basic(VBA)
-
19
DATE型変数を初期化する方法
Visual Basic(VBA)
-
20
WorkBooksをオープンさせずにシートにコピーしたい【EXCEL VBA】
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
プログラムについて。
-
DataTableに入っているデータを...
-
Eclipseのコード入力時の、行コ...
-
エクセルVBA 実行時エラー'...
-
Subversionで作業一段落の作業...
-
Access VBAから使用したExcelプ...
-
Listviewに表示された文字のコ...
-
マウントしたディスクにcpで、...
-
VBA 最終行・最終列コピー範囲...
-
プログラム終了時にエラーが発...
-
EPROM Writer の使い方がわから...
-
.BATでクリップボードに複数の...
-
エクセルのマクロについて教え...
-
Ctrl + Cなど複数の入力キー...
-
C++言語で、構造体のコピーは可...
-
Excel VBA キーワードから列を...
-
Windows上のマウス操作をプログ...
-
arduino unoからデータを読み出...
-
文字列の切り出し
-
シートに張り付けたボタンがシ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Access VBAから使用したExcelプ...
-
C++言語で、構造体のコピーは可...
-
DataTableに入っているデータを...
-
マウントしたディスクにcpで、...
-
エクセルVBA 実行時エラー'...
-
gitってなんですか?
-
Listviewに表示された文字のコ...
-
arduino unoからデータを読み出...
-
一行おきに貼り付ける 可能でし...
-
Ctrl + Cなど複数の入力キー...
-
Activesheet.Pasteで困っています
-
Eclipseのコード入力時の、行コ...
-
ブラウザからコピペすると文字...
-
シートに張り付けたボタンがシ...
-
ROBOCOPYをスペース付きのフォ...
-
文字列の切り出し
-
Eclipseでコピーするとき行数な...
-
【UWSC】WEBページ内コピーした...
-
jakarta poiを使用し、EXCELの...
-
バッチで当日日付で作成される...
おすすめ情報