WorksheetFunction.matchで取得したデータが複写先に正しく反映しない

Question

以下のマクロについて教えてください。
最初のデータ入力時にはsheet1、sheet2ともに同じ管理番号があります。（1枚目の画像参照）
その後sheet1の工事会社を変えて管理番号を削除することがあります。
その場合マクロを実行するとsheet1に違った管理番号のデータが反映されることがあります。(2枚目の画像）
このようなパターンでもsheet2の管理番号に対応するデータを、sheet1に正しく反映させるのにはどのように対処すればいいのでしょうか？

sheet1＝管理台帳
sheet2＝（Ｂ社）管理台帳

Sub 工事会社工程コピー()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws1Key As Range
Dim i As Long
Dim r As Long
MsgBox "管理番号は入力済みですか？"
ActiveSheet.AutoFilterMode = False
On Error Resume Next

Set ws1 = Worksheets("管理台帳")
Set ws1Key = ws1.Range("C10", ws1.Cells(Rows.Count, "C").End(xlUp)) '
Set ws2 = Worksheets("（Ｂ社）管理台帳")

For i = 10 To ws2.Cells(Rows.Count, "C").End(xlUp).Row
   r = WorksheetFunction.match(ws2.Cells(i, 3).Value, ws1Key, 0) + 9
    ws1.Range(ws1.Cells(r, 4), ws1.Cells(r, 9)).Value _
        = ws2.Range(ws2.Cells(i, 4), ws2.Cells(i, 9)).Value
Next

End Sub

Comments

[coco_bauer]

「その後sheet1の工事会社を変えて管理番号を削除する」という操作をしたら、sheet1の内容はどうなるのですか？　「その場合マクロを実行するとsheet1に違った管理番号のデータが反映されることがあります」ということは、sheet1に正しい管理番号のデータが反映される場合と、違った管理番号が反映されることがあるという事ですか？　正しいデータが反映される場合のsheet1の内容と、違った管理番号が反映される場合のsheet1の内容を示してください。

[mowmowmilk1]

ご検討いただきありがとうございます。
2番目の画像にもありますが、12行目のデータは正しく反映されませんでした。そのほかの行は正しく反映されました。

Answer 1

なぜかはよくわかりませんが（動作確認してないので）、
ステップインで1行づつ実行しながら、
ローカルウィンドウでそれぞれの変数の値が期待通りに、
変化しているかを確認したらわかるかと思います。

参考サイト＞＞
プロパティ、メソッドの探り方 マクロ記録とＦ１のHelpを使う

Sub test2()
    Const cmyCCount As Long = 6
    Dim rngListB As Range
    Dim rngTable As Range
    Dim c As Range

    With Worksheets("（Ｂ社）管理台帳").Range("B9").CurrentRegion
        Set rngListB = Intersect(.Cells, .Offset(1))
    End With
    With Worksheets("管理台帳").Range("B9").CurrentRegion
        Set rngTable = Intersect(.Cells, .Offset(1))
    End With

    For Each c In rngTable.Columns(2).Cells
        If Not IsEmpty(c.Value) Then    '値が空白でない時に処理
            r = 0                           '変数の初期化
            On Error Resume Next
            r = WorksheetFunction.Match(c.Value, rngListB.Columns(2), 0)
            On Error GoTo 0
            If r > 0 Then
                c.Offset(, 1).Resize(, cmyCCount).Value _
                        = rngListB.Cells(r, 3).Resize(, cmyCCount).Value
            End If
        End If
    Next
End Sub

ポイントとしては、
1）on errorステートメントは必要以外の範囲に限定して適用してください。
エラーを全体で無視すると、必要な時に不具合の原因が分からなくなります。
2）If文で不要な処理は除外しましょう。
無駄なループを減らし処理速度が改善します。
3）変数を使いまわす時は初期化を忘れずに。
前の値が残ったままだと、意図しない動作の原因になります。
4）セルの位置は、指定のセルからの相対位置でも示せます。
一旦、変数にセル範囲を取り込んだなら、
改めてシート全体の位置から示さなくても、
変数にあるセルを基準に相対位置で示せるといいと思いました。

注）サンプルコードは、動作確認していません。
上手く動かなければごめんなさいです。

参考になれば。。。

Comments

[mowmowmilk1]

早速の回答ありがとうございました。無事解決することができました。今後もなにかありましたらお力を貸してください。ありがとうございました。