Excel (VBA)

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

 
(Windows 10全般 : Excel 2016)
ForEach...Nextステートメントが上手くいきません
投稿日時: 21/07/29 15:12:22
投稿者: chiii1519

 
皆様ご教示お願い致します。
本業ではない為不足が多いかと思いますが勉強させて下さい。
 
【業務管理一覧】に当月データの記録のある行を可視セルで指定し、ForEach...Nextステートメントにより
同ブック、別シート【マスタ】及び【データ】からループで記録していきたいのですが、【データ】から記録したい情報が入らず全てブランクになってしまいます。エラーは発生していません。
 
VisibleRangeには指定列に当月作業日が入力されているデータをフィルターで絞った可視セルが格納されています。
 
成功点
・可視セル選択
・【マスタ】からの記録
 
失敗点
・【データ】からの記録
 
 
 
******************************************************************************************
     
'変数宣言
    Dim A, key, rM, rH, rMy, rFirst As Range
     
'キー(No)をセット
    Set key = VisibleRange.Offset(0, -2)
 
'範囲をセット
    Sheets("マスタ").Activate
    Set A = Range("A2", Range("A2").End(xlDown))
     
'ループ処理
'@
 
    For Each rM In A
       Set rH = key
       Set rMy = rH.Find(what:=rM)
     
     If rMy Is Nothing Then
       Exit For
      
       Else
     
       Set rFirst = rMy
       rMy.Offset(0, 4) = rM.Offset(0, 3)
   
    End If
     
    Do
       Set rMy = rH.FindNext(rMy)
       If rMy.Address = rFirst.Address Then
        
    Exit Do
     
       Else
        
       rMy.Offset(0, 4) = rM.Offset(0, 3)
        
    End If
     Loop
     
    Next
 
これを同様に4行分繰り返します。
ここまでは異常なしです。
 
問題はここからです。
 
'変数宣言(追加)
  Dim B, rM_2, rMy_2, rFirst_2 As Range
    
'範囲をセット
    Sheets("データ").Activate
    Set B = Range("A2", Range("A2").End(xlDown))
          
'ループ処理
'収益フラグ
 
    For Each rM_2 In B
       Set rH = key
       Set rMy_2 = rH.Find(what:=rM_2)
     
     If rMy_2 Is Nothing Then
       Exit For
      
       Else
     
       Set rFirst_2 = rMy_2
       rMy_2.Offset(0, 8) = rM_2.Offset(0, 8)
   
    End If
     
    Do
       Set rMy_2 = rH.FindNext(rMy_2)
       If rMy_2.Address = rFirst_2.Address Then
        
    Exit Do
     
       Else
        
       rMy_2.Offset(0, 8) = rM_2.Offset(0, 8)
        
    End If
     Loop
     
    Next
 
 
ここの処理がブランクになります。
 
分かりにくい点が多いかと思いますがお力を貸してください。。。
 
どうぞよろしくお願いいたします。

回答
投稿日時: 21/07/29 16:44:09
投稿者: WinArrow
投稿者のウェブサイトに移動

>分かりにくい点が多い
 
分かりにくいということは、少しは分かるところがあると解釈できるが
表のレイアウトなどの説明がなけらば、
何をしようとしているかもわかりません。
 
デバッグの方法を紹介します。
 
 
ステップ実行を使えば、
どこの行で、変数が意図した値になっているか
意図した流れになっているか
確認することができます。
 

回答
投稿日時: 21/07/29 17:18:25
投稿者: sk

引用:
【業務管理一覧】に当月データの記録のある行を可視セルで指定し、
ForEach...Nextステートメントにより同ブック、別シート【マスタ】
及び【データ】からループで記録していきたいのですが、
【データ】から記録したい情報が入らず全てブランクになってしまいます。
エラーは発生していません

引用:
For Each rM_2 In B
    Set rH = key
    Set rMy_2 = rH.Find(what:=rM_2)
  
    If rMy_2 Is Nothing Then
        Exit For
    Else
        Set rFirst_2 = rMy_2
        rMy_2.Offset(0, 8) = rM_2.Offset(0, 8)
    End If

1 回目のループにおいて、Find メソッドの戻り値が Nothing である
(ヒットするセルがない)のであれば、そのままループを抜けるので
そうなるのは当然の結果ですね。
 
----------------------------------------------------------------------
 
'変数宣言(追加)
Dim B As Range, rM_2 As Range, rMy_2 As Range, sFirstAddress As String
 
'範囲をセット
Sheets("データ").Activate
Set B = Range("A2", Range("A2").End(xlDown))
 
For Each rM_2 In B
    Set rMy_2 = key.Find(What:=rM_2.Value, LookIn:=xlValues, LookAt:=xlWhole)
    If Not rMy_2 Is Nothing Then
        sFirstAddress = rMy_2.Address
        Do
            rMy_2.Offset(0, 8) = rM_2.Offset(0, 8)
            Set rMy_2 = key.FindNext(rMy_2)
            If rMy_2 Is Nothing Then
                Exit Do
            End If
        Loop Until rMy_2.Address = sFirstAddress
    End If
Next
 
----------------------------------------------------------------------
 
恐らくこういうことをなさりたいのではないかと。
(場合によっては Do ... Loop ステートメントも要らないと思いますが)

回答
投稿日時: 21/07/29 22:15:47
投稿者: simple

まずはインデントをきちんとつけることを強く推奨いたします。
掲示板に投稿するしないと関係なく、コードの構造がもっと明確に見えるはずです。
あなたにとって重要です。
 
既に本質的部分について指摘がありますので、周辺的事項についてコメントします。
(1)

  Dim A, key, rM, rH, rMy, rFirst As Range
と宣言するとRange型なのは rFirst だけで、他はすべてVariant型であることに
 注意してください。(指摘がありました)
 
(2)
    Set key = VisibleRange.Offset(0, -2)
ですが、VisibleRangeが使われているのを初めて見ました。
  
 ただし、これはこのままではエラーになりませんか?
 VisibleRangeにはWindowオブジェクトを頭につける必要があるように思います。
  
 また、フィルタなどの可視セルに限定されるわけではありません。
 Addressを確認してみてください。
  
 実行時に何が表示されているかに大きく依存します。
 Select以上に状況依存のように思います。
 例えば、A列が表示されているとエラーになります。
 また、一画面に収まる部分しか対象にできません。(承知の上なら結構ですが)

回答
投稿日時: 21/07/29 23:17:48
投稿者: WinArrow
投稿者のウェブサイトに移動

>VisibleRange
 
は、推測ですが
 
Dim VisibleRange As Range
ではないかと思います。
コード掲示の際、一緒に掲示してくださいね・・・

回答
投稿日時: 21/07/29 23:33:00
投稿者: WinArrow
投稿者のウェブサイトに移動

@のループは、成功していると書いてあるが、
たまたま、成功しただけです。
基本的に、論理が逆になっています。
skさんのレスと重複しますが、
Do 〜 Loopをもっと簡潔に記述すると
 

   Do
        rMy.Offset(0, 4) = rM.Offset(0, 3)
        Set rMy = rH.FindNext(rMy)
    Loop Until rMy.Address = rFirst.Address

 
このように4行で記述できます。
検証してみましょう。

回答
投稿日時: 21/07/30 15:40:21
投稿者: mattuwan44

ごめんなさい。
 
そもそもなんでFindメソッドを使わないといけないかが不明です。
オートフィルターで抽出されたデータの何列目かの値を右に移動させたいのでは?
 
やりたいことの説明を日本語で説明してはいかがでしょうか?
 
あと、できるだけ、コードは端折らない方がよいかと思います。

回答
投稿日時: 21/07/30 16:55:01
投稿者: sk

mattuwan44 さんの引用:
オートフィルターで抽出されたデータの何列目かの値を右に移動させたいのでは?

chiii1519 さんの引用:
業務管理一覧】に当月データの記録のある行を可視セルで指定

chiii1519 さんの引用:
Set key = VisibleRange.Offset(0, -2)

chiii1519 さんの引用:
For Each rM In A
   Set rH = key
   Set rMy = rH.Find(what:=rM)

・rMy の参照先はワークシート[業務管理一覧]のセルである。
 (変数 VisibleRange に対する Set ステートメントが端折られてはいるが、
 「【マスタ】からの記録」は一応出来ているという証言を信じるなら、
 この部分に関して疑う余地はない)
 
chiii1519 さんの引用:
同ブック、別シート【マスタ】及び【データ】からループで記録

chiii1519 さんの引用:
Sheets("マスタ").Activate
Set A = Range("A2", Range("A2").End(xlDown))

chiii1519 さんの引用:
For Each rM In A
   Set rH = key
   Set rMy = rH.Find(what:=rM)

・rM の参照先はワークシート[マスタ]のセルである。
 
chiii1519 さんの引用:
rMy.Offset(0, 4) = rM.Offset(0, 3)

・したがって、上記のコードは、あるセルの値を
 同じワークシート上の別のセルに代入するコードではない

回答
投稿日時: 21/07/30 17:54:03
投稿者: simple

VisibleRangeに関する説明部分を読み飛ばし、コードだけで疑問を書いてしまいました。
失礼しました。その部分をなかったものとしてください。 ペコリ。

投稿日時: 21/08/03 15:38:30
投稿者: chiii1519

WinArrow様、sk様、simple様 お忙しい中ご回答ありがとうございます。
お返事が遅くなり申し訳ございません。
 
まずは、
・インデントを付ける(初歩の初歩ですが...)
・ForEachNext と DoLoop部分の論理が逆
という点について理解出来るまで勉強したいと思います。
 
WinArrow様、sk様のアドバイス通りにコードを修正しましたところ
希望通りに動きました。
 
図も添付せず視覚的情報の少ない中、内容を的確にご理解頂きまして
ご丁寧なアドバイスをありがとうございました。

回答
投稿日時: 21/08/03 22:38:56
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:
・ForEachNext と DoLoop部分の論理が逆
という点について理解出来るまで勉強したいと思います。

 
勘違いしているかもしれませんので、敢えて書きます。
 
For〜NextとDo 〜Loopで論理が逆になっているわじぇではなく、
Do〜Loopの中の処理に「論理が逆」があるということです。
 
以下のコードの
「TEST1」は、あなたの記述と同じ論理のコードです。
「TEST2」は、処理順番を変更したコードです。
Sub test1()
Dim RNG As Range, FIRSTADD As String
Dim RH As Range

    Set RH = Range("A1:A5")
    Set RNG = RH.Find("AAA")
    FIRSTADD = RNG.Address
    Debug.Print "@ " & FIRSTADD
    Do
        Set RNG = RH.FindNext(RNG)
        Debug.Print "A " & RNG.Address
        If RNG.Address = FIRSTADD Then Exit Do
    Loop
End Sub

Sub test2()
Dim RNG As Range, FIRSTADD As String
Dim RH As Range

    Set RH = Range("A1:A5")
    Set RNG = RH.Find("AAA")
    FIRSTADD = RNG.Address
    Debug.Print "@ " & FIRSTADD
    Do
        Debug.Print "A " & RNG.Address
        Set RNG = RH.FindNext(RNG)
    Loop Until RNG.Address = FIRSTADD
End Sub

 
この違いを正しく理解しましょう。
 

投稿日時: 21/08/04 10:08:07
投稿者: chiii1519

WinArrow 様
 
ありがとうございます。
A1、A2、A5に"AAA"を入力し、実際に動きを確認させて頂きました。
改めて質問よろしいでしょうか。
 
結果
test1では @ &A&2 A $A&5、&A&1、&A&2
test2では A &A&2 A &A&2、$A&5、&A&1
と表示されました。
 
test1では、"AAA"と一致する1番目のセル(A2)と【一致したら】ループを抜ける。
 
test2では、処理 

引用:
Set RNG = RH.FindNext(RNG)

部分で"AAA"と一致する1番目のセル(A2)と【一致するセル】からループを開始し、
条件 
引用:
Loop Until RNG.Address = FIRSTADD

部分で条件が正しくない場合ループを抜ける。
 
という解釈で間違いないでしょうか。
 
とすると、分からないのが
・なぜ@が"AAA"の入力されているうちの先頭にあたるA1セルではなく、A2セルなのか。
・test2の条件部分の解釈を恐らく誤っている為にAの結果について理解出来ません。
 
手元の書籍やネット等で調べましたが、中々理解に至りませんでした・・・
重ね重ね申し訳ありませんが、ご教示いただけますと幸いです。

回答
投稿日時: 21/08/04 11:01:35
投稿者: WinArrow
投稿者のウェブサイトに移動

chiii1519 さんの引用:

部分で条件が正しくない場合ループを抜ける。
 
という解釈で間違いないでしょうか。

 
Loop Until
は、「条件が成立するまで、ループする」
という命令ですから、
>条件が正しくない場合ループを抜ける。
は、間違いと思います。
 
chiii1519 さんの引用:

・なぜ@が"AAA"の入力されているうちの先頭にあたるA1セルではなく、A2セルなのか。

 
FINDメソッドは、一般機能の検索コマンドと同じです。
一般機能の検索コマンドで、「全て検索」を指定すると、検索範囲の先頭セル(今回はA1)は最後に表示されます。
つまり、検索範囲の先頭セルの次から検索されるということです。
Doループを抜ける条件を、ループの中で、IF文を使用しても結果は同じになりますが、
UNTIL とか WHILEを使った方がすっきりするし、多分、微妙ではあるが、処理時間が早くなると思います。

回答
投稿日時: 21/08/04 11:14:32
投稿者: WinArrow
投稿者のウェブサイトに移動

chiii1519 さんの引用:

結果
test1では @ &A&2 A $A&5、&A&1、&A&2
test2では A &A&2 A &A&2、$A&5、&A&1
と表示されました。

正しくは、
test1では @ &A&2 A $A&5、&A&1、&A&2
test2では @ &A&2 A &A&2、$A&5、&A&1
ですよね・・・・
 
TEST1では、最初に検索されたセルを最後に処理することになります。
Test2では、最初に検索されたセルを最初に処理することになります。
 
論理が逆と書いたのは、言い過ぎでしたが、
Test2の方が、分かりやすいと考えたからです。
 

回答
投稿日時: 21/08/04 11:37:31
投稿者: WinArrow
投稿者のウェブサイトに移動

何度もレスしますが、
 
TEST1のコードが間違っていました。
あなたの考えと同じコードは
↓です。
 

Sub test1()
Dim RNG As Range, FIRSTADD As String
Dim RH As Range

    Set RH = Range("A1:A5")
    Set RNG = RH.Find("AAA")
    FIRSTADD = RNG.Address
    Debug.Print "@ " & FIRSTADD
    Do
        Set RNG = RH.FindNext(RNG)
        If RNG.Address = FIRSTADD Then Exit Do
        Debug.Print "A " & RNG.Address
    Loop
End Sub

 
このコードを実行すると
@ &A&2 A $A&5、&A&1
となるはずです。
 
検証してみてください。

投稿日時: 21/08/06 08:54:15
投稿者: chiii1519

WinArrow 様
 
漸く理解出来ました。
ご丁寧に教えていただき大変感謝しています。
 
お忙しい中ご協力頂きありがとうございました。

投稿日時: 21/08/06 08:56:35
投稿者: chiii1519

WinArrow 様
 
漸く理解出来ました。
ご丁寧に教えていただき大変感謝しています。
 
お忙しい中ご協力頂きありがとうございました。