このコミュニティでもたびたび質問されている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)

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

このQ&Aに関連する最新のQ&A

A 回答 (3件)

私も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

試していないので、エラー出るかも
    • good
    • 0
この回答へのお礼

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

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

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

こんにちは。



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

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

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

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

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

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

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

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

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

>例)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

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

補足日時:2009/05/17 07:47
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QエクセルVBAでファイルを連続して処理する方法は

エクセルVBAで、エクセルファイルを開いた状態で特定処理を行う仕組みを作りました。
ただ、複数のファイルを処理したいのですが、いちいちファイルを開いてから処理しなければならないため効率が今ひとつです。
ファイル名称をテーブル化するなどして、一気に連続して処理するようにしたいのですが、どのように行えばいいでしょうか。

また、処理したいのは、更新日付が一定日以降のエクセルファイルです。
更新日付と対象ファイルのフォルダーを指定すれば、更新日がそれ以降のファイルを検索し、それが順次処理されていくようなVBAをつくりたいと考えています。

部分的にでもよいので、どなたか分かる方、教えてください。
ちなみにエクセルのバージョンは2000です。
よろしくお願いします。

Aベストアンサー

> 選択フォルダのサブフォルダをのものも含むようにはできないでしょうか。

再帰処理すればできますよ。

Microsoft Scripting Runtime を参照してから、次のコードを試してみて
下さい。
取り急ぎで書いたので、エラーがあるかもしれませんが。。。

まあ、環境によっては使えませんし、推奨はできないのですが、FileSearch
を使ってもサブフォルダを含めた検索はできます。調べればサンプルはすぐ
でてくると思いますよ。

ご参考までに。

Private mDateFilter As Date

Sub フォルダ内のXLSファイル順次処理()

  Dim fso As FileSystemObject
  Dim sDir As String
  
  ' // 日付のフィルタ条件設定 例)10日前の 0:00 以降更新のファイルを対象とする場合
  mDateFilter = DateAdd("d", -10, Date) + TimeValue("00:00:00")

  ' // 対象ファイルのあるフォルダを指定
  sDir = BrowseForFolder()
  If Len(sDir) = 0 Then
    Exit Sub
  End If

  Set fso = CreateObject("Scripting.FileSystemObject")
  
  Dim fld As Folder
  Dim iRes As Integer
  
  If fso.FolderExists(sDir) Then
    Set fld = fso.GetFolder(sDir)
    iRes = 0
    If fld.SubFolders.Count > 0 Then
      iRes = MsgBox("サブフォルダも検索しますか?", _
             vbYesNoCancel Or vbInformation)
    End If
    Select Case iRes
      Case vbYes:  Call FindFiles(fld, True)
      Case vbNo, 0: Call FindFiles(fld, False)
      Case Else:  ' // User Cancel
    End Select
  End If
  
  Set fld = Nothing
  Set fso = Nothing


End Sub

' // XLS ファイルを検索するサブプロシージャ
Private Sub FindFiles( _
  ByRef fld As Folder, _
  ByVal fCheckSubfolders As Boolean _
)

  ' // ファイルへの処理
  Dim f   As Object
  For Each f In fld.Files
    If f.Name Like "*.xls" And f.Name <> ThisWorkbook.Name Then
      If f.DateLastModified >= mDateFilter Then
        ' // 処理例
        Call MainProc(f)
      End If
    End If
  Next

  ' // サブフォルダ検索オプション
  Dim subFolder As folder
  If fCheckSubfolders Then
    ' // 再帰呼び出し
    For Each subFolder In fld.SubFolders
      Call FindFiles(subFolder, True)
    Next
  End If

End Sub

' // メイン処理 -- FindFiles から順次呼び出されます
Sub MainProc(ByRef f As file)

  ' // ここにご自分で書いたプロシージャを
  ' // とりあえず、セルにでも書き出してみます
  Dim i As Long
  i = Cells(Rows.Count, "A").End(xlUp).Row + 1
  Cells(i, "A").Value = f.Name
  Cells(i, "B").Value = f.DateLastModified

End Sub

' // フォルダ選択ダイアログ
Private Function BrowseForFolder() As String

  Const BIF_RETURNONLYFSDIRS = &H1

  Dim fld As Object
  Set fld = CreateObject("Shell.Application") _
       .BrowseForFolder(0&, "選択します", BIF_RETURNONLYFSDIRS)
  If Not fld Is Nothing Then
    BrowseForFolder = fld.Self.Path
  End If
  Set fld = Nothing

End Function

> 選択フォルダのサブフォルダをのものも含むようにはできないでしょうか。

再帰処理すればできますよ。

Microsoft Scripting Runtime を参照してから、次のコードを試してみて
下さい。
取り急ぎで書いたので、エラーがあるかもしれませんが。。。

まあ、環境によっては使えませんし、推奨はできないのですが、FileSearch
を使ってもサブフォルダを含めた検索はできます。調べればサンプルはすぐ
でてくると思いますよ。

ご参考までに。

Private mDateFilter As Date

Sub フォルダ内のXLS...続きを読む

QApplication.ScreenUpdating=Falseを使うと・・・。

VBAでユーザーフォームを表示させ、コマンドボタンを押すと
ユーザーフォームを閉じて処理を開始すると言う設定をしているのですが
ここにApplication.ScreenUpdating=Falseを追加すると
ユーザーフォームが閉じずに処理を行なってしまいます。
(最後まで開いたまま)

コマンドボタンのクリックイベントの最初にUserForm1.hideと入れ、その後にApplication.ScreenUpdating=Falseを入れた後に
行なう処理を書いているのですが、どこか間違っていますでしょうか?

お教え下さい。よろしくお願いします。

Aベストアンサー

fk_sapさん、こんばんは。Wendy02です。

ここらの話は、設計の問題なんですね。
人の好き好きにもあると思いますし、あまり、これという方法もあるわけではありません。

>ユーザーフォームを消して「処理中」などと書いたシートを
>表示させてみようかなどと、技術もないクセにそう言う理想ばかりが先走ってしまって・・・(汗)

ご指摘のとおり、その問題は、「処理中」いう表示ですね。私は、凝ったことをせずに、ユーザーフォーム上のラベルなどに出してしまいます。フリーソフトのExcelユーティリティには、すごく凝った、プログレスバーもどき(本物は配布できないはず)などしている人がいますが、それは、VBでするならともかく、私は、Excelでは、すこぶる簡単にしてしまいます。

他にワークシート上に出すと言っても、よく、Windowのタイトル部分や、ステータスバー部分を使うことは思いつくのですが、必ずしも、人は見ないような気がしてやめてしまいます。「処理中」などと書いたシート なんて、オブジェクトとしては、私には重過ぎるように思いますね。

>Beepを入れることによって、どう言う効果があるのでしょうか?

これは、私の癖かもしれませんね。終了が気が付かないことがあるからです。MsgBox は、最後にクリックをしなければ終われません。その代わりに、終了の合図にWshShell.PopUpを使う人がいます。クリックを押さないで、MsgBox を終わらせられるからです。私は、どちらも面倒なので、Beepだけで済ませてしまいます。

fk_sapさん、こんばんは。Wendy02です。

ここらの話は、設計の問題なんですね。
人の好き好きにもあると思いますし、あまり、これという方法もあるわけではありません。

>ユーザーフォームを消して「処理中」などと書いたシートを
>表示させてみようかなどと、技術もないクセにそう言う理想ばかりが先走ってしまって・・・(汗)

ご指摘のとおり、その問題は、「処理中」いう表示ですね。私は、凝ったことをせずに、ユーザーフォーム上のラベルなどに出してしまいます。フリーソフトのExcelユーティ...続きを読む

QエクセルVBA 2千万行のCSVファイルを開きたい

上から順に読み込み、順次処理していき最終的には全部処理します。CSVファイルは読むだけです。

やり方はいくつか考えられます。
1
100万行ずつシートに読み込み、終わったら次を読み込む。CSVファイルは開きっぱなし。

2
100万行ずつのエクセルファイルに分割し、順次開いて処理。ファイル開閉に時間がかかる

3
2千万行の巨大な配列に代入し、順次処理。
CSVファイルは閉じることが出来る。

3がいいように思えますが、どうでしょうか?使用メモリは1と同じですか?

Aベストアンサー

> 懸念点は、最初から最後まで巨大なCSVファイルを開いたまま処理することです。

それに関しては問題ないです。VBAのOpenでファイルを開いた場合、実は一切読んでいません。今何行目を読んでいるかを示すカウンタを用意するだけです。またLine Inputで読み込む場合も、今操作している一行しか相手にしません。

だからこそ、実装している物理メモリ量をはるかに超えるサイズのファイルを扱っていても、仮想メモリのお世話にならずに済むわけです。

なので懸念材料はそこではなく、分割処理することによってデータ同士の断絶が起きると言うか、連続性がなくなることでしょうか。最初の100万行の固まりと、次の100万個の固まりには全くつながりがないことになるので。

QApplication.DisplayAlerts =Falseでも警告される?

下記のコードを実行するとSheet1という名前のシートがないBookを開いた場合、「統合元ファイル○○のSheet1を開けません」という警告がでます。
無ければ集計しなくていいので「はい」を押せばいいのですが、その都度止まってしまうのは困ります。
Application.DisplayAlerts = False としても警告されるのはなぜでしょうか?出ないようにすることは出来ないのでしょうか?

Sub test03() 'Sheet1のみ開かずに統合
Dim MyFile As String, MyPath As String
Dim SumFile() As Variant, i As Long
MyPath = ThisWorkbook.Path & "\"
MyFile = Dir(MyPath & "*.xls", vbNormal)
Do Until MyFile = ""
If MyFile <> ThisWorkbook.Name Then
ReDim Preserve SumFile(i)
Application.DisplayAlerts = False
SumFile(i) = "'" & MyPath & "[" & MyFile & "]Sheet1'!R1C1:R10C2" 'A1からB10のLinkを変数に代入
Application.DisplayAlerts = True
i = i + 1
End If
MyFile = Dir
Loop
If i = 0 Then MsgBox "データが有りません ( ̄□ ̄;)!!": Exit Sub
Worksheets("Sheet1").Range("A1").Consolidate Sources:=SumFile()
End Sub

下記のコードを実行するとSheet1という名前のシートがないBookを開いた場合、「統合元ファイル○○のSheet1を開けません」という警告がでます。
無ければ集計しなくていいので「はい」を押せばいいのですが、その都度止まってしまうのは困ります。
Application.DisplayAlerts = False としても警告されるのはなぜでしょうか?出ないようにすることは出来ないのでしょうか?

Sub test03() 'Sheet1のみ開かずに統合
Dim MyFile As String, MyPath As String
Dim SumFile() As Variant, i As Long
MyPath...続きを読む

Aベストアンサー

横から失礼します。

> ブックをOpenせずにSheet1の存在確認をする方法はありますか?

これをヒントに関数化してみて下さい。

Sub Sample()

  ' ExecuteExcel4Macro メソッドを使う方法
  ' ダミーアクセスしてエラーになったらシートは無い
  
  Dim strQry As String
  Dim Dummy As Variant
    
  strQry = "'C:\[test.xls]Sheet1'!R1C1"
  Dummy = Application.ExecuteExcel4Macro(strQry)
  If IsError(Dummy) Then
    MsgBox "Sheet1 は無い"
  Else
    MsgBox "Sheet1 が在る"
  End If

End Sub

QVBAからファイルをセル入力から開く方法

VBAからファイルをセル入力から開く方法

だれかご教授頂けませんでしょうか?VBAからエクセルファイルを開こうとしています。
そこでシート1のA1セルにファイル名を記述してあり、そのファイル名からファイルを開く事は出来ますでしょうか?
何か良い方法がありましたら教えて頂きたいのですが。

Aベストアンサー

下記で試してください。
パス名の最後には\を付けるようにしてください。

Workbooks.Open "D:\ABC\" & Sheets("シート1").Range("A1").Value


ワークシートのイベントを使う方法も考えられると思います。
BeforeDoubleClickイベント辺りを使って
ファイル名セルをダブルクリックすれば

Excel(エクセル) VBA入門:ワークシートのイベント
http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_event.html

QFALSE & TRUE について

初歩の質問ですが関数でたとえば VLOOKUP(A1,商品,0)
の式があります。
検索の型がFLASEとなっていたり 0 となっていたり
します。TRUEは 1 とか TRUE になっています。
なにか使い分けがあるのでしょうか?
分かる方教えてください。

Aベストアンサー

VLOOKUP()等における検索の型の指定は、Excelのヘルプ等の記載では、TRUE or FALSEですが、コンピュータ言語の世界では、

FALSE = 0
TRUE <> 0 (0ではない数値=0以外の数値)

という決まりがあるので、FALSEの代わりに 0 を、TRUEの代わりに 1 を使うことができます。(FALSE or TRUEと 0 or 1 とで、特に使い分けというのはないと思います。)

QEXCEL VBA で現在開いているブックのファイル名を取得する方法

EXCEL2003 VBAで業務を簡素化するために、現在開いているブックのファイル名を取得する方法が分かりません。
作業手順をマクロを使って処理していますが、オリジナルのワークブックをファイル名を変えて保存し、以後、このワークブックを読み込んで使用しています。
このときのVBAは、オリジナルのファイル名を使っているため、ファイル名を変更するとエラーになり、以後の業務に使用できません。
常にファイル名を取得出来るVBAをどなたか、教えて下さい。

Aベストアンサー

>現在開いているブックのファイル名
 ちょっと曖昧な表現かなぁという気もいたしますが、VBAが書いてあるブックのブック名は
ThisWorkbook.Name
で、現在 "アクティブにして" 操作対象になっているブックの名前は
ActiveWorkbook.Name
ですね。

 しかし、
>VBAは、オリジナルのファイル名を使っているため、ファイル名を変更するとエラーになり
というような文脈からすると、
ThisWorkbook.Name
の方ですかね。

Qエクセル:Application.ScreenUpdatingについて

マクロでCSVファイルを開き、CSVファイル上で一部の操作したいのですが、
画面には開いたCSVファイルを表示させたくないです。

 Application.ScreenUpdating = False
 csvFile.Activate
 Application.ScreenUpdating = True
※csvFileは、開いたCSVファイルにセットした名称

上記のように、CSVファイルをアクティブにしている部分をApplication.ScreenUpdatingで挟んでいるのですが、
CSVファイルが表面に出てきてしまいます。

 MsgBox "まえ"
 csvFile.Activate
 MsgBox "うしろ"

と記述すると、
「まえ」のメッセージボックスの『OK』をクリックするとCSVファイルが表面に表示され、
その上に「うしろ」のメッセージボックスが表示されるので、
CSVファイルを表面に出しているのは csvFile.Activate のコードで間違いないと思います。

表示させたくないファイルをわざわざアクティブにしているのは、
自分の知識ではアクティブにしないとそのファイル上での操作ができないためです。

シートの切り替えなどは
Application.ScreenUpdating = False
で止めることができるのですが、別ファイルの場合は止められないのでしょうか。

マクロでCSVファイルを開き、CSVファイル上で一部の操作したいのですが、
画面には開いたCSVファイルを表示させたくないです。

 Application.ScreenUpdating = False
 csvFile.Activate
 Application.ScreenUpdating = True
※csvFileは、開いたCSVファイルにセットした名称

上記のように、CSVファイルをアクティブにしている部分をApplication.ScreenUpdatingで挟んでいるのですが、
CSVファイルが表面に出てきてしまいます。

 MsgBox "まえ"
 csvFile.Activate
 MsgBox "うしろ"

と記述...続きを読む

Aベストアンサー

えっと、、、
画面描画を止めて、すぐに戻してるので表示されるのでは?
試してないけど、

 Application.ScreenUpdating = False
 csvFile.Activate
 Thisworkbook.Activate
 Application.ScreenUpdating = True

とか。

それよりも Open からのコードを出された方が良いアドバイスが得られるかも。

Q他のExcelファイルのVBAコードを検索したい

他のExcelファイルに書かれたVBAコードに、ある特定のキーワード(パス名など)が使われているかを自動的に調べるためのVBAを書きたいと思っています。

仕事で使っているマクロが書かれたExcelファイルからある特定の処理をしているマクロを含むものを検索したいのですが、検索するExcelファイルが膨大なため、順番にExcelファイルを開いてVBAコードをチェックしていく工程をマクロ化したいのです。ファイルを順に開いていく処理はマクロ化できるのですが、書かれたVBAコードのチェックをどうしたらよいか悩んでいます。

「VBAコードに対して検索する」または「VBAコードを1行ずつ変数に代入する」、「モジュールシートをテキストファイルに出力する」などの処理が自動化できれば対応できそうなのですが、そのような処理をExcelVBAで作成できるでしょうか。

なお環境はWindowsXPのExcel2003です。
よろしくお願いいたします。

Aベストアンサー

開いたブックにあるVBAコードをテキストに出力する方法
Sub VBExport(WKB As Workbook)
  Dim A, B
  Set A = WKB.VBProject.VBComponents
  For Each B In A
    B.Export "C:\~" '★テキストファイルでエクスポート
    xxxxxx '★上記テキストファイルを調べる処理
  Next
End Sub
ブックやシートにあるコードも(空でも)出力されます。
もし、標準モジュールのみ処理したい場合は「B.Type = 1」に限り
エクスポートします。

QExcel VBAのApplication.ExecuteExcel4Macro

こんにちは。
Excel VBAでファイル間集計のマクロを作りました。
変数MyStrに指定のパスとファイル名を代入するようにしてあります。
Application.ExecuteExcel4Macro(MyStr)
を実行し指定のパスにファイル名が実在すれば、動作は上手くいきます。
ただし、指定のパスにファイルが存在しない場合、ファイル検索のダイアログが出てしまい、強制終了すれば、実行時エラー2023になってしまいます。
これを回避し、「該当のファイルが見つかりません」とメッセージを出したいのですが、IF文にどのように書けばいいのか分かりません。

参考サイトでも、構いませんので皆さんの知恵を貸して下さい。
よろしくお願いします。

Aベストアンサー

Dir() 関数でチェックしたらいかがですか?

http://officetanaka.net/excel/vba/file/file06.htm


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング