質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.23%

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

解決済

回答 1

投稿

  • 評価
  • クリップ 1
  • VIEW 432

mowmowmilk1

score 11

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

sheet1=管理台帳
sheet2=(B社)管理台帳

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("(B社)管理台帳")

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
イメージ説明
イメージ説明

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • coco_bauer

    2019/06/02 14:12

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

    キャンセル

  • mowmowmilk1

    2019/06/03 23:06

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

    キャンセル

回答 1

checkベストアンサー

0

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

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

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

    With Worksheets("(B社)管理台帳").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)セルの位置は、指定のセルからの相対位置でも示せます。
一旦、変数にセル範囲を取り込んだなら、
改めてシート全体の位置から示さなくても、
変数にあるセルを基準に相対位置で示せるといいと思いました。

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

参考になれば。。。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/06/03 23:06

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.23%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる