エクセルマクロで住所を比較しコードを代入したい

質問

エクセル2003を使っています。
シート1に
　A列にコード
　B列に都道府県名
　C列に市区町村名
の表があります。

シート2は
　B列に住所
が入っています。

このシート2の住所とシート1の都道府県名と市区町村名をマッチングさせて
シート1のコードをシート2のC列に代入したいのですが

マクロを使ってやりたいのですがどうしたらよいでしょうか?　よろしくお願いします。

回答No.4

tom04 · ベストアンサー

No.1です！

＞この場合代入される側のSet ws2 = Worksheets("sheet2")の
シート名を指定しなくてもいい方法はありませんか
シート名が固定ではないので開いているシートで動作するように
したいのですが

とありましたので・・・

参照先のBookは開いた後のコードになります。
参照先のBook名は仮に「book2」とします。

そしてws2を設定しなくてもいいように、画面左下にある操作したいSheet（今回はSheet2とします）のSheet見出し上で右クリック
→　コードの表示　→　VBE画面が出ますので、
訂正した↓のコードをコピー＆ペーストしてマクロを実行してみてください。

Sub test() 'この行から
Dim i, j As Long
Dim wb As Workbook
Dim ws1 As Worksheet
Set wb = Workbooks("book2") '←「book2」の部分はBook名を！
Set ws1 = wb.Worksheets("sheet1") '←「sheet1」はbook2の参照先Sheet名を！
For i = 2 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
For j = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(j, 2) Like ws1.Cells(i, 2) & ws1.Cells(i, 3) & "*" Then
Cells(j, 3) = ws1.Cells(i, 1)
End If
Next j
Next i
End Sub 'この行まで

こんな感じではどうでしょうか？m(__)m

お礼

ありがとうございます、本当に助かりました。

回答No.3

imogasi

質問には実例を挙げて質問すべきだ。
何が難しいのかわかって質問しているのか。
シート２の住所と
シート１のB列に都道府県名＋　C列に市区町村名は、同じ体系なのか。何も書かないで質問になら無い。
住所と言えば、普通は丁目番地まで連想するが、データはどうなんですか。
外に住所で都道府県が省かれているものも散在するとかも面倒になる
含まれる形である場合は、シート１を検索するのは、それなりに難しくなる。
住所が東京都調布市までになっているなら、ＶＢＡでもＶＬＯＯＫＵＰ関数を使って、関数と同じように出来る。
丁目番地つきの場合は、丁目番地をはずす（除く）ことは何か特別の形式（数字とハイフンに限るとか）が無いと分離が難しい。

回答No.2

rukuku

こんばんは
原始的な方法です。

準備として、シート1のD2に
=B2&C2
以下、シート1のデータがある行だけオートフィルします。
（１行目はタイトル行であると想定しています）

また、シート2のB列のデータに余計なスペースが含まれている場合には前もって取り除いておいてください。

Like演算子を使うと特定の文字列が含まれるか判定できます。

Sub 住所コード()
　Dim DataLastLine As Long 'シート１の最終行
　Dim ResultLastLine As Long 'シート２の最終行
　Dim DataLine As Long 'シート１の各行
　Dim ResultLine As Long 'シート２の各行

　ThisWorkbook.Activate

　DataLastLine = Worksheets("シート1").[A65536].End(xlUp).Row
　ResultLastLine = Worksheets("シート2").[B65536].End(xlUp).Row

　For DataLine = 2 To DataLastLine
　　For ResultLine = 2 To ResultLastLine
　　　If Worksheets("シート2").Cells(ResultLine, "B") Like "*" & Worksheets("シート1").Cells(DataLine, "D") & "*" Then _
　　　　Worksheets("シート2").Cells(ResultLine, "C") = Cells(DataLine, "A")
　　Next ResultLine
　Next DataLine

End Sub

回答No.1

tom04

こんばんは！
外していたらごめんなさい。

関数でもできそうな感じですが、VBAをご希望のようなので・・・
一例です。

↓の画像のような感じでSheet1には都道府県・市区町村名のみで
Sheet2のB列には都道府県からの正確な住所が入っているものとします。

↓のコードを標準モジュールにコピー＆ペーストしてマクロを実行してみてください。

Sub test() 'この行から
Dim i, j As Long
Dim ws1, ws2 As Worksheet
Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")
For i = 2 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
For j = 2 To ws2.Cells(Rows.Count, 1).End(xlUp).Row
If ws2.Cells(j, 2) Like ws1.Cells(i, 2) & ws1.Cells(i, 3) & "*" Then
ws2.Cells(j, 3) = ws1.Cells(i, 1)
End If
Next j
Next i
End Sub 'この行まで

参考になれば良いのですが
的外れならごめんなさいね。m(__)m

補足

すいません、期待通りの動きになったのですが
これを同じブック内ではなく違うブックから参照する場合の
指定法はどうすればよろしいでしょうか？
この場合代入される側のSet ws2 = Worksheets("sheet2")の
シート名を指定しなくてもいい方法はありませんか
シート名が固定ではないので開いているシートで動作するように
したいのですが。