エクセルvbaのフォームでエラーとなります

質問

ワークシートにIDと名前の対応表があります。
別のシートのA1に検索したいID欄、A2にVLOOKUP関数で結果を表示させています。

VBAの入力フォーム上で、あるIDを入れたときに前途のA1に数値を代入し、A2で得られた結果をフォーム上の欄で取得するようにしています。

この時、表の中に対応する番号と名前があればキチンと結果が出ますが、そもそもデータベースが無ければVBA自体がエラーとなってしまいます。

ワークシート上のVLOOKUPには近似値では困るのでFALSEを指定していますので、該当番号がなければここでの結果は＃N/Aとなっています。

フォーム上で該当番号がなければ代わりの文字列、たとえば「なし」などを入力されるようにできるか、最低でもプロセスが中断される事がないようにしたいのですが、どのようにすれば可能でしょうか？

回答No.2

ham_kamo · ベストアンサー

No.1です。すいません、COUNTIFの式が間違っていました。（ついでにVLOOKUPの範囲にも：が抜けたりかっこが足りなかったり、間違いだらけでした）

=IF(COUNTIF(A1,$F1$F10)=0,"なし",VLOOKUP(A1,$F$G10,2,FALSE)
でなくて、
=IF(COUNTIF($F1:$F10,A1)=0,"なし",VLOOKUP(A1,$F1:$G10,2,FALSE))
でした。

お礼

ありがとうございました！
目から鱗というか、基本的な考えさえ思い浮かばないなんて、どうかしていました。感謝！

回答No.3

noname#95859

このようにされたらどうでしょう？
前提：
フォーム Form1
テキストボックス１：TextBox1
テキストボックス２：TextBox2
変換表：Sheets("Sheet2").Range("A1:B4").
－－－－－－－－－－－－－－－－－－－
（１）Vlookupを使う
Private Sub TextBox1_Change()
Cells(1, 1).Value = TextBox1.Text
If TextBox1.Text <> "" Then
If Not IsError(Cells(2, 1).Value) Then
TextBox2.Text = Cells(2, 1).Value
Else
TextBox2.Text = "なし"
End If
Else
TextBox2.Text = ""　’注１
End If
end sub

注１：テキストボックス１で、デリートで戻って空白になった時の
”なし”表示を避けるためです。

（２）イベントプロシージャの中だけでやってしまう方法。
　　　（Vlookupは使わない）
Private Sub TextBox1_Change()
dim myArray as variant
ID_asked = TextBox1.Text
myArray = Sheets("Sheet2").Range("A1:B4").Value
r = UBound(myArray, 1)
flag = 0
For ii = 1 To r
If ID_asked = myArray(ii, 1) Then name_answer = myArray(ii, 2): flag = 1: Exit For
Next
If TextBox1.Text <> "" Then
If flag = 1 Then TextBox2.Text = name_answer Else TextBox2.Text = "なし"
Else
TextBox2.Text = ""
End If
end sub

セルの範囲を myArray = Sheets("Sheet2").Range("A1:B4").Value
で、バリアント変数に入れることで、メモリ上に配列を持ってくることができます。UBound(myArray, 1)、UBound(myArray, 2)でその大きさを知ることができます。上記では縦方向のUBound(myArray, 1)だけを使っています。
テキストボックス１に入力されたIDをfor loopでサーチして、返すと言うのが（２）の案です。

お礼

ありがとうございます。
正直、難解な構文ですが、これを読み解くことでスキルにつながると考えますのでじっくりと取り組んでみたいと思います。
コピペでは、不具合があったときに理解できていなければいけません。
勉強させていただきます。

ありがとうございました！

回答No.1

ham_kamo

フォームの方をいじるのではなくて、セルの方で、
=VLOOKUP(A1,$F1$G10,2,FALSE)
となっている（範囲は適当です）のを
=IF(COUNTIF(A1,$F1$F10)=0,"なし",VLOOKUP(A1,$F$G10,2,FALSE)
のように、VLOOKUPで検索する範囲の１列目にA1の値が入ってなかったら、IF分で「なし」を表示するようにしてしまえばいかがでしょうか。