vba ある値を別シート参照(列指定)して一致したセルの右隣の値を引っ張る方法について

Question

お世話になっております。
kaoriと申します。

今回は主に題名の事をしたいのですが、少々ややこしいのでやりたい事を使うものを使用して説明させていただきます。

【使用するシート】
説明シート[犬種(英語)列と犬種(日本語)列]

【使用する値】
dogBreed(英語で犬の犬種が書かれています)

【内容】
Function pet()
Call localize(dogBreed) '引数はランダムでtiwawaとかpaguとかになるとしてください。
msgbox localize 'チワワと日本語が取得できている
End Function

Function localize(Byref dogBreed As String)
Dim sh1 As Worksheet
Set sh1 = Sheets("説明シート")
Dim wanko As String
※説明は犬種(英語)列 2は犬種(日本語)列となっております。
If wanko = VLokup(dogBreed, sh1, 説明, 2, False) Then

localize = wanko(日本語化)

Else
localize = dogBreed(英語のまま)
End If

End Function

と、説明シートを参照してもし犬種(英語)列にtiwawaがあるならその右隣のセル 犬種(日本語)列のチワワを取得して戻り値として渡したいのです。
犬種(英語)列にtiwawaがない場合戻り値は tiwawaのままでよいという事になります。

以上、日本語で書くとかえってややこしいと思い
私が考えて作ってみたコードを使ってやりたい事を説明として書いてみました。

ここがわかりにくいという点があると思いますので、その際は質問して頂けたら幸いです。
よろしくお願い致します。

Answer 1

和名検索部分だけ抜き出すとこんな感じになると思います。

On Error Resume Next 'エラーが発生しても次行から処理継続

'和名検索
wanko = WorksheetFunction.VLookup(dogBreed, 抽出範囲, 2, False)
If Err = 0 Then
    '正常パターン
    戻り値 = wanko
Else
    '異常パターン
    戻り値 = dogBreed
End If

On Error GoTo 0      'On Errorを解除

---

細かな指摘とアドバイス

【関数の作成について】
・戻り値を返す関数はFunction、返さない関数はSubで作成するのが一般的です。
今回の場合、Pet関数はSub、localize関数はFunction。

・Functionの宣言はFunction ファンクション名(引数 as 引数の型…) as 戻り値の型
文字列を戻り値としたい場合は、戻り値の型を文字列型(String)で宣言しましょう。
宣言しなくてもVariant型で補われますが、型を意識することが大事です。

【VLookup関連】
・VLookUpの第２引数（検索範囲）にWorkSheetは渡せません。
Range("説明シート!A:B")のように範囲をRangeで指定してあげる必要があります。

・VLookUpで正しく検索するためには、いくつかの注意点があります。
①検索範囲の左端列が検索対象列であること
②検索範囲が左端列で昇順ソートされていること
これを満たしていないと検索範囲に値があるのに見つからない、といった事態が発生しますのでご注意ください。

・説明シートの内容がVLookUpの条件を満たさない場合、VLookup以外での検索を検討する必要があります。（Range.Findなど）

Comments

[kaori1994]

jawaさん、お世話になっております。
本日は実際のコード含め、指摘とアドバイスありがとうございます！
コードはもちろん、その他情報が凄く勉強になりました。
どうやら私の中で、まだ関数についてや、RangeやCellsやシート参照等がちゃんと理解できていない事が判明しました。
また、VLookup使用するにも条件があるという事も初めて知りました。
なので、VLookUpの条件が満たす場合と満たさない場合の検証と
VLookUp以外で実装可能かの検証をしていきたいと思います。
お忙しい中、私の為に貴重なお時間を割いていただきありがとうございました！

Answer 2

説明シートを参照してもし犬種(英語)列にtiwawaがあるならその右隣のセル 犬種(日本語)列のチワワを取得して戻り値として渡したい

の部分について

これを実現したいのが

If wanko = VLokup(dogBreed, sh1, 説明, 2, False)  Then 
    localize = wanko(日本語化) 
Else 
    localize = dogBreed(英語のまま) 
End If 

この部分と思いますが、ちょっとうまくなさそうです

VLookup を使って処理するなら、このような手順になると思います

１）戻り値 localize の初期値（何も見つからなかったら返したい値）には入力値 dogBreed を入れておく
２）VLookup で入力値を検索して日本語列から結果を取り出す
→ 範囲外エラー ４）へ
３）if 日本語の中身があった（文字が入っていた）ら then 戻り値に日本語を代入する
４）戻り値が確定したので処理を終わる

英語名が無かった場合は入力値を返すことになっているので
戻り値を最初に入力値で初期化しておけばよいでしょう

VLookup で日本語が取り出せたのなら英語名も存在することになるので
VLookup は１回だけ実行し、かつ日本語を取り出すだけでよさそうです

以上ご参考まで

Comments

[kaori1994]

takitoさん、お世話になっております。
なるほど！戻り値には初期値を入れておく必要があるのですね、勉強になります！
ここでまた質問があるのですが、よろしいでしょうか・・・

Function localize(Byref dogBreed As String)
Dim sh1 As Worksheet
Set sh1 = Sheets("説明シート")
Dim wanko As String
localize= dogBreed

On Error Resume Next
If wanko = VLokup(dogBreed, sh1, 説明, 2, False) Then

localize = wanko(日本語化)

Else
localize = dogBreed(英語のまま)
End If
End Function

としたのですが、2点問題がございます・・・
果たしてこれでtakitoさんのおっしゃっている戻り値の初期化ができているのかという点と、これで実行するとsubまたはfunctionが定義されていませんとエラーがはかれます
申し訳ありませんが、お時間の都合が良い時にご回答いただけたら幸いです。
以上、よろしくお願い致します。

[takito]

1) そのプログラムでは
localize = dogBreed
の部分が初期化に該当しています
この後にどのようなタイミングで localize 関数を抜けても、呼び出し側にちゃんと値が返されます

2) 「subまたはfunctionが定義されていません」というのは恐らく VLookup の使い方が間違えているためと思います
書籍やGoogle先生に「vba VLookup」を聞いてみて、正しい使い方を確認してみてください

Answer 3

「VLookup」で検索できなかった場合は、On Errorでエラーを拾わないといけません。

以下のサイトが参考になると思います。
https://www.moug.net/tech/exvba/0100001.html

Comments

[kaori1994]

tomo.inaさん、初めまして。
On Errorでエラーを拾わないといけないのですね！
参考サイトURLまでご丁寧にありがとうございます。
早速チェックさせて頂きます。
ありがとうございました！