ExcellVBAのFindに関する質問です。

質問

お世話になります。
VBA超初心者です。

同じSheetに二つのテーブル

性別テーブル(名前を付けてます）
顧客ＩＤ 性別
SA081　 男
SA083　　男
SA010 　　女
SA013　 女

顧客テーブル(名前を付けてます）
ID 氏名 　　　顧客ID　性別
1 山田　太郎　　SA081
2 斉藤　清 　　SA082
3 山本　花子　　SA061

です。

顧客テーブルの顧客IDを上から性別テーブルを検索して、あれば顧客テーブルの性別フィールドにその人の性別を記入するには、どうすればいいのでしょうか。

一生懸命考えて作ってはいるのですが、デバックばっかり出て、全然進みません。
Sub Chck()
Dim k As Range, s, i
Set k = Range("顧客テーブル").Range(2, 3).Value

For i = 2 To myTbl.Rows.Count
Set s = Range("性別テーブル").Cells.Find(what:=i, lookat:=xlPart, MatchCase:=False)
If s Is Nothing Then
k.Range("g2").Value 'ここに性別を書きます（ここは顧客テーブル外です。）
Exit Sub
End If
Next
End Sub

宜しくお願いします

回答No.4

hana-hana3 · ベストアンサー

＞それとも全部おかしいのでしょうか？

ｙｅｓ・・・。
簡単な動作から覚えましょうｗ

最初のエラーがk.Range("g2").Valueですよね。
このままだと、k の内容はシートが指定されないといけませんが、
＞Set k = Range("顧客テーブル").Range(2, 3).Value
では全く別の値をオブジェクトに設定しています。
この設定にも誤りがあります。

おそらく、"顧客テーブル"と言う範囲を設定したいのでしょう。
この場合、シートのどこかに顧客テーブルという名前の範囲が設定されていれば、Set k = Range("顧客テーブル") と書けますが、Range(2, 3).Valueは余計です。
でも、書かれたソースでは役立たない値です。

＞For i = 2 To myTbl.Rows.Count

myTblが何処にも定義されていないので、参照不可でエラー。

＞Set s = Range("性別テーブル").Cells.Find(what:=i, lookat:=xlPart, MatchCase:=False)
If s Is Nothing Then

Set s = Range("性別テーブル").Find(・・・・という書き方が良いでしょう。
でも、検査値が what:=i ではＦｏｒ文が動かないので、検索しようが無いですね。
動いたとしても数値なので、顧客ＩＤは入っていません。

＞k.Range("g2").Value
一巡して、（仮に）Range("g2").Value = 検索値 では、毎回同じ位置に入ってしまいます。

個々の文を正しくしても、目的の通りに動作させることは不可能です。

＞Exit Sub
も、うまく動作したとしても１回目の検索で記入が終了してしまいます。

他の方法もあると思いますが下記のような物を書いて見ました。
'Find版
Sub ChckFind()
Dim k As Range
Dim Ct As Integer
Dim s As Range

For Each k In Range("顧客テーブル") '顧客テーブルの全ての値を取得
If InStr(k.Value, "顧客ID") = 1 Then Ct = k.Column '顧客ＩＤが何列目にあるかを記録
If k.Column = Ct And k <> "" Then '顧客テーブルの列で、値が空白以外なら検索を行う
Set s = Range("性別テーブル").Find(what:=k.Value, lookat:=xlPart, MatchCase:=False)
If Not s Is Nothing Then '見つかった場合
'顧客ＩＤの隣のセルに、性別テーブルで見つけた顧客ＩＤの
'隣のセルの値を記入。
k.Offset(0, 1) = s.Offset(0, 1)
End If
End If
Next
End Sub

'Vlookup版
Sub ChckVlookup()
Dim k As Range
Dim Ct As Integer
Dim s As Variant

On Error Resume Next 'エラーがあっても止まらないで実行
For Each k In Range("顧客テーブル")
If InStr(k.Value, "顧客ID") = 1 Then Ct = k.Column
If k.Column = Ct And k <> "" Then
'VLOOKUPを使って性別テーブルの値を取得
s = WorksheetFunction.VLookup(k.Value, Range("性別テーブル"), 2, False)
If s <> "" Then '値が見つかったら記入する。
k.Offset(0, 1) = s
End If

End If
Next
On Error GoTo 0 'エラーが有ったら止まるように設定する。

End Sub

頑張ってくださいね。

お礼

有難うございます。
本当に私のと全然違いますね。
For EachステートメントやInStr関数っていうのがあるなんて知りませんでした。
又色々質問すると思いますので、その時も宜しくお願いします。

回答No.3

papayuka

#2です。

書き忘れましたが、元のコードが lookat:=xlPart になってたので、そのままサンプルも書きましたが、xlWhole にしないと部分的に一致する値があった場合、先に見つけた方を拾ってしまいます。

お礼

有難うございます。
本を見て勉強しているんですが、難しくって…
又質問すると思いますので、その時も宜しくお願いします。

回答No.2

papayuka

変数の型などを理解されていないようで、かなりの部分がおかしいと思います。

↓kはRange型ですから代入するのであれば
Set k = Range("顧客テーブル").Range(2, 3).Value
　　　　　↓
Set k = Range("顧客テーブル")
のようにしないと成り立ちません。

　　　　　　　　↓myTblってどこから出てきたのでしょう？
For i = 2 To myTbl.Rows.Count

↓最初の i は For の指定だと 2 です。つまり 2 を含むものを探してます。
～.Find(what:=i, lookat:=xlPart, MatchCase:=False)

　　　　　　↓逆です。見つからなかった場合になっています。
If s Is Nothing Then

　　　　↓文法的に変です。
k.Range("g2").Value

'--------------------------------------------------------------------------
上手く動くか解りませんがサンプルです。
試すならテスト環境で。

Sub Test()
Dim tRange As Range, r As Range, fr
Set tRange = Range("顧客テーブル").Offset(, 2).Resize(, 1)
For Each r In tRange
　Set fr = Range("性別テーブル").Resize(, 1).Find(what:=r, lookat:=xlPart)
　If Not fr Is Nothing Then r.Offset(, 1).Value = fr.Offset(, 1).Value
Next r
End Sub

回答No.1

hana-hana3

ＶＢＡの問題以前に、データベースの作り方に誤りがあるような気がします。
顧客ＩＤというのは一意なもので普遍ですから、これを顧客テーブルのＩＤに割り当てるべきかと思います。

そうすれば、単純にＶＬＯＯＫＵＰ関数で処理できます。
顧客ID　氏名 　　　　性別
SA081　　山田　太郎　=VLOOKUP(～)

ＶＢＡで処理する必要は何でしょうか？

補足

早速の回答有難うございます。
確かにそうですね。
何を一生懸命悩んでたんだろう。

ですが、一応勉強の為にマクロでの書き方を教えていただけますか。

k.Range("g2").Value　の記述の仕方が間違っているんだと思うんですが。。。
それとも全部おかしいのでしょうか？

宜しくおねがいします。