2つのブックの列と列を比較し、一致すれば、片方のブックのA列にある番号を転記したい

質問

いつもお世話になっております。
また、Excelで悩んでしまいまして(>_<)
今、『観光地.xls』というブックのA列には、以下のようなデータが記載されています。

夏井川渓谷
オルチャ渓谷
御岳渓谷
ハラワ渓谷
福知渓谷
イアオ渓谷
チャリン渓谷
ワイメア渓谷
羽山渓谷
ヴァッハウ渓谷

そしてもう一つの、『日本の渓谷.xls』というブックのB列には、

夏井川渓谷
ウェルサンピア京都
御岳渓谷
昭和記念公園レインボープール
福知渓谷
大磯ロングビーチ
羽山渓谷

と、日本の渓谷のデータと日本のプールのデータが混ざって並んでおりまして、隣のA列には、渓谷のデータにのみ、k1,k2・・・と、番号を振っております。プールのデータの隣は、空白になっています。

この状態から、『観光地.xls』のA列と、『日本の渓谷.xls』のB列を比較し、データが一致すれば、『日本の渓谷.xls』の渓谷データのすぐ隣、A列に記載してある番号を、『観光地.xls』のL列に、転記したいのです。

『観光地.xls』のB～K列には、他の観光地のデータがずらりと記載されているので、空白のL列に、『観光地.xls』のA列の日本の渓谷データ(＝『日本の渓谷.xls』のB列の渓谷データ)に、対応する番号を付したいのです。

データが膨大ですので、マクロを使うのが最も合理的だと思うのですが、以前、似たような質問↓
http://okwave.jp/qa5015030.html
を解決してくださいまして、この時の応用で何とかなる、「一致したら斜線を引くのではなく、同シートのL列に、『日本の渓谷.xls』のA列のデータを転記するよう、指示すればいいのかな」と思い、マクロを書いてみました。

Sub keikoku()
◆変数名の入力ミスによる動作の不具合を防ぐために変数名を宣言。
Dim KeikokuSearch1 As Range, KeikokuSearch2 As Range, k As Range, kk As Range

◆Activateメソッドを実行。
With Workbooks("観光地.xls")
.Activate
.Sheets("Sheet1").Activate
End With

◆Rangeオブジェクトで範囲を選択
With Workbooks("観光地.xls").Sheets("Sheet1")
Set KeikokuSearch1 = .Range(.Range("A1"), .Range("A1").End(xlDown))
End With

With Workbooks("日本の渓谷.xls").Sheets("Sheet1")
Set KeikokuSearch2 = .Range(.Range("B1"), .Range("B1").End(xlDown))
End With

◆For Each...In...Nextステートメントで、データの一致を調べる。
For Each k In KeikokuSearch1
For Each kk In KeikokuSearch2
If k.Value = kk.Value Then

・・・

と、この辺りで立ち往生してしまいました(;_;)

If Then～Else～End Ifステートメントで、KeikokuSearch1とKeikokuSearch2のデータが一致すれば、条件を満たす場合の処理として、KeikokuSearch2(『日本の渓谷.xls』のB列)の隣にあるA列のデータを、KeikokuSearch1(『観光地.xls』のA列)から11マス隣のL列にコピーする、という風に指示したいのですが・・・ココから先のコードが、おぼつかない状況です(>_<)

やはりマクロは難しいです。皆様のお力をお借りいただければ幸いです。
よろしくお願いします<m(__)m>

回答No.4

merlionXX · ベストアンサー

merlionXXです。

> 何かRangeの中にRangeが入ってて不思議です( ゜д゜)

Rangeの中にRangeが入るのはちっとも不思議ではありません。

Range("A1:A10").Select　は
Range("A1", "A10").Select　とも書けます。（ただし　Range("A1, A10").Selectだと別な意味になりますので注意）
Range(Cells(1, 1), Cells(10, 1)).Select　とも書けるし
Range(Cells(1, "A"), Cells(10, "A")).Select　でも
Range(Range("A1"), Range("A10")).Select　でもOKなんです。

> 「A:A」みたいにコロンを間に入れて全選択みたいなこと、できないんですかね・・・

End(xlUp)やEnd(xlDown)って、何をするコードかわかります？
End(xlUp)はCtrl+↑　End(xlDown)はCtrl+↓で飛ぶ先のセルを取得しているんです。
Rows.Countは全行数ですからエクセル2000や2003 の場合は65536です。（バージョンにより行数が変わるのできめ打ちせず、Rows.Countを使用しています。）

つまり、Range("A" & Rows.Count).End(xlUp))　とは、A65536セルからCtrl+↑　で飛んだ先のセルです。
だから、Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))は、A1セルからA列のデータのある最終セルの範囲ということになります。
この場合、Ctrl+↑　で飛ばす基点を定めなければなりませんので、基点となるセルが指定されないRange("A:A").End(xlUp)や、Range("A60000,A1").End(xlUp)という書き方では意味がないのです。
もちろん範囲をRange("A:A")のように列全部を指定することも可能ですが、総当りで検索させたいわけですから、そんな指定をするととんでもない時間をかけて、空白の範囲を無駄な検索をし続けることになります。

お礼

なるほど、すごくわかりやすいです、ありがとうございます！
でも一つだけ疑問が残ります、最後に一つだけ、お聞きしてよろしいでしょうか(>_<)

.Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))

を、

.Range(Range("A1"), Range("A1" & Rows.Count).End(xlUp))

に書きかえると、うまく動作しませんでした(ToT)
もちろん、merlionXXさんの説明のおかげで、意味は把握したつもりです。

.Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))

は、「A1というセルと、A列の全行数(Rows.Count)の内、最も下の行から飛んで(xlUp)最初にデータがあるセル、その2つのセルが繋ぐ範囲を選択する」という意味のコードだと・・・(^_^;)

そう考えると、Range("A" & Rows.Count)における「"A"」は、書き換えたマクロがエラーになったことからも、「A1」とは別物みたいだと解釈しました。ただ、Range("A" & Rows.Count)における「"A" &・・・」というコードの書き方に関する説明は、私が持っているマクロの本や、ネットでいろいろ検索してみたのですが、見つかりませんでして・・・この「"A" &」の意味を、お暇な時で構いませんので、教えていただければ幸いです(>_<)

回答No.5

merlionXX

> Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
> は、「A1というセルと、A列の全行数(Rows.Count)の内、最も下の行から飛んで(xlUp)最初にデータがあるセル、その2つのセルが繋ぐ範囲を選択する」という意味のコードだと・・・

選択（Select）はしていません。単におっしゃる範囲を指定しただけです。
また、「"A" &」の意味ですが、エクセル2003までならRows.Countは65536行ですから
Range("A" & Rows.Count) と Range("A65536")は同じ意味です。
Cells(Rows.Count,"A")と書くことも出来ます。
これでおわかりですね、Range("A1" & Rows.Count)なら、Range("A165536")になってしまい存在しないセル番地です。

ところで、http://Okwave.jp/qa5047671.html はまだ解決していないのですか？

お礼

あっ、なるほど、わかりました！
もう一つの質問の方ももちろん、merlionXXさんのおかげで解決しているのですが、イモガシさん流のやり方だけ上手くいかなかったので気になってたのです・・・なんとなく、いろんな方法を知っておいた方がいい気がしまして。でもほんと、いつも丁寧にありがとうございます(^_^;)

回答No.3

merlionXX

merlionXXです。
dj-sさん、大夫進歩しましたね。
さて、ではわたしの書いたコードが指定する範囲と、dj-sさんの範囲とではどこがちがうのか、以下のアドレスを表示させるコードで試してみてください。 .Range("A:A").End(xlUp)では、常にA1セルだけになってしまうと思います。

Sub TEST()
With Workbooks("観光地.xls").Sheets("Sheet1")
MsgBox "dj-sさんの範囲は、" & .Range("A:A").End(xlUp).Address
MsgBox "merlionXXの範囲は、" & .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp)).Address
End With
End Sub

お礼

ありがたいお言葉です。merlionXXさんのご指導なくしてはここまでこれませんでした！
マクロを実行してみたところ、確かに私のは「$A$1」と出たのに、merlionXXさんの方は「$A$1:$A$14」(14行目にポツンとデータを入れてみました)と出てきました！
また、コードの一部を、

MsgBox "dj-sさんの範囲は、" & .Range("A1,A60000").End(xlUp).Address

と書きかえて実行したところ、これまた「$A$1」になってしまいました・・・

MsgBox "dj-sさんの範囲は、" & .Range("A60000,A1").End(xlUp).Address

と書きかえると、「$A$14」は出てきたのですが、これは単一セルですよね(ToT)
どうしてですかねぇ、merlionXXさんが書いてくださったコードは、何かRangeの中にRangeが入ってて不思議です( ゜д゜)
「A:A」みたいにコロンを間に入れて全選択みたいなこと、できないんですかね・・・
http://q.hatena.ne.jp/1215786204
ココ↑では、A列を全部選択する際に「A:A」を使用しているのに、「Rangeプロパティとしては使ってはいけないのかな？」と思い、

MsgBox "dj-sさんの範囲は、" & .Range("A:A").Address

と、意地でもコロンを使ってデータを全て取得しようと「.End(xlUp)」を取り除いみたのですが、これまた「$A:$A」って出ちゃいました(>_<)
「$A:$A」だと、データがどこにあるのか全く分かりませんよね(笑)
はぁ～難しいですねマクロは(^_^;)

回答No.2

merlionXX

Sub keikoku2()
'◆変数名の入力ミスによる動作の不具合を防ぐために変数名を宣言。
Dim KeikokuSearch1 As Range, KeikokuSearch2 As Range, k As Range, kk As Range
'◆Rangeオブジェクトで範囲を選択
With Workbooks("観光地.xls").Sheets("Sheet1")
Set KeikokuSearch1 = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
End With
With Workbooks("日本の渓谷.xls").Sheets("Sheet1")
Set KeikokuSearch2 = .Range(.Range("B1"), .Range("B" & Rows.Count).End(xlUp))
End With
'◆For Each...In...Nextステートメントで、データの一致を調べる。
For Each k In KeikokuSearch1
For Each kk In KeikokuSearch2
If k.Value = kk.Value Then
k.Offset(0, 11).Value = kk.Offset(0, -1).Value
End If
Next kk
Next k
End Sub

ご提示のコードを、データの途中に空白があっても良いように一部を変えました。

お礼

うまくいきました！いつもありがとうございます(>_<)
もし、kとkkのデータが一致すれば、kから11列右のL列のデータと、kkから1列左のデータを同じにする、というわけですか～( ゜д゜)
merlionXXさんが修正したのもココ↓
http://www.niji.or.jp/home/toru/notes/8.html
を読み、なんとなく理解できました、下からセルを選択しないと、最終行がどこにあるのかわからないんですね(^_^;)
ただ、Rangeオブジェクトがどうも理解できないのですが・・・A列とB列の全部を選択するのなら、

Set KeikokuSearch1 = .Range("A:A").End(xlUp)
Set KeikokuSearch2 = .Range("B:B").End(xlUp)

でも大丈夫だと思って、書き換えた後に試してみたところ・・・何も起きませんでした(ToT)

オブジェクト.Range(セル範囲)
または
オブジェクト.Range(開始セル, 終了セル)

ですよね？オブジェクトは省略できるらしいのでいいとして、なぜmerlionXXさんの

.Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
.Range(.Range("B1"), .Range("B" & Rows.Count).End(xlUp))

はうまく作動するのに、私が書きかえた方はダメなのでしょうか？
すいません、お暇な時で構いませんので、ご回答いただきたいです。
よろしくお願いします<m(__)m>

回答No.1

n-jun

Offsetプロパティ
http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_cell.html#offset

kk.Offset( , -1).Copy k.Offset( ,11)

と言う感じではないかと。

お礼

そうですよね、知りませんでした・・・ありがとうございます(^_^;)