Option Explicit
 
Sub 業者情報設定()
    Dim sh1 As Worksheet        '業者一覧シート
    Dim sh2 As Worksheet        '配布用シート
    Dim dicT As Object          'ディクショナリ　キー：業者名　値：業者一覧シートの行番号
    Dim maxrow1 As Long         '業者一覧シートの最大行
    Dim maxrow2 As Long         '配布用シートの最大行
    Dim row1 As Long            '業者一覧シートの行番号
    Dim row2 As Long            '配布用シートの行番号
    Dim name As String          '業者名
    Set dicT = CreateObject("Scripting.Dictionary")     'ディクショナリ設定
    Set sh1 = Worksheets("業者一覧")
    Set sh2 = Worksheets("配布用")
    maxrow1 = sh1.Cells(sh1.Rows.Count, "A").End(xlUp).Row   '業者一覧シート　A列の最大行を取得
    '業者一覧シートの５行から最終行まで繰り返す
    For row1 = 5 To maxrow1
        name = sh1.Cells(row1, "A").Value       '業者名取得
        If name <> "" Then
            dicT(name) = row1                   'ディクショナリへ業者名と行番号を登録
        End If
    Next
    maxrow2 = sh2.Cells(sh2.Rows.Count, "A").End(xlUp).Row   '配布用シート　A列の最大行を取得
    '配布用シートの１４行から最終行まで繰り返す
    For row2 = 14 To maxrow2
        name = sh2.Cells(row2, "B").Value       '業者名取得
        If name <> "" Then
            '業者名がディクショナリに登録されているかチェックする
            If dicT.exists(name) = True Then
                '登録済みの場合の処理
                row1 = dicT(name)       '業者一覧シートの該当行を取得する
                sh2.Cells(row2, "C").Value = sh1.Cells(row1, "B").Value     '電話番号
                sh2.Cells(row2, "D").Value = sh1.Cells(row1, "C").Value     '郵便番号
                sh2.Cells(row2, "E").Value = sh1.Cells(row1, "D").Value     '住所
            Else
                '未登録の場合の処理
                sh2.Cells(row2, "C").Value = "業者一覧に未登録"
                sh2.Cells(row2, "D").Value = "業者一覧に未登録"
                sh2.Cells(row2, "E").Value = "業者一覧に未登録"
            End If
        End If
    Next
    MsgBox ("完了")
End Sub
 

T3B0aW9uIEV4cGxpY2l0CgpTdWIg5qWt6ICF5oOF5aCx6Kit5a6aKCkKICAgIERpbSBzaDEgQXMgV29ya3NoZWV0ICAgICAgICAn5qWt6ICF5LiA6Kan44K344O844OICiAgICBEaW0gc2gyIEFzIFdvcmtzaGVldCAgICAgICAgJ+mFjeW4g+eUqOOCt+ODvOODiAogICAgRGltIGRpY1QgQXMgT2JqZWN0ICAgICAgICAgICfjg4fjgqPjgq/jgrfjg6fjg4rjg6rjgIDjgq3jg7zvvJrmpa3ogIXlkI3jgIDlgKTvvJrmpa3ogIXkuIDopqfjgrfjg7zjg4jjga7ooYznlarlj7cKICAgIERpbSBtYXhyb3cxIEFzIExvbmcgICAgICAgICAn5qWt6ICF5LiA6Kan44K344O844OI44Gu5pyA5aSn6KGMCiAgICBEaW0gbWF4cm93MiBBcyBMb25nICAgICAgICAgJ+mFjeW4g+eUqOOCt+ODvOODiOOBruacgOWkp+ihjAogICAgRGltIHJvdzEgQXMgTG9uZyAgICAgICAgICAgICfmpa3ogIXkuIDopqfjgrfjg7zjg4jjga7ooYznlarlj7cKICAgIERpbSByb3cyIEFzIExvbmcgICAgICAgICAgICAn6YWN5biD55So44K344O844OI44Gu6KGM55Wq5Y+3CiAgICBEaW0gbmFtZSBBcyBTdHJpbmcgICAgICAgICAgJ+alreiAheWQjQogICAgU2V0IGRpY1QgPSBDcmVhdGVPYmplY3QoIlNjcmlwdGluZy5EaWN0aW9uYXJ5IikgICAgICfjg4fjgqPjgq/jgrfjg6fjg4rjg6roqK3lrpoKICAgIFNldCBzaDEgPSBXb3Jrc2hlZXRzKCLmpa3ogIXkuIDopqciKQogICAgU2V0IHNoMiA9IFdvcmtzaGVldHMoIumFjeW4g+eUqCIpCiAgICBtYXhyb3cxID0gc2gxLkNlbGxzKHNoMS5Sb3dzLkNvdW50LCAiQSIpLkVuZCh4bFVwKS5Sb3cgICAn5qWt6ICF5LiA6Kan44K344O844OI44CAQeWIl+OBruacgOWkp+ihjOOCkuWPluW+lwogICAgJ+alreiAheS4gOimp+OCt+ODvOODiOOBru+8leihjOOBi+OCieacgOe1guihjOOBvuOBp+e5sOOCiui/lOOBmQogICAgRm9yIHJvdzEgPSA1IFRvIG1heHJvdzEKICAgICAgICBuYW1lID0gc2gxLkNlbGxzKHJvdzEsICJBIikuVmFsdWUgICAgICAgJ+alreiAheWQjeWPluW+lwogICAgICAgIElmIG5hbWUgPD4gIiIgVGhlbgogICAgICAgICAgICBkaWNUKG5hbWUpID0gcm93MSAgICAgICAgICAgICAgICAgICAn44OH44Kj44Kv44K344On44OK44Oq44G45qWt6ICF5ZCN44Go6KGM55Wq5Y+344KS55m76YyyCiAgICAgICAgRW5kIElmCiAgICBOZXh0CiAgICBtYXhyb3cyID0gc2gyLkNlbGxzKHNoMi5Sb3dzLkNvdW50LCAiQSIpLkVuZCh4bFVwKS5Sb3cgICAn6YWN5biD55So44K344O844OI44CAQeWIl+OBruacgOWkp+ihjOOCkuWPluW+lwogICAgJ+mFjeW4g+eUqOOCt+ODvOODiOOBru+8ke+8lOihjOOBi+OCieacgOe1guihjOOBvuOBp+e5sOOCiui/lOOBmQogICAgRm9yIHJvdzIgPSAxNCBUbyBtYXhyb3cyCiAgICAgICAgbmFtZSA9IHNoMi5DZWxscyhyb3cyLCAiQiIpLlZhbHVlICAgICAgICfmpa3ogIXlkI3lj5blvpcKICAgICAgICBJZiBuYW1lIDw+ICIiIFRoZW4KICAgICAgICAgICAgJ+alreiAheWQjeOBjOODh+OCo+OCr+OCt+ODp+ODiuODquOBq+eZu+mMsuOBleOCjOOBpuOBhOOCi+OBi+ODgeOCp+ODg+OCr+OBmeOCiwogICAgICAgICAgICBJZiBkaWNULmV4aXN0cyhuYW1lKSA9IFRydWUgVGhlbgogICAgICAgICAgICAgICAgJ+eZu+mMsua4iOOBv+OBruWgtOWQiOOBruWHpueQhgogICAgICAgICAgICAgICAgcm93MSA9IGRpY1QobmFtZSkgICAgICAgJ+alreiAheS4gOimp+OCt+ODvOODiOOBruipsuW9k+ihjOOCkuWPluW+l+OBmeOCiwogICAgICAgICAgICAgICAgc2gyLkNlbGxzKHJvdzIsICJDIikuVmFsdWUgPSBzaDEuQ2VsbHMocm93MSwgIkIiKS5WYWx1ZSAgICAgJ+mbu+ipseeVquWPtwogICAgICAgICAgICAgICAgc2gyLkNlbGxzKHJvdzIsICJEIikuVmFsdWUgPSBzaDEuQ2VsbHMocm93MSwgIkMiKS5WYWx1ZSAgICAgJ+mDteS+v+eVquWPtwogICAgICAgICAgICAgICAgc2gyLkNlbGxzKHJvdzIsICJFIikuVmFsdWUgPSBzaDEuQ2VsbHMocm93MSwgIkQiKS5WYWx1ZSAgICAgJ+S9j+aJgAogICAgICAgICAgICBFbHNlCiAgICAgICAgICAgICAgICAn5pyq55m76Yyy44Gu5aC05ZCI44Gu5Yem55CGCiAgICAgICAgICAgICAgICBzaDIuQ2VsbHMocm93MiwgIkMiKS5WYWx1ZSA9ICLmpa3ogIXkuIDopqfjgavmnKrnmbvpjLIiCiAgICAgICAgICAgICAgICBzaDIuQ2VsbHMocm93MiwgIkQiKS5WYWx1ZSA9ICLmpa3ogIXkuIDopqfjgavmnKrnmbvpjLIiCiAgICAgICAgICAgICAgICBzaDIuQ2VsbHMocm93MiwgIkUiKS5WYWx1ZSA9ICLmpa3ogIXkuIDopqfjgavmnKrnmbvpjLIiCiAgICAgICAgICAgIEVuZCBJZgogICAgICAgIEVuZCBJZgogICAgTmV4dAogICAgTXNnQm94ICgi5a6M5LqGIikKRW5kIFN1Ygo=