VBA　別シートの表に含まれる文字があったら、それを返したい

初歩的な質問で申し訳ありません。
Sheet1に表1、Sheet2に表2があります。以下例を示します。

■Sheet1
番号　地名
1 ニューヨーク
2 ハワイ
3 京都
4 東京
5 ワシントン

■Sheet2
アメリカ　　日本
ニューヨーク　東京
ボストン　神奈川
ロサンゼルス　京都
ハワイ　大阪
ワシントン　福岡

↓やりたいこと
■Sheet1
番号　地名　国名
1 ニューヨーク　アメリカ
2　ハワイ　アメリカ
3　京都　日本
4　東京　日本
5　ワシントン　アメリカ

上記のように、
①Sheet1の2列目を一つずつ検索　(for文など)
②Sheet2の表のアメリカ列と一致　→真：sheet1の3列目にアメリカ
→偽：sheet1の3列目に日本

■試したコード
For i = 2 To xlDown
search= Worksheets("sheet1").Cells(i, 2).Value
If Worksheets("sheet2").Range("A1:A6").Find(What:="search") Then
Worksheets("sheet1").Cells(i,3) = "アメリカ"
Else
Worksheets("sheet1").Cells(i, 3) = "日本"

■問題点
試したコードを実施すると、オブジェクト変数またはwithブロック変数が設定されていませんとエラーが出ます(if文の箇所)。問題点が分からないので解決方法を教えて頂きたいです。

Y.H.

2019/08/28 08:19

で、何が問題になっていて、何を質問されたいのでしょうか？具体的に質問に追記ください。

kagobito

2019/08/28 08:22

失礼しました。試したコードを実施すると、オブジェクト変数またはwithブロック変数が設定されていませんとエラーが出ますが、問題点が分からないので解決方法を教えて頂きたいです。

tatsu99

2019/08/28 09:45

試したコードが提示されていますが、全ての行を提示していますか。途中までしか、提示していないように見えますが。

行動規範の内容に同意します

回答3件

問題点はFindの使い方が違うということだけですね。
公式マニュアル
戻り値は以下の通りです。

検索範囲の先頭のセルを表す Range オブジェクト。
一致するデータが見つからなかった場合、このメソッドは Nothing を返します。

よってIf文で直接判定することはできません。
Is Nothingを使いましょう。
また引数whatがリテラル文字列になっています。
直前で設定している変数名を指定するようにしましょう。
修正すると以下のようになります。

VBA
1If Worksheets("sheet2").Range("A1:A6").Find(What:=search) Is Nothing Then
2    Worksheets("sheet1").Cells(i, 3) = "日本"
3Else
4    Worksheets("sheet1").Cells(i, 3) = "アメリカ"
5End If

投稿2019/08/29 04:57

ttyp03

総合スコア16998

ベストアンサー

■試したコード
For i = 2 To xlDown
search= Worksheets("sheet1").Cells(i, 2).Value
If Worksheets("sheet2").Range("A1:A6").Find(What:="search") Then
Worksheets("sheet1").Cells(i,3) = "アメリカ"
Else
Worksheets("sheet1").Cells(i, 3) = "日本"

問題点が分からないので解決方法を教えて頂きたいです。

えっと、、、
Findメソッドは、
「セル範囲」に対して検索する機能です。
そして、検索をして見つかったら、見つけた「セル範囲」を返します。

例えば、
「A列のセル」を見て「ニューヨーク」という文字が入っているセルを探す。
と命令して、
「A2セル」が見つかったとします。

そうすると、kagobitoさんが書かれている文章は

「もし、（A2セル）がその時は、3列目に（アメリカ）と入力する」

と書かれていますよね？
なんのことかわかります？？？？

「もし、（A2セル）の値がニューヨークならば、その時は3列目に（アメリカ）と入力する」

というように書かないといけないですよね？

オブジェクト変数またはwithブロック変数が設定されていませんとエラーが出ます

ここでまずは、「オブジェクト」とは何ぞや？ということを理解しておく
必要があります。（詳しくはネットで検索して確認をしてください。）
ここでは、「オブジェクト≒操作対象」という形で説明します。

つまりFnidメソッドで返ってきた操作対象のセルに対して、
どんな操作をするのかが書かれていないので、
何をしていいかわからないよというエラーです。
※エラーメッセージはズバリのヒントをくれないことが多々ありますので、
注意してください。

今回のFindメソッド件の場合、
検索して見つかった場合は、セル範囲を返しますし、
見つからなかった場合は、「Nothing」を返しますので、
一旦変数に返り値を受けて、
もし、その中身がNothingなら、何もしない。
そうでなければ、帰ってきたセル範囲に対して、
なんらかの操作の指示をしてやる必要があります。
Nothingの場合、操作対象でないので、操作の命令をするとエラーに
なりますので、Nothingかどうかで条件分岐をします。
そして、セルが見つかった場合は、その返り値を再利用したいので、
「変数」にそれを保持しておきます。

ExcelVBA
1Sub test()
2    Dim i As Long, j As Long
3    Dim rngTarget As Range
4
5    i = 2
6    j = Worksheets("Sheet1").Range("A1").End(xlDown).Row
7
8    For i = i To j
9        Set rngTarget = Worksheets("Sheet2").Range("A1:A6").Find( _
10                        what:=Worksheets("Sheet1").Cells(i, 2).Value)
11        If rngTarget Is Nothing Then
12        Else
13            Worksheets("Sheet1").Cells(i, 3).Value = rngTarget.Offset(, 1).Value
14        End If
15    Next
16End Sub