マクロと複数文字列一括置換の（私には）複雑な話

質問

ウィンドウズ98、エクセル2000で作業中です。

駅名を数字に置換するマクロを作っています。

▽こんな感じです。（マクロの記録でエクセルが勝手に書いてくれました）
Selection.Replace What:="西日暮里", Replacement:="5", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False

1つのセルに1つの駅名を書いればうまく機能するのですが、複数の
駅名を入れると置換されなくなってしまいました。

｜西日暮里｜　→　｜5｜

｜西日暮里:新宿｜　→　｜西日暮里:新宿｜

置換に全文一致を使っていたのでそれを外すと新たな問題が,,,

｜西日暮里:新宿｜　→　｜西4:1｜

西日暮里は5に置換されるはずが、4に置換され、更に西が残りました。
西日暮里の前に、日暮里を4に置換（全文一致ではない）する行があったことが問題でした。

これをうまいこと回避する手はありますでしょうか？
説明に明確でない所があれば、指摘して下さい。
ちなみにマクロは素人で、今回初めて使います。

宜しくお願い致します。

回答No.2

nishi6

かなり日にちも経って無意味ですかね。ユーザー定義関数を作ってみましたので参考に投稿します。
多分、駅名を変換するデータがあると思って作っています。前提は、シートのどこかに変換テーブル、例えば、
　新宿　　　　1
　京成幕張　　2
　新宿三丁目　3
　日暮里　　　4
　西日暮里　　5
　蒲田　　　　6
　京急蒲田　　7
　幕張　　　　8

のようなものがあるとして、これに範囲名『data』を付けます。
使い方は、A1に駅名があれば、別のセルで　=EkimeiOkikae(A1) とします。
セルには『:』で分けられた駅名が何個あってもかまいません。

以下を標準モジュールに貼り付けます。
Public Function EkimeiOkikae(Ekimei As String)
　　Dim wkEkimei As String 'セルの内容(ワーク)
　　Dim wkEkiElm As String 'セルの駅名の1つ
　　Dim pot As Integer '2つの駅名を分ける位置

　　Application.Volatile '自動再計算関数にする
　　wkEkimei = Ekimei & ":" '駅名が1つ、2つ以上を同じ形式にする
　　pot = InStr(wkEkimei, ":")
　　While pot > 0
　　　　wkEkiElm = Left(wkEkimei, pot - 1) '1つの駅名
　　　　wkEkiElm = Application.VLookup(wkEkiElm, Range("data"), 2, False) 'dataから検索
　　　　EkimeiOkikae = EkimeiOkikae & wkEkiElm & ":" '検索結果
　　　　wkEkimei = Right(wkEkimei, Len(wkEkimei) - pot) '複数の場合の次の駅名

　　　　pot = InStr(wkEkimei, ":")
　　Wend
　　EkimeiOkikae = Left(EkimeiOkikae, Len(EkimeiOkikae) - 1) '最後の『:』を除く
End Function

お礼

丁寧なご回答、
ありがとうございます。

また、お礼が遅くなってすいません。
ご回答頂いたのもお忘れかと思うほど時間が経ってしまいました。

＞多分、駅名を変換するデータがあると思って作っています。
あります。
難しそうですが、チャレンジしてみます。

「転ばぬ先の杖」とはいいつつも自分の必要な知識から虫食いのように
勉強しています。

来年２月からは本格的に・・・。と密かに考えてはいるのですが・・・。

本当にありがとうございました。