VLOOKUP関数とテキストボックスの連携

質問

エクセルでユーザーフォームを作成しています。

「テキストボックス11」に、以下のように、VLOOKUP関数を入れたいのですが、
どのようにしたら良いのか、わかりません。

●入れたい関数は ISERROR関数とVLOOKUP関数を組み合わせたもの。
=if(iserror(vlookup(「コンボボックス1」の値,マスタ!A80:D90,4,false)),""vlookup(「コンボボックス1」の値,マスタ!A80:D90,4,false))
(1)「コンボボックス1」の値を検索値とする。
(2)参照範囲は「マスタ」シートの「A80:D90」

失敗しているVBAは以下の通りです。
Private Sub UserForm_Initialize()
TextBox11.Value = Application.WorksheetFunction.if(iserror(VLookup(ComboBox1.Value, Range("A85:D94"), 4)),"",VLookup(ComboBox1.Value, Range("A85:D94"), 4))
End Sub

教えていただけると助かります。

回答No.2

keithin · ベストアンサー

＃突っ込みドコロが多いので，説明が長くなりました。

「Application.WorksheetFunction」は，それぞれの関数（iserroなりvlookupなり)の「全て」に取り付けなければいけません。
また，ワークシート関数の全ての関数がapplication.worksheetfunctionに用意されているわけではありません。たとえばif関数は含まれていないため，vbaの関数などを代用する必要があります。
どの関数が使えてどの関数は用意が無いのかは，VBA画面で「オブジェクトブラウザ」を利用し，worksheetfunctionクラスの配下にどんな関数が準備されているか見てみることで，確認することができます。

worksheetfunctionを使う勉強のために書き方を練習してみるのもひとつのやり方ですが，多くの場合にはvbaではvbaらしく合理的なプログラムも組めるよう，状況に応じた工夫をします。

＃更に注意
VBAで使う以前にVLOOKUP関数自体の正しい使い方についてもきちんと理解しておかないと，当然必要な結果は得られません。次のようなよく失敗するケースについて，理解はされていますか？
○VLOOKUP関数の第４引数を省略した場合，検索対象範囲の左端列は昇順に並べ替えておかないと，正しい結果は得られません
○VLOOKUP関数の検索値として「コンボボックスの値」を取り込むと数字であっても文字列扱いになるため，セルに用意した検索対象範囲の左端列に数値が記入されていた場合に対する検索は失敗します

作成例１：実はこの方法では出来ない
with application.worksheetfunction
userform1.textbox1.value = iif(.iserror(.vlookup(combobox1.value, worksheets("シート名").range("A85:D94"),4)),"", .vlookup(combobox1.value, worksheets("シート名").range("A85:D94"),4))
end with
＃検索値が無いなどの場合に，iserrorで逃がしたはずなのに計算が出来ない事を確認

作成例２：検索値が文字列の場合
with application.worksheetfunction
if .countif(worksheets("Sheet1").range("A85:A94"), combobox1.value) > 0 then
userform1.textbox1.value = .vlookup(combobox1.value, worksheets("Sheet1").range("A85:D94"), 4, false)
end if
end with

作成例３：検索値が文字列の場合，かつ検索対象範囲のA列が昇順で並べ替え済みの場合
with application
userform1.textbox1.value = iif(.iserror(.vlookup(combobox1.value, worksheets("シート名").range("A85:D94"),4)),"", .vlookup(combobox1.value, worksheets("シート名").range("A85:D94"),4))
end with

お礼

無事に出来ました。
マクロはまだまだ始めたばかりでわかりません。

もっともっと勉強します。

回答No.3

imogasi

第1点
=VLOOKUP(2,$D$1:$E$3,2)など第1引数にりてらる数値を指定することはOKを確認しました《質問では
ComboBox1.Valueですが。
=VLOOKUP("y",$D$1:$E$3,2)もOKでした。
これが色んな関数でOKかどうかは、確かめておかないといけない。普通は値の入ったセル番地を指定することが多い。
ーー
第2点
IFがVBAにあるからといって、またIsError函数が、VBA函数にあるからといって、あるいは深く考えず、関数の組み合わせのつもりでif(iserror(VLookup　と書いたのかもしれないが、こういうエクセル関数の組み合わせは、最初にApplication.WorksheetFunctionをつけたら仕舞い（その効果があるの）ではない。
VLOOKUP函数を実行して、その結果（エラ-値）を変数なりで受けて（一旦受けないなら式全体の前にIFを書き、Thenを添える）これに対し、IFステートメントやIserror関数を用いて、別行（単位）で判定しないとならない。
セルにFormulaとして入れる場合は、シートのセルにに関数を入れるのと同じ形で入れられるが、WorksheetFunction.
は指定した単一の函数しか実行できないと思う。関数の組み合わせやイプは直接Application.WorksheetFunctionのあとにかけないでしょう。
A８５以下で値の重複がないのだろうから（セル範囲）.Find(What:= 値）（メソッド）などと簡単に書くことも使えるとおもう。

回答No.1

mshr1962

Application.WorksheetFunctionはワークシート関数毎に必要です。
とはいえ何度も使うのは、良くないので数式を分解して

Dim RT As Variant
RT = Application.WorksheetFunction.VLookup(ComboBox1.Value, Range("A85:D94"), 4)
If IsError(RT) Then
TextBox11.Value = ""
Else
TextBox11.Value = RT
End If