Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


10087 / 13644 ツリー ←次へ | 前へ→

【23713】For Next と For Each文の関係 okb 05/4/2(土) 14:22 質問[未読]
【23717】Re:For Next と For Each文の関係 IROC 05/4/2(土) 15:03 回答[未読]
【23724】Re:For Next と For Each文の関係 okb 05/4/2(土) 16:24 質問[未読]
【23731】Re:For Next と For Each文の関係 ちゃっぴ 05/4/2(土) 22:13 回答[未読]
【23732】Re:For Next と For Each文の関係 ちゃっぴ 05/4/2(土) 22:18 発言[未読]
【23734】Re:For Next と For Each文の関係 okb 05/4/3(日) 0:03 質問[未読]
【23735】Re:For Next と For Each文の関係 ウッシ 05/4/3(日) 0:22 回答[未読]
【23737】Re:For Next と For Each文の関係 okb 05/4/3(日) 0:40 質問[未読]
【23738】Re:For Next と For Each文の関係 ウッシ 05/4/3(日) 0:46 回答[未読]
【23739】Re:For Next と For Each文の関係 ちゃっぴ 05/4/3(日) 1:12 回答[未読]
【23745】Re:For Next と For Each文の関係 okb 05/4/3(日) 10:03 質問[未読]
【23773】Re:For Next と For Each文の関係 ちゃっぴ 05/4/3(日) 23:03 回答[未読]
【23741】Re:For Next と For Each文の関係 okb 05/4/3(日) 1:30 質問[未読]
【23746】Re:For Next と For Each文の関係 ウッシ 05/4/3(日) 10:27 発言[未読]
【23750】Re:For Next と For Each文の関係 okb 05/4/3(日) 11:24 質問[未読]
【23744】Re:For Next と For Each文の関係 Hirofumi 05/4/3(日) 9:35 回答[未読]
【23748】Re:For Next と For Each文の関係 okb 05/4/3(日) 10:39 質問[未読]
【23749】Re:For Next と For Each文の関係 Hirofumi 05/4/3(日) 11:00 質問[未読]
【23753】Re:For Next と For Each文の関係 okb 05/4/3(日) 11:58 質問[未読]
【23754】Re:For Next と For Each文の関係 ウッシ 05/4/3(日) 12:03 質問[未読]
【23755】Re:For Next と For Each文の関係 okb 05/4/3(日) 12:20 発言[未読]
【23756】Re:For Next と For Each文の関係 Hirofumi 05/4/3(日) 12:46 回答[未読]
【23758】Re:For Next と For Each文の関係 okb 05/4/3(日) 13:12 発言[未読]
【23759】Re:For Next と For Each文の関係 Hirofumi 05/4/3(日) 13:39 回答[未読]
【23760】Re:For Next と For Each文の関係 okb 05/4/3(日) 13:57 お礼[未読]
【23761】Re:For Next と For Each文の関係 Hirofumi 05/4/3(日) 14:55 回答[未読]
【23757】Re:For Next と For Each文の関係 okb 05/4/3(日) 12:47 発言[未読]

【23713】For Next と For Each文の関係
質問  okb  - 05/4/2(土) 14:22 -

引用なし
パスワード
   つぎの構文で、思うように動作しないのでMsgBoxで、確認すると、
For Each Next文が正常に動作しません。
シートは、6枚なんですが、同じシートをチェックしたり、チェックされない
シートあったりで、よく判りません。
この構文が、規則違反ならその代替案をご教示いただくと、うれしいです。

Dim Sh As Worksheet
For i = 5 To 29 Step 2
      Sheets("Menu").Select
      If Cells(i, 7).Value = "" And Cells(i, 6).Value <> "" Then
          Shname = Cells(i, 6).Value
          For Each Sh In Worksheets
            MsgBox (i & Shname & ActiveSheet.Name)
            Sh.Activate
            If ActiveSheet.Name = "Menu" Or ActiveSheet.Name 
                   = "基準管理" Then
                      wdummy = "aaa"
            ElseIf ActiveSheet.Name = Shname Then
                 Application.DisplayAlerts = False 
                 ActiveWindow.SelectedSheets.Delete
                 Application.DisplayAlerts = True
            Else
               wdummy = "bbb"
            End If
          Next Sh
          Sheets("Menu").Select
       End If
Next

【23717】Re:For Next と For Each文の関係
回答  IROC  - 05/4/2(土) 15:03 -

引用なし
パスワード
   SelectやActive〜のような選択するだけの処理は
誤動作の原因となるので、使わない方が良いでしょう。

With文を使いましょう。
    
    With Sheets("Menu")

      If .Cells(i, 7).Value = "" And .Cells(i, 6).Value <> "" Then
         
       Shname = .Cells(i, 6).Value

    End With    
−−−−−−−−−−
   Sh.Activate
   If ActiveSheet.Name = "Menu" Or ActiveSheet.Name 
     ↓
   If Sh.Name = "Menu" Or Sh.Name 

アクティブにする必要はありません。


あとシートを削除すると、行削除と同様に
シートの順番(インデックス番号)がズレますので
for〜Next で Step -1 で逆からループしないと正しく動作しません。

【23724】Re:For Next と For Each文の関係
質問  okb  - 05/4/2(土) 16:24 -

引用なし
パスワード
   IROC さん:レスありがとうございました。
しかし、まだよくわかっていません。
つぎのように訂正したのですが、With文でエラーになります。
With文を理解していません。
For i = 29 To 5 Step -2
    With Sheets("Menu")
      'Sheets("Menu").Select
      If Cells(i, 7).Value = "" And Cells(i, 6).Value <> "" Then
          Shname = Cells(i, 6).Value
          For Each Sh In Worksheets
            MsgBox (i & Shname & Sh.Name)
               'Sh.Activate
            If Sh.Name = "Menu" Or Sh.Name = "基準管理" Then
               'If ActiveSheet.Name = "Menu" Or
                'ActiveSheet.Name = "基準管理" Then
               wdummy = "aaa"
            ElseIf Sh.Name = Shname Then
                 Application.DisplayAlerts = False
                 ActiveWindow.SelectedSheets.Delete
                 Application.DisplayAlerts = True
            Else
               wdummy = "bbb"
            End If
          Next Sh
          'Sheets("Menu").Select
       End If
     End With
Next

>SelectやActive〜のような選択するだけの処理は
>誤動作の原因となるので、使わない方が良いでしょう。
For Each のことでしょうか?


>あとシートを削除すると、行削除と同様に
>シートの順番(インデックス番号)がズレますので
>for〜Next で Step -1 で逆からループしないと正しく動作しません。
For Eachを、For Nextに書き換えるのでしょうか?
上記のFor Nextでは、意味ないように思いますが…。  

【23731】Re:For Next と For Each文の関係
回答  ちゃっぴ  - 05/4/2(土) 22:13 -

引用なし
パスワード
   >つぎのように訂正したのですが、With文でエラーになります。
>With文を理解していません。

Debug.Print Workbooks("〜").Worksheets("〜").Cells(1, 1).Value
Debug.Print Workbooks("〜").Worksheets("〜").Cells(2, 1).Value
Debug.Print Workbooks("〜").Worksheets("〜").Cells(3, 1).Value

これを、With Statementを使用して書き直すと
こういう感じになります。

With Workbooks("〜").Worksheets("〜")
  Debug.Print .Cells(1, 1).Value
  Debug.Print .Cells(2, 1).Value
  Debug.Print .Cells(3, 1).Value
End With

>>SelectやActive〜のような選択するだけの処理は
>>誤動作の原因となるので、使わない方が良いでしょう。

Selection等を使用した場合、その名のとおり、
現在選択されているものを対象に処理されますよね。

この現在選択されているというのが曲者で、Bookを開いたりとかすると
選択されているBookが変更されたりします。

ということはすなわち、そこまで把握してCodeを読まないといけないので
それだけわかりにくいということになります。

明示できるような箇所でしたら、明示してやったほうがいいでしょう。
(処理も速くなりますし・・・)

>>あとシートを削除すると、行削除と同様に
>>シートの順番(インデックス番号)がズレますので
>>for〜Next で Step -1 で逆からループしないと正しく動作しません。
>For Eachを、For Nextに書き換えるのでしょうか?
>上記のFor Nextでは、意味ないように思いますが…。

Index番号を意識してください。  
ある行を削除すると次の行の行番号はどうなりますか?

Indexが存在するCollection ObjectをFor Each 〜 Next Loopで
まわすとえらいことになります。行はIndex指定できます。

FSOの Files Collection Object のような場合、
Index指定が存在しないので、そういった現象は発生しません。

【23732】Re:For Next と For Each文の関係
発言  ちゃっぴ  - 05/4/2(土) 22:18 -

引用なし
パスワード
   【23548】データの間引きの高速化
http://www.vbalab.net/vbaqa/c-board.cgi?cmd=one;no=23548;id=excel

ここでも書きましたが、行の削除は基本的に行うべきものではないです。

Dataの間引きが必要であれば、作業列を作り、そこに削除する印をつけておいて
Sortし、まとまったところでClear(ClearContents)してやったほうがいいでしょう。

また、別のSheetに抽出するという手もあります。

【23734】Re:For Next と For Each文の関係
質問  okb  - 05/4/3(日) 0:03 -

引用なし
パスワード
   かなり、悩んでいます。
考え方をかえて、下記のようにしました。
For i = 5 To 29 Step 2
    Sheets("Menu").Select
    If Cells(i, 7).Value = "" And Cells(i, 6).Value <> "" Then
       Shname = Cells(i, 6).Value
       MsgBox (i & Shname)
       Sheets(Shname).Select
       Application.DisplayAlerts = False
       ActiveWindow.SelectedSheets.Delete
       Application.DisplayAlerts = True
       MsgBox ("DEL")
    End If
Next
途中までは、正常に削除されますが、Sheets(Shname).Selectが何件目かで"インデックスの有効範囲外"でこけます。
シート(Menu)の値は、ハイパーリンクで取り込んだもので間違いないはずです。
構文に間違いが、あるでしょうか?

【23735】Re:For Next と For Each文の関係
回答  ウッシ  - 05/4/3(日) 0:22 -

引用なし
パスワード
   こんばんは

http://park7.wakwak.com/~efc21/cgi-bin/wwwlng.cgi?print+200504/05040011.txt
は終了する旨を書き込んで解決にして下さい。

>途中までは、正常に削除されます
>シート(Menu)の値は、ハイパーリンクで取り込んだもの
どうやって取り込んでいるのか具体的には?

       MsgBox (i & Shname)
       Application.DisplayAlerts = False
       Sheets(Cstr(Shname)).Delete
       Application.DisplayAlerts = True

とするとどうなりますか?

【23737】Re:For Next と For Each文の関係
質問  okb  - 05/4/3(日) 0:40 -

引用なし
パスワード
   >http://park7.wakwak.com/~efc21/cgi-bin/wwwlng.cgi?print+200504/05040011.txt
>は終了する旨を書き込んで解決にして下さい。
解決にしました。
ルール違反、お許しください。

>>シート(Menu)の値は、ハイパーリンクで取り込んだもの
>どうやって取り込んでいるのか具体的には?
メニュー>挿入>ハイパーリンク>ドキュメント内から取り込んだものです。

>       MsgBox (i & Shname)
>       Application.DisplayAlerts = False
>       Sheets(Cstr(Shname)).Delete
>       Application.DisplayAlerts = True
>
>とするとどうなりますか?
Sheets(Shname).Selectで、こけるまでは正常に表示、削除されます。

【23738】Re:For Next と For Each文の関係
回答  ウッシ  - 05/4/3(日) 0:46 -

引用なし
パスワード
   こんばんは

>Sheets(Shname).Selectで、こけるまでは正常に表示、削除されます。
「Sheets(Shname).Select」は不要ですよ。

Application.DisplayAlerts = False
With Sheets("Menu")
  For i = 5 To 29 Step 2
    If .Cells(i, 7).Value = "" And .Cells(i, 6).Value <> "" Then
      Shname = .Cells(i, 6).Value
      'MsgBox (i & Shname)
      Sheets(Cstr(Shname)).Delete
      'MsgBox ("DEL")
    End If
  Next
End With
Application.DisplayAlerts = True

【23739】Re:For Next と For Each文の関係
回答  ちゃっぴ  - 05/4/3(日) 1:12 -

引用なし
パスワード
   Sheetの削除でしたね。(__)

ということであれば、WorkSheets Propertyの引数には配列を指定可能なので
For Each 〜 Next Loop で削除するSheetのIndexを配列に格納し、
まとめて削除ということもできますね。

Dim wstTarget As Excel.WorkSheet
Dim lngIndexes() As Long
Dim i As Long

For Each wstTarget In WorkSheets
  ' 削除条件を指定
  If 〜 Then
    Redim Preserve lngIndexes(i)
    lngIndexes(i) = wstTarget.Index
  End If
  i = i + 1
Next wstTarget

' まとめて削除
Application.DisplayAlerts = False
Worksheets(lngIndexes).Delete
Application.DisplayAlerts = True

こっちのほうが速いはずです。

【23741】Re:For Next と For Each文の関係
質問  okb  - 05/4/3(日) 1:30 -

引用なし
パスワード
   レスありがとうございました。
早速、実行してみました。
>      Sheets(Cstr(Shname)).Delete
正常に、削除されるシートもありますが、上記で 実行時エラーg インデックス
が有効範囲でない となります。

【23744】Re:For Next と For Each文の関係
回答  Hirofumi  - 05/4/3(日) 9:35 -

引用なし
パスワード
   此れって、「WorkSheetsコレクション」とか「For Each 〜 Next」の問題じゃなくて
ただ単に、「Cells(i, 6).Value」の値の問題では無いの?
詰まり、シート名とCells(i, 6).Valueの値が、
全角、半角、大文字、小文字等が違っているのでは?

もしそうなら、以下の様にして見たら

Option Explicit
Option Compare Text '★この行追加

Public Sub SheetsDelete1()

  Dim Sh As Worksheet
  Dim i As Long
  Dim Shname As Variant
  Dim wdummy As String
  
  For i = 5 To 29 Step 2
    With Sheets("Menu")
      If .Cells(i, 7).Value = "" And .Cells(i, 6).Value <> "" Then
        Shname = .Cells(i, 6).Value
        For Each Sh In Worksheets
          If Not (Sh.Name = "Menu" Or Sh.Name = "基準管理") Then
            If Sh.Name = Shname Then
              MsgBox Shname & "が削除されます"
'              Application.DisplayAlerts = False
'              ActiveWindow.SelectedSheets.Delete
'              Application.DisplayAlerts = True
            End If
          End If
        Next Sh
      End If
    End With
  Next i
  
End Sub

若しくは、比較を「=」では無く、
「StrComp」で「vbTextCompare」を指定して比較するとか?

'Option Explicit

Public Sub SheetsDelete2()

  Dim Sh As Worksheet
  Dim i As Long
  Dim Shname As Variant
  Dim wdummy As String
  
  For i = 5 To 29 Step 2
    With Sheets("Menu")
      If .Cells(i, 7).Value = "" And .Cells(i, 6).Value <> "" Then
        Shname = .Cells(i, 6).Value
        For Each Sh In Worksheets
          If StrComp(Sh.Name, "Menu", vbTextCompare) <> 0 _
              And StrComp(Sh.Name, "基準管理", vbTextCompare) <> 0 Then
            If StrComp(Sh.Name, Shname, vbTextCompare) = 0 Then
              MsgBox Shname & "が削除されます"
'              Application.DisplayAlerts = False
'              ActiveWindow.SelectedSheets.Delete
'              Application.DisplayAlerts = True
            End If
          End If
        Next Sh
      End If
    End With
  Next i
  
End Sub

【23745】Re:For Next と For Each文の関係
質問  okb  - 05/4/3(日) 10:03 -

引用なし
パスワード
   構文を、いまいち理解できません。
削除条件をどのように、書けばいいんでしょう?
i = i + 1 の位置は?
>Dim wstTarget As Excel.WorkSheet
>Dim lngIndexes() As Long
>Dim i As Long
>
>For Each wstTarget In WorkSheets
>  ' 削除条件を指定
>  If 〜 Then
>    Redim Preserve lngIndexes(i)
>    lngIndexes(i) = wstTarget.Index
>  End If
>  i = i + 1
i = i + 1 は、End Ifの前では?

>Next wstTarget
>
>' まとめて削除
>Application.DisplayAlerts = False
>Worksheets(lngIndexes).Delete
>Application.DisplayAlerts = True

【23746】Re:For Next と For Each文の関係
発言  ウッシ  - 05/4/3(日) 10:27 -

引用なし
パスワード
   こんにちは

>>途中までは、正常に削除されます
>>シート(Menu)の値は、ハイパーリンクで取り込んだもの
>どうやって取り込んでいるのか具体的には?

Hirofumi さんも書かれていますけど、
「ハイパーリンクで取り込んだ」というCells(i, 6).Valueのシート名の問題かと思います。

【23748】Re:For Next と For Each文の関係
質問  okb  - 05/4/3(日) 10:39 -

引用なし
パスワード
   早速実行してみました。
1件目のメッセージを表示後、インデックスエラーになりました。
>    With Sheets("Menu")
       上でエラー
>      If .Cells(i, 7).Value = "" And .Cells(i, 6).Value <> "" Then
>        Shname = .Cells(i, 6).Value
>        For Each Sh In Worksheets
>          If Not (Sh.Name = "Menu" Or Sh.Name = "基準管理") Then
>            If Sh.Name = Shname Then
>              MsgBox Shname & "が削除されます"
>              Application.DisplayAlerts = False
>              ActiveWindow.SelectedSheets.Delete
>              Application.DisplayAlerts = True
>            End If
>          End If
>        Next Sh
>      End If
>    End With

【23749】Re:For Next と For Each文の関係
質問  Hirofumi  - 05/4/3(日) 11:00 -

引用なし
パスワード
   ▼okb さん:
>早速実行してみました。
>1件目のメッセージを表示後、インデックスエラーになりました。
>>    With Sheets("Menu")
>       上でエラー
>>      If .Cells(i, 7).Value = "" And .Cells(i, 6).Value <> "" Then
>>        Shname = .Cells(i, 6).Value
>>        For Each Sh In Worksheets
>>          If Not (Sh.Name = "Menu" Or Sh.Name = "基準管理") Then
>>            If Sh.Name = Shname Then
>>              MsgBox Shname & "が削除されます"
>>              Application.DisplayAlerts = False
>>              ActiveWindow.SelectedSheets.Delete
>>              Application.DisplayAlerts = True
>>            End If
>>          End If
>>        Next Sh
>>      End If
>>    End With

変、インデックスエラーとは、「Menu」と言うシートが無いからでしょ
元々のコードで

Dim Sh As Worksheet
For i = 5 To 29 Step 2
      Sheets("Menu").Select
      If Cells(i, 7).Value = "" And Cells(i, 6).Value <> "" Then

Sheets("Menu").Selectと言う事は、「Menu」と言うシートが有るはずでは?

【23750】Re:For Next と For Each文の関係
質問  okb  - 05/4/3(日) 11:24 -

引用なし
パスワード
   >「ハイパーリンクで取り込んだ」というCells(i, 6).Valueのシート名の問題かと思います。
シート名を入力した値から、実行しても同じですね。
少し現象がわかってきました。シート名の値そのものによるようです。
漏電ーーーーーーOK
動力ポンプーーーOK
非常コンセントー不可、必ずインデックスエラー 
非常コンセントの位置を変えても同じです。

何が、原因なんでしょうか?

【23753】Re:For Next と For Each文の関係
質問  okb  - 05/4/3(日) 11:58 -

引用なし
パスワード
   >>      Sheets("Menu").Select
>      If Cells(i, 7).Value = "" And Cells(i, 6).Value <> "" Then
>
>Sheets("Menu").Selectと言う事は、「Menu」と言うシートが有るはずでは?
なぜなんでしょう?「Menu」と言うシートは、ありますし、Sheets("Menu").Selectも実行できます。

【23754】Re:For Next と For Each文の関係
質問  ウッシ  - 05/4/3(日) 12:03 -

引用なし
パスワード
   こんにちは

Sheets("非常コンセント").Select
は出来るのでしょうか?

【23755】Re:For Next と For Each文の関係
発言  okb  - 05/4/3(日) 12:20 -

引用なし
パスワード
   >Sheets("非常コンセント").Select
>は出来るのでしょうか?
エラーでした。
Sheets("避難器具").Selectでは、成功です。

【23756】Re:For Next と For Each文の関係
回答  Hirofumi  - 05/4/3(日) 12:46 -

引用なし
パスワード
   変だけど、Select式に替えてみました

Public Sub SheetsDelete3()

  Dim Sh As Worksheet
  Dim i As Long
  Dim Shname As Variant
  
  For i = 5 To 29 Step 2
    Sheets("Menu").Select
    If Cells(i, 7).Value = "" And Cells(i, 6).Value <> "" Then
      Shname = Trim(Cells(i, 6).Value)
      For Each Sh In Worksheets
        If StrComp(Sh.Name, "Menu", vbTextCompare) <> 0 _
            And StrComp(Sh.Name, "基準管理", vbTextCompare) <> 0 Then
          If StrComp(Sh.Name, Shname, vbTextCompare) = 0 Then
            MsgBox Shname & "が削除されます"
'            Application.DisplayAlerts = False
'            ActiveWindow.SelectedSheets.Delete
'            Application.DisplayAlerts = True
          End If
        End If
      Next Sh
    End If
  Next i
  
End Sub

【23757】Re:For Next と For Each文の関係
発言  okb  - 05/4/3(日) 12:47 -

引用なし
パスワード
   >Sheets("非常コンセント").Select
>は出来るのでしょうか?
シート名を変更して、Sheets("非常").Select では成功しました。
どういうことなんでしょう?

【23758】Re:For Next と For Each文の関係
発言  okb  - 05/4/3(日) 13:12 -

引用なし
パスワード
   すみません。Delete をコメントにすると
(1),(3)共に、いずれも動作しますがコメントをはずすと、エラーになります。

【23759】Re:For Next と For Each文の関係
回答  Hirofumi  - 05/4/3(日) 13:39 -

引用なし
パスワード
   ▼okb さん:
>すみません。Delete をコメントにすると
>(1),(3)共に、いずれも動作しますがコメントをはずすと、エラーになります。

ゴメン、コメントアウトの部分以下の様に修正して下さい

          If StrComp(Sh.Name, Shname, vbTextCompare) = 0 Then
            MsgBox Shname & "が削除されます"
            Application.DisplayAlerts = False
            Sh.Delete
            Application.DisplayAlerts = True
          End If

【23760】Re:For Next と For Each文の関係
お礼  okb  - 05/4/3(日) 13:57 -

引用なし
パスワード
   Hirofumi さん ありがとうございました。
正常に、動作しました。
感謝、感謝です。
昨日から、悩んでいたので、うれしいです。
本当に、ありがとうございました。

【23761】Re:For Next と For Each文の関係
回答  Hirofumi  - 05/4/3(日) 14:55 -

引用なし
パスワード
   れで動いたって事は

>此れって、「WorkSheetsコレクション」とか「For Each 〜 Next」の問題じゃなくて
>ただ単に、「Cells(i, 6).Value」の値の問題では無いの?
>詰まり、シート名とCells(i, 6).Valueの値が、
>全角、半角、大文字、小文字等が違っているのでは?

の都合と、後、 Cells(i, 6).Valueの値の後にスペースが入っているのだと思います

後、シート名でセルを修飾できないは、何とも解せないはなしです

【23773】Re:For Next と For Each文の関係
回答  ちゃっぴ  - 05/4/3(日) 23:03 -

引用なし
パスワード
   すでに解決されたようですが・・・

>削除条件をどのように、書けばいいんでしょう?

は、自分で考えてください。私よりよっぽど詳しいはずです。

>i = i + 1 は、End Ifの前では?

失礼しました。おっしゃるとおり、End Ifの前になります。

10087 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free