マクロ：修正したのに元にもどってしまう。＆違うシートを参照する

質問

お世話になっています。
「××シートのセルに文字列を記入したとき、●●という別シートの中のデータを一列チェックして一致していたら、書き込んだセルの隣のセルに××という文字を書き込む」という処理を考えています。
●●というシートには、Ａ列とＢ列にランダムな単語が並んでいます。
例えば、
Ａ　　　Ｂ
スズキ　神奈川
サトウ　千葉
タナカ　東京

という感じです。
××シートでは、
Ａ　　　Ｂ
スズキ

と書き込んだら
Ａ　　　Ｂ
スズキ　神奈川

と自動入力してほしいのです。
今のところ、

Dim sName As String
sName = Target.Value
For i = 1 To Sheets("●●").Range("A1").End (xlDown).Row
If sTitle = Sheets("●●").Cells(i, 1).Value Then
※ Offset(0, 1).Value = Sheets("●●").Cells(Resize(1, 0)).Value
　　　　Exit For
　　　Else
　　　　　　　Target.Interior.ColorIndex = xlNone
　　　　　End If
　　　　 Next i
という処理を考えていますが、※というところが自信ありません。

あと、エラーが出てコードを修正してコンパイルをかけると、修正する前のコードに戻ってしまい、同じエラーメッセージが出てきます。
修正後に上書き保存をしたり、一度ブックを閉じてまた開いて・・・としても駄目でした。
このせいで作業がだいぶ遅れるのですが、修正前に戻らないようにする方法はないでしょうか？

回答No.4

Wendy02 · ベストアンサー

こんにちは。

イベント・ドリブン型だと思いますが、質問で出されただけではよく分からない部分がありました。なお、それは、ループでは探しません。

'××シートのモジュールに入れてください。

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim i As Variant
　　If Target.Column <> 1 Then Exit Sub 'A列(1列)以外は除外
　　If Target.Value = "" Then Exit Sub
　　If Target.Count > 1 Then Exit Sub
　　i = Application.Match(Target.Value, Worksheets("●●").Range("A:A"), 0)
　　Application.EnableEvents = False
　　If Not IsError(i) Then
　　　　Target.Offset(, 1).Value = _
　　　　Worksheets("●●").Range("A:A").Cells(i).Offset(, 1).Value
　　End If
　　Application.EnableEvents = True
End Sub

回答No.5

Wendy02

#4 です。

●●シートには、氏名と出身地が、A:B 列にあって、

××シートに対しては、
>５０人分の人名がシートいっぱいにランダムに書き込まれ

なら、

If Target.Column <> 1 Then Exit Sub 'A列(1列)以外は除外

を外せばよいのでは？

回答No.3

zap35

他の方も書かれていますが、マクロよりVLOOKUP関数の方が簡単そうですね。それでもマクロがよいのですか？

＞　Offset(0, 1).Value = Sheets("●●").Cells(Resize(1, 0)).Value
は
　Target.Offset(, 1).Value = Sheets("●●").Cells(i, 2).Value
でしょう

でもFor～Next文で処理すると遅くなると思いますので、Findメソッドを使う方が実用的だと思います。こんな感じですが詳しくはヘルプを見てください

Dim trg As Range
　Set trg = Sheets("●●").Columns(1).Find(What:=sName, _
　　　　　　　　Lookin:=xlValues, Lookat:=xlWhole)
　If trg Is Nothing Then
　　Msgbox "該当なし"
　Else
　　Target.Offset(, 1).Value = trg.Value
　End If

補足

１の方の補足欄に詳細を書きました。
よろしくお願いいたします。

回答No.2

chie65536

因みに、B1セルを選択して、セルの右下のカドを下方向に必要な行までドラッグして「セルのコピー」ってやれば、

B1：=VLOOKUP(A1,●●!A:B,2,0)
B2：=VLOOKUP(A2,●●!A:B,2,0)
B3：=VLOOKUP(A3,●●!A:B,2,0)
B4：=VLOOKUP(A4,●●!A:B,2,0)
B5：=VLOOKUP(A5,●●!A:B,2,0)
B6：=VLOOKUP(A6,●●!A:B,2,0)

って状態になるので、そのまま、A1、A2、A3、A4、A5…と入力しましょう。B列に勝手にデータが表示されてく筈です。

補足

１の方の補足欄に詳細を書きました。
よろしくお願いいたします。

回答No.1

chie65536

マクロなんか不要。

A1に「スズキ」と入れたら
B1に「=VLOOKUP(A1,●●!A:B,2,0)」って入れましょう。

そしたらB1に「神奈川」ってでます。

で「B1の中身を、式でなく、値にしたい」って場合は、

B1セルを選択して「編集」「コピー」
B1セルを選択したまま「編集」「形式を選択して貼り付け」「貼り付け - 値」を選択して「ＯＫ」

ってやれば、式の結果の「値」がB1セルの中身になります。

補足

書きかたが悪くて申し訳ないのですが、●●シートと××シートのデータの並びが一致するとは限りません。

●●シートが
Ａ　　　Ｂ
スズキ　神奈川
サトウ　千葉
タナカ　東京

であったとしても、

××シートに
Ａ　　　Ｂ
サトウ　千葉
スズキ　神奈川
タナカ　東京

と記入される可能性があります。
質問では３人分しかデータがありませんが、実際は５０人分以上あります。
５０人分の人名がシートいっぱいにランダムに書き込まれ、その隣のセルにかならずその人の出身地を表示させたいイメージです。

Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　Ｅ　　　Ｆ
キムラ　埼玉　　イシダ　山梨　ヤマダ　東京　
スズキ　神奈川　サトウ　千葉　オオタ　奈良
タナカ　東京　　ムトウ　大阪　マツダ　千葉
　：　　　　　　　：　　　　　　：

ひとり書き込むごとに別のシートを参照するのは面倒なので、最初に一覧表をつくり、マクロが勝手に参照してくれないかな？と思いました。
その場合も、VLOOKUP関数で出来ますか？