VBAでVlookup機能を使うときにエラー

このコミュニティでもたびたび質問されているVLOOKUPのVBAですが
解答例で多く書かれているのが
Public Sub test()
　　　　Dim MyVariant As Variant
　　　　MyVariant = Application.VLookup("excel", Range("A:B"), 2, False)
　　　　If IsError(MyVariant) Then
　　　　　　Debug.Print "Not Found"
　　　　Else
　　　　　　Debug.Print MyVariant
　　　　End If
　　End Sub
なのですがRangeの部分にシートの情報を乗せるとエラーが出ます
例）Application.VLookup(Label6.Caption, WorkSheets("Sheet5").Range("A:B"), 2, False)

まだまだ初心者なので何がおかしいのかわかりません
よろしくお願いします

No.3 ベストアンサー 回答者： hige_082 回答日時： 2009/05/17 11:38

私もVBAでVLOOKUPは一般的ではないと思いますよ

VBA的にやるとこんな感じかな

Private Sub データ表示()
Dim a As Range
Label日付.Caption = Sheets("休暇・特勤").Cells(CurRow, 1)
Label曜日.Caption = Format(Sheets("休暇・特勤").Cells(CurRow, 1), "（aaa）")
Set a = Sheets("祝日").Range("B4:B34").Find(Sheets("休暇・特勤").Cells(CurRow, 1).Value)
If a Is Nothing Then
Label休日.Visible = False
Else
Label休日.Visible = True
End If
End Sub

試していないので、エラー出るかも

この回答へのお礼

ありがとうございました。曜日の部分もスマートな形に訂正していただき感謝です。
マクロ、VBAを始めてほんのちょっと慣れてきましたが、知っている知識のみで処理をさせようとして無理が出たみたいです。

今後も精進していきます。

お礼日時：2009/05/17 22:29

No.2 回答者： Wendy02 回答日時： 2009/05/17 09:36

こんにちは。

>コミュニティでもたびたび質問されているVLOOKUPのVBAですが

たびたび？　VBAのコードとしては、VLOOKUPは、ほとんど使わないと思います。

>Application.VLookup(Label6.Caption, WorkSheets("Sheet5").Range("A:B"), 2, False)

VBAとワークシートでは、Range オブジェクトの考え方が違います。必要な大きさだけ取ればよいのです。詳しい説明は混乱しかねませんから、割愛しますが、VBAはワークシートの延長ではありませんから、とてもイレギュラーです。

それと、　Label6.Caption って、どこのラベルなのでしょうか。これを、そのまま置いたところで、該当シートモジュール以外はエラーが発生するはずです。こういう場合、親オブジェクトから正しく書かなくてはなりません。言い換えると、コンテナを使わなくてはなりません。しかし、それより以前に、Label ではなく、TextBox にしてほしいものですね。

それと、Public キーワードを付けているか、分かっていればよいけれども、その書く意義が分からないなら、書かないほうがよいです。

後は、文字列か数値かということですが、それは、#1さんの回答に譲ります。

この回答へのお礼

厳しい御指摘ありがとうございます。
ほかのコミュニティでもVBAに慣れてきた初心者の多い行動に
『すべてをVBAで処理させようとする』をあげておられました。
まさに今の私自身でした（汗）
指摘通り、Public キーワードを書く意義もわかっていませんでした。
Sheets("○")とWWorkSheets("○")の違いも今日知りました
また初心に帰って基礎を勉強します

お礼日時：2009/05/17 22:35

No.1 回答者： n-jun 回答日時： 2009/05/16 23:52

＞例）Application.VLookup(Label6.Caption, WorkSheets("Sheet5").Range("A:B"), 2, False)

このコードの前に
MsgBox TypeName(Label6.Caption)
を追加してみてStringと出たとします。

検索値が”数値”であった場合は「型が一致しない」となるかと。
そこで”数値”にしてあげるためVal関数を用いる。

MsgBox TypeName(Val(Label6.Caption))・・・Doubleになる。
Application.VLookup(Val(Label6.Caption), WorkSheets("Sheet5").Range("A:B"), 2, False)

と言う事ではないでしょうか。

この回答への補足

ありがとうございます
下記のものが実際のコードです

Private Sub データ表示()
Label日付.Caption = Sheets("休暇・特勤").Cells(CurRow, 1)
曜日 = Weekday(Label日付.Caption)
Select Case 曜日
Case vbSunday: Label曜日.Caption = "（日）"
Case vbMonday: Label曜日.Caption = "（月）"
Case vbTuesday: Label曜日.Caption = "（火）"
Case vbWednesday: Label曜日.Caption = "（水）"
Case vbThursday: Label曜日.Caption = "（木）"
Case vbFriday: Label曜日.Caption = "（金）"
Case vbSaturday: Label曜日.Caption = "（土）"
End Select

If IsError(Application.VLookup(Val(Label日付.Caption), Sheets("祝日").Range("B4:D34"), 3, False)) Then
ElseIf Application.VLookup(Val(Label日付.Caption), Sheets("祝日").Range("B4:D34"), 3, False) = False Then
Label休日.Visible = False
ElseIf Application.VLookup(Val(Label日付.Caption), Sheets("祝日").Range("B4:D34"), 3, False) = True Then
Label休日.Visible = True
End If
End Sub

祝日シートのＢ列に祝日の日付（1/1など）Ｄ列にTRUEと表記しています
ユーザーフォーム上でLabel日付.Captionに表示された日付になると『祝日です』と表記されるVBAを作ろうとしています。
しかし確認用に
Application.VLookup(Val(Label日付.Caption), Sheets("祝日").Range("B4:D34"), 3, False)の値を通常シートに表記すると全日付で『#N/A』となってしまいます？
ワークシート上で関数を使ってユーザーフォームに値を返したほうが無難ですかね？

補足日時：2009/05/17 07:47