Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(指定なし : 指定なし)
インデックスが有効範囲にありません(家と職場で異なる)
投稿日時: 24/02/16 17:38:19
投稿者: まるよこ

家のパソコンでは実行ができて、職場で使おうと思ったのですが「インデックスが有効範囲にありません」となりました。なにか解決策がありますでしょうか。
いろんな本をヒントに書いたコードです。
操作対象ブックフォルダの中にあるブックをひらき、CH-731というシートがあります。
CH731の1行目は見出し
2行目移行に社員人数分のデータが入っています。
これを見出しを残して個人別に保存したいです。
元データにVBAを書くのではなく、元データを傷つけずに行いたいです。
また他の所属のブックも同じことをしたいため、このようにしました。
 
もう1点
家でこのまま全員分がうまく作成できました。
しかしできあがった個人のファイルはオートフィルターとなっており、他の職員が非表示になっているだけで、個人別保存は結果としてうまくいきませんでした。currentregionを使うのがいいとは思っているのですが、なにかうまくいく方法はありますでしょうか。
 
Option Explicit
Sub 抽出()
Application.DisplayAlerts = False
Dim bookname As String
bookname = Dir(ThisWorkbook.Path & "\操作対象ブックフォルダ\*")
Workbooks.Open ThisWorkbook.Path & "\操作対象ブックフォルダ\" & bookname
 
dim i
Sheets("CH-731").Activate
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Workbooks("実行用ブック.xlsx").Sheets("氏名").Cells(i - 1, 1) = Sheets("CH-731").Cells(i, 4).Value
 
Next
 
Range("A1").AutoFilter
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row - 1
 
Sheets("CH-731").Activate
Range("A1").AutoFilter
Range("a1").AutoFilter 4, Workbooks("実行用ブック").Sheets("氏名").Cells(i, 1).Value
Sheets("CH-731").Activate
Sheets("CH-731").Copy
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs _
ThisWorkbook.Path & "\" & Workbooks("実行用ブック").Sheets("氏名").Cells(i, 1) & "CH-731.xlsx"
Application.DisplayAlerts = True
ActiveWorkbook.Close
 
Next
Sheets("CH-731").Activate
Range("A1").AutoFilter
 
End Sub

回答
投稿日時: 24/02/16 20:12:37
投稿者: simple

どの行でそのエラーが発生するのか記入ください。
 
また、OSとExcelのバージョンは明記するようにしてください。

回答
投稿日時: 24/02/17 21:28:44
投稿者: WinArrow

引用:

家のパソコンでは実行ができて、職場で使おうと思ったのですが「インデックスが有効範囲にありません」となりました。

 
家のパソコンと職場のパソコンの違い
ではないと思いますよ!!!!
 
simpleさんレスにあるように
エラーになった場合、「何処で」という情報は、原因を推定→特定するための情報です。
 

回答
投稿日時: 24/02/18 05:02:04
投稿者: hatena
投稿者のウェブサイトに移動

まるよこ さんの引用:
家のパソコンでは実行ができて、職場で使おうと思ったのですが「インデックスが有効範囲にありません」となりました。なにか解決策がありますでしょうか。

 
Range("a1").AutoFilter 4, Workbooks("実行用ブック").Sheets("氏名").Cells(i, 1).Value

 
上記のコードの"実行用ブック"を"実行用ブック.xlsx"に変更したらどうだろうか。
下記も同様に。
 
Range("a1").AutoFilter 4, Workbooks("実行用ブック").Sheets("氏名"[quote="まるよこ"]家のパソコンでは実行ができて、職場で使おうと思ったのですが「インデックスが有効範囲にありません」となりました。なにか解決策がありますでしょうか。[/quote]).Cells(i, 1).Value

 
まるよこ さんの引用:
しかしできあがった個人のファイルはオートフィルターとなっており、他の職員が非表示になっているだけで、個人別保存は結果としてうまくいきませんでした。

 
個人別にブックを作成したいのなら、下記のような手順になります。
 
特定の個人名でオートフィルターをかける
新規ブックを作成
新規ブックにフィルターをかけたデータをコピー
新規ブックを個人名を含むファイル名で保存する。
新規ブックを閉じる。
オートフィルターを解除
 
-----------------------------------------------------
以下、蛇足
 
アクティブなブック、シートを対象にした(ブックやシートを指定しない)コードは、
何を対象に処理しているか判別しづらいので、コードを解読しづらいし、バグの原因になりかねません。
特に今回のように複数のブックを扱う場合はそうなりやすいです。
 
処理対象のブックやシートを指定したコードに修正することをお勧めします。
 
 
ブック・シートの指定|VBA入門
https://excel-ubara.com/excelvba1/EXCELVBA337.html

投稿日時: 24/02/18 10:04:07
投稿者: まるよこ

みなさま、コメントありがとうございます。
また、投稿する際にいろいろと至らず申し訳ありません。
作りたいものができたため、本を一巡してまだ理解不足なままいきおいで作り、安易に質問してしまいました。まずもう少し自分で勉強してみたいと思います。
 
 
職場 
OS:Windows 10 Pro
2019
 
家はまた調べます。
 
エラーとなるところ
Workbooks("実行用ブック.xlsx").Sheets("氏名").Cells(i - 1, 1) = Sheets("CH-731").Cells(i, 4).Value
 
 
hatena様がコメントしていただいたように直してみましたが、結果はかわりませんでした。
ブック・シートの指定の参考URLもありがとうございます。

回答
投稿日時: 24/02/18 11:29:29
投稿者: WinArrow

まるよこ さんの引用:

 
エラーとなるところ
Workbooks("実行用ブック.xlsx").Sheets("氏名").Cells(i - 1, 1) = Sheets("CH-731").Cells(i, 4).Value
 

 
掲示のコードの中では
"実行用ブック.xlsx"を開いていませんよね・・・
家での操作時は、手操作で開いているのではないでしょうか?
 
 
アドバイスです。
インデントを付けると、読みやすくなります。(可読性向上)
以下、インデントを付けただけ参考コードを紹介します。
コードの中身はそのままです。
Option Explicit

Sub 抽出()
    Application.DisplayAlerts = False
    
Dim bookname As String
    
    bookname = Dir(ThisWorkbook.Path & "\操作対象ブックフォルダ\*")
    Workbooks.Open ThisWorkbook.Path & "\操作対象ブックフォルダ\" & bookname
 
Dim i
    Sheets("CH-731").Activate
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        Workbooks("実行用ブック.xlsx").Sheets("氏名").Cells(i - 1, 1) = Sheets("CH-731").Cells(i, 4).Value
 
    Next
 
    Range("A1").AutoFilter
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row - 1
 
        Sheets("CH-731").Activate
        Range("A1").AutoFilter
        Range("a1").AutoFilter 4, Workbooks("実行用ブック").Sheets("氏名").Cells(i, 1).Value
        Sheets("CH-731").Activate
        Sheets("CH-731").Copy
        Application.CutCopyMode = False
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs _
        ThisWorkbook.Path & "\" & Workbooks("実行用ブック").Sheets("氏名").Cells(i, 1) & "CH-731.xlsx"
        Application.DisplayAlerts = True
        ActiveWorkbook.Close
 
    Next
    Sheets("CH-731").Activate
    Range("A1").AutoFilter
 
End Sub


 
hatenaさんからのアドアイスがあるように、
ブック、シート、などで、修飾すると、
更に可読性が向上します。

回答
投稿日時: 24/02/18 11:41:01
投稿者: WinArrow

改めてコードを見直すと、
Forループが2回出てくるが、1回目のループは、何をしてるんでしょうか?
 
前レスで、実行用ブック.xlsx"は開いていないからと書きましたが、
新しく作成する部句ですよね?
この時点で、そのブックを参照するって、おかしくない?
再度、流れを検討してみてください。

投稿日時: 24/02/18 14:36:27
投稿者: まるよこ

説明不足で申し訳ありません。
 
まず、フォルダがありその中に「実行用ブック」があります。
VBAのコードはこの実行用ブックに記入しています。
ですのでマクロを動かす時点でこの実行用ブックは開いている状態です。
 
そして実行用ブックと同じ場所に「操作対象ブックフォルダ」フォルダがあり
その中にCH-731シートが入ったブックがあります。
 
Option Explicit
Sub 抽出()
Application.DisplayAlerts = False
Dim bookname As String
bookname = Dir(ThisWorkbook.Path & "\操作対象ブックフォルダ\*")
Workbooks.Open ThisWorkbook.Path & "\操作対象ブックフォルダ\" & bookname
 
で、操作対象ブックフォルダの中にあるファイルをひらきます。
 
dim i
Sheets("CH-731").Activate
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Workbooks("実行用ブック.xlsx").Sheets("氏名").Cells(i - 1, 1) = Sheets("CH-731").Cells(i, 4).Value
 
ひらいたファイルのCH-731のD列には氏名欄があります。
この氏名欄を実行用ブックの氏名シートに書き出していきます。
For i はこの動作で全員分の名前を書きだしています。

投稿日時: 24/02/18 15:17:13
投稿者: まるよこ

 
操作対象ブックのシート(CH731)の仕様
1行目:項目名
2行目以降:個人名データ:個人名はD列:重複あり/なし不明
 
D列の個人名の重複はありません。
件数に関しては場合によりますが多くても50です。
 
追加で
この操作対象ブックのシート(CH731)のブックについて
他の所属にも同じような仕様のブックがあります。
他の所属だとしても個人名の重複は一切ないです。
他の所属のブックも同じように処理がしたいため、
わざわざ操作対象ブックフォルダというものを作り、その中のブックを入れ替えて使用する
という前提でこのように動作にしました。

回答
投稿日時: 24/02/18 15:20:19
投稿者: WinArrow

申し訳ありあせん。何か、勘違いしていました。
 
>ですのでマクロを動かす時点でこの実行用ブックは開いている状態です。

引用:
職場で使おうと思ったのですが「インデックスが有効範囲にありません」となりました。

職場でも同じに
マクロを動かす時点でこの実行用ブックは開いている状態です。
と考えてよいのでsか?
 
CH-731シートの個人名は重複ありますか?

回答
投稿日時: 24/02/18 17:01:21
投稿者: Suzu

引用:
「インデックスが有効範囲にありません」となりました。なにか解決策がありますでしょうか。

 
原因追及の為、
ご質問にあったコードを、処理内容を変えずに、ワークブック、
ワークシートを明示的に指定実行する様にしてみました。
どこでエラーになるでしょうか?
 
Sub 抽出2()
  Dim bookname As String
  Dim i As Long

  Dim wbk0 As Workbook
  Dim wbk1 As Workbook
  Dim wst1 As Worksheet

  'Application.DisplayAlerts = False
  bookname = Dir(ThisWorkbook.Path & "\操作対象ブックフォルダ\*")

  Set wbk0 = ThisWorkbook
  Set wbk1 = Application.Workbooks.Open(ThisWorkbook.Path & "\操作対象ブックフォルダ\" & bookname)
  Set wst0 = wbk0.Worksheets("氏名")
  Set wst1 = wbk1.Worksheets("CH-731")

  For i = 2 To wst1.Cells(wst1.Rows.Count, 1).End(xlUp).Row
    wst0.Cells(i - 1, 1) = wst1.Cells(i, 4).Value
  Next

  wst1.Range("A1").AutoFilter
  For i = 1 To wst1.Cells(wst1.Rows.Count, 1).End(xlUp).Row - 1
    wst1.Range("A1").AutoFilter
    wst1.Range("a1").AutoFilter 4, wst0.Cells(i, 1).Value
    wst1.Copy

    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    wbk1.SaveAs ThisWorkbook.Path & "\" & wst0.Cells(i, 1) & "CH-731.xlsx"
    Application.DisplayAlerts = True
    wbk1.Close
  Next
  wst1.Range("A1").AutoFilter
End Sub

 
 
引用:
個人のファイルはオートフィルターとなっており、他の職員が非表示になっているだけで、個人別保存は結果としてうまくいきませんでした。currentregionを使うのがいいとは思っているのですが、なにかうまくいく方法はありますでしょうか。

 
コードに不慣れなうちは、
オートフィルターで抽出したデータをコピーし保存
ではなく
オートフィルターで目的以外を抽出し、削除、オートフィルターを解除し別名で保存
の方が簡単かと思いますよ。

回答
投稿日時: 24/02/18 17:05:33
投稿者: simple

 Explorer上で "登録されている拡張子は表示しない" というオプションを実行している場合は、
 Excelにもその影響が及びます。
 拡張子を省略してWorkbooks("実行用ブック")と書いてもエラーになりません。
 その扱いが職場とご自宅で違うというケースが想定されます。
 (脆弱性があるので拡張子省略オプションは余り推奨できません。)
  
 もしくは別のブックを使っていて、シートのネーミングが不一致であるかでしょう。
 ご質問にあたっては、デバッグの第一歩であるエラー箇所の確認は、
 質問者さんにおいて実行していただき、正確な情報を提供していただくことが必要です。

投稿日時: 24/02/18 17:39:53
投稿者: まるよこ

Suzu様
 
提供していただいたものを家のパソコンで試しました。
職場は明後日以降の出勤になるため、まだ試せていません。
 
wst1.Range("A1").AutoFilter
「オートメーションエラーです」
がでました
 
 
家のパソコンは
OS:Home11
Officeは2021でした。

回答
投稿日時: 24/02/19 08:21:15
投稿者: MMYS

Workbook や Worksheet は複数存在します。複数存在するので、どのブック・シートのなのか指定が必要です。その引数がインデックスです。
 
 Workbooks(インデックス)
 Sheets(インデックス)
 
引数でシート名を指定すると、ブック・シートの検索が内部で行われす。で、もし指定のブック・シートが存在しなかった場合、「インデックスが有効範囲にありません」とエラーになります。
 

まるよこ さんの引用:

まず、フォルダがありその中に「実行用ブック」があります。
VBAのコードはこの実行用ブックに記入しています。
ですのでマクロを動かす時点でこの実行用ブックは開いている状態です。

であるなら、自分自身なのは明確なため、次のように書き換えたほうが良いです。
 
 Workbooks("実行用ブック.xlsx").Sheets("氏名").Cells(i - 1, 1) = Sheets("CH-731").Cells(i, 4).Value
  ↓
 ThisWorkbook.Sheets("氏名").Cells(i - 1, 1) = Sheets("CH-731").Cells(i, 4).Value

これでエラーが発生する要因の一つのWorkbookの可能性は排除したので、エラーの原因は該当シートが存在しないためです。
 Sheets("氏名")
 Sheets("CH-731")
のどちらかで、該当シートが存在せず、見つからないためエラーが発生します。この場合、要因が1行に2つあると原因が特定困難なので、Suzuさんが、示されたとおり、
 
  Set wst0 = wbk0.Worksheets("氏名")
  Set wst1 = wbk1.Worksheets("CH-731")

と記述して、どちらでエラーが発生するかを特定します。
 
 
 
※以下補足。
毎回、Workbooks("実行用ブック.xlsx")やWorksheets("氏名")と記述すると、その都度、メモリ内部のどこにWorkbookオブジェクト・Worksheetsオブジェクトがあるのかを毎回検索が行われます。しかし、下記のように記述するとその情報が保持されます。そのためメモリ内部を再検索する必要かなないのでVBAの効率が上がります。(シート名の記述間違いなど無用なバグも排除出来でき可読性も上がります)
 
 Set wbk0 = Workbooks("実行用ブック.xlsx")

Workbooks.OpenはWorkbookオブジェクトを返しますから、直接下記のように記述も出来ます。
 
 Set wbk1 = Application.Workbooks.Open(ThisWorkbook.Path & "\操作対象ブックフォルダ\" & bookname)

回答
投稿日時: 24/02/19 09:40:07
投稿者: Suzu

まるよこ さんの引用:
wst1.Range("A1").AutoFilter
「オートメーションエラーです」
がでました

 
???
もともとインデックスが不明ですエラーは
 
Workbooks("実行用ブック.xlsx").Sheets("氏名").Cells(i - 1, 1) = Sheets("CH-731").Cells(i, 4).Value
 
の部分で発生すると言うことでした。
 
それは、当方のコードで言うと
 
  For i = 2 To wst1.Cells(wst1.Rows.Count, 1).End(xlUp).Row
    wst0.Cells(i - 1, 1) = wst1.Cells(i, 4).Value
  Next
です。
 
ここ以前の部分で、インデックスが不明 が発生するはずなのですよ。。
 
それが発生しない。
 
他の方も述べていますが
Workbooks("実行用ブック.xlsx").Sheets("氏名").Cells(i - 1, 1) = Sheets("CH-731").Cells(i, 4).Value
これ。
 
引用:
まず、フォルダがありその中に「実行用ブック」があります。
VBAのコードはこの実行用ブックに記入しています。

コードが記載されているブックの拡張子は、「xlsm」 です。
「xlsx」の拡張子のファイルは開かれていないので、参照できず インデックスエラーになっています。
 
Workbooks("実行用ブック.xlsm") とするか、
これ以降の位置の様に、Workbooks("実行用ブック") とします。
 
 
 
  wst1.Range("A1").AutoFilter
でエラーになったと言うことですか。。
しかもオートメーションエラー??
 
フィルター対象が無いなら AutoFilterメソッドが失敗しました のはず。。
???
オートメーションエラーの原因は判りかねます。
 
 
とりあえず、元コードの コード中の拡張子の部分を変えれば、エラーとはならないはずです。
 
でも・・
引用:
家のパソコンでは実行ができて、職場で使おうと思ったのですが

 
???片方うまくいって、片方うまくいかない??
エラーにならなかったのは、実行用ブック.xlsx と、実行用ブック.xlsm が 同時に開いていたから
くらいしか思いつきません。
 
質問者の方の状況をある程度予測・想定し回答しなければならないのは しょうがないと思えるのですが、
オートメーションエラーも含め、 ご説明頂けていない、その予測・想定が 多すぎる様です。
すみませんが、当方は回答リタイヤです。

回答
投稿日時: 24/02/19 10:15:35
投稿者: WinArrow

引用:

VBAのコードはこの実行用ブックに記入しています。
ですのでマクロを動かす時点でこの実行用ブックは開いている状態です。

 
ということは、
Workbooks("実行用ブック.xlsx")

Workbooks("実行用ブック.xlm")
の間違いですね。
 
若しかして、掲示板のコードは、手入力ですか?
手入力して間違ったコードを掲示しても、
回答者は、掲示したコードしか見えないし、
それによって、遠回りして、解決に至らないこともありえます。
 
コードは、コードペインからコピペしてください。

回答
投稿日時: 24/02/19 15:12:28
投稿者: WinArrow

追加質問

引用:

For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Workbooks("実行用ブック.xlm").Sheets("氏名").Cells(i - 1, 1) = Sheets("CH-731").Cells(i, 4).Value
Next


個人名を「氏名」シートの書き出していますが、
個人名が重複しているならば、最後の名前を付けて保存処理で
同一ブックを作成することになります。
勿論、同一名のファイルは保存できないから、警告メッセージに対応する形になるでしょう。
  
個人名が重複していないならば、Autofilterで抽出する必要はありません。
1回目のループ処理も不要です。
 
 
因みに
Autoilter後
引用:
Sheets("CH-731").Activate
Sheets("CH-731").Copy

の実行は、"CH-731"シートをそっくり複写した「新しいブック」が作成されます。
ですから、AutoFiter実行前と同じ内容のブックになっています。
(但し、AutoFiterで非表示になっている行が含まれています。)

投稿日時: 24/02/21 09:24:47
投稿者: まるよこ

Suzu様 や WinArrow様が言われていたとおり
Workbooks("実行用ブック.xlsm") としたところ、職場でも使うことができました。
質問や皆様から聞かれてることの返答について、私が至らない点が多く、多くの皆様の時間をおとりしてしまい申し訳ありませんでした。またコードの流れ自体が無駄な部分も多く、余計に混乱させしまって申し訳ありません。もっと勉強が必要だと感じました。
そして本当にありがとうございました。