VBA　.WorksheetFunctionについて

Question

Dim DestBook As Workbook
    Dim pathmacrobook As String
    Dim namebook As String
    Dim myb As Range
    Dim r As Long
     
  Application.ScreenUpdating = False 
  ThisWorkbook.Activate
    pathmacrobook = ThisWorkbook.Path & "\" & Worksheets("sheet1").Cells(1, 3).Value & "\"  
    
    Set DestBook = Workbooks("残高集計用.xls")  
    namebook = Dir(pathmacrobook & "*.xls")    
    
    Do While Not namebook = ""              
Set myb = DestBook.Worksheets("sheet3").Range("A65536").End(xlUp)  
     With Workbooks.Open(pathmacrobook & namebook)          
 r = aplication.WorksheetFunction.MatchThisWorkbook.Worksheets("sheet1")
.Range("C3:AH3"), namebook.Worksheets("sheet1").Range("C"), 0)
        If r > 0 Then
      .Close False
 Else
 With Workbooks.Open(pathmacrobook & namebook)
       .Worksheets("Sheet1").UsedRange.Offset(1).Copy myb.Offset(1)      
　　　　　lngREC = lngREC + 1           
              .Close False  
        End With
        End If
        namebook = Dir()
  Loop
    Set DestBook = Nothing
       MsgBox lngREC & "日分" & "読込完了しました"

上記のコードについてですが、修飾子が不正です。や、
Loopに対するDoがありません等エラーが出てしまいます。

やりたい事は、"namebook"を開いた時、"Thisworkbook"のsheet3のC列に"namebook"のsheet1のC列があれば、
"namebook"閉じ、そうでなければコピーするというようにしたいです。
どなたかご教授お願いします。

Wendy02 · Accepted Answer

こんにちは。

*
>・MATCH関数を使うのがナンセンスという事でしょうか？
ナンセンス(無意味）ということではありません。きちんとした使い方があります。

まず、以下ですが、
自ブックを動かさないなら、
Dim mySheet As Worksheet
Set mySheet = ThisWorkbook.Worksheets("Sheet1") 'このように、親オブジェクトからまとめると、ひとつになります。
----------------------------------------------
----------------------------------------------
With Workbooks.Open(pathmacrobook & namebook)

> r = WorksheetFunction.Match(ThisWorkbook.Worksheets("sheet1").Range("C3:AH3"), namebook.Worksheets("sheet1").Range("C"), 0)

　 ↓
r =WorksheetFunction.Match(MySheet.Range("C3:AH3"), .Worksheets("sheet1").Range("C:C"), 0)

となりますね。しかし、本来、これでは、

Match(検査値,検査範囲,照合の型) というワークシート関数の形がありますから、その検索値に範囲　"C3:AH3"　を入れたら、そのままでは、VBEditor 側では働きません。それは、ワークシートで、数式を見ても、どのように検索しているか分からないのです。ワークシートで正しく検索できていることが、条件ですから。最後に、もう一度書きます。

>・Set = Workbook(namebook).Worksheets("sheet1").Range("C")
>とすると、ローカルウィンドウでは”Nothing”となっていました。
>・Dim ○ As ○としたときに、オブジェクトがありません。とういう
>時は、Workbook()が足りないという事でよろしいでしょうか？

「　.Worksheets("sheet1").Range("C:C"), 0)」

これは、上の部分をみてください。ワークブックのオブジェクトを確保したら、そのオブジェクトを利用する場合、With ステートメントが書かれているのですから、それを利用します。

**
また、こういう部分は、
Dim myDestSheet As Worksheet
Set myDestSheet = DestBook.Worksheets("sheet3")
----------------------------------------------
----------------------------------------------
> Set myb = DestBook.Worksheets("sheet3").Range("A65536").End(xlUp)
　
Set myb = myDestSheet.Range("A65536").End(xlUp)

とすれば短くなります。

ところで、VBAのコードでは、どうもちぐはぐな感じがしてくるのです。With ステートメントを使っているわりには、ご質問の内容では、なぜか、そのWithステートメントの意味が分かっていらっしゃらなかったりするようです。ここらは、基礎的なもので、VBAの教本の最初の方に、「基本文法」という部分にあります。

また、ワークシート関数の部分は、Excel自体を使う意味では、「致命的」です。他は大した問題とは思いません。そこがクリアしないと、絶対にコードが通らないのです。仮に、私が、お手本を示しましょうと思っても、どうしても、その部分は、意味が分からないので、全体としては、書けないのです。

***
基本的な、サンプルを示します。
一度、研究してみてください。


A列に
a
b
c
・
・
z
と入れて、B1 に検索値を入れます。

Sub TestSample1()
Dim r As Long
r = WorksheetFunction.Match(Range("B1"), Range("A:A"), 0)
If r > 0 Then
　　MsgBox r,vbInformation
End If
End Sub

値が見つかれば、数字が出ますが、値が見つからないと、
-----------------------------------------
実行時エラー '1004':
　WorksheetFunction クラスのMatch プロパティを取得できません。
-----------------------------------------

と出ます。

Sub TestSample2()
Dim r As Long
On Error Resume Next
r = WorksheetFunction.Match(Range("B1"), Range("A:A"), 0)
If r > 0 Then
　MsgBox r,vbInformation
End If
On Error GoTo 0
End Sub

では、こうしたらどうでしょうか？
確かに、エラーは出なくなりました。ところが、これをループしてみるとおかしな現象が出てきます。

今、B1～B3 に、c, a, 3　と入れました。

Sub TestSample3()
　　Dim r As Long
　　Dim i As Long
　　On Error Resume Next
　　For i = 1 To Range("B65536").End(xlUp).Row
　　　　r = WorksheetFunction.Match(Cells(i, 2), Range("A:A"), 0)
　　　　If r > 0 Then
　　　　　　MsgBox r,vbInformation
　　　　End If
　　Next i
　　On Error GoTo 0
End Sub

なぜか、3番目は、2回目と同じ答えが出てきます。
つまり、これでも、うまくないわけです。

そこで、
 r = 0
をr = WorksheetFunction.Match　の前に入れてみると、
初めて、正しい検索がなされます。

ただ、こういう書き方は、正規の書き方で、

掲示板では、Application.Match という書き方が流通しています。

Sub TestSample4()
　　Dim r As Variant　'Long型は不可
　　Dim i As Long
　　On Error Resume Next
　　For i = 1 To Range("B65536").End(xlUp).Row
　　　　r = 0
　　　　r = Application.Match(Cells(i, 2), Range("A:A"), 0)
　　　　If IsError(r) = False Then
　　　　　　MsgBox r, vbInformation
　　　　End If
　　Next i
　　On Error GoTo 0
End Sub

****
>何とか自分の手で完成させたいので、

それは分かるのですが、通らないコードでも、ここを直せばよいというものと、そうでないものがあります。成り立たない数式では、他人には意味が分からないのです。

ThisWorkbook.Worksheets("sheet1").Range("C3:AH3")
namebook.Worksheets("sheet1").Range("C")

ワークシートでも、
=MATCH(C3:AH3,C,0)

入力した式は正しくありません、と出るのではありませんか？

=MATCH(C3:AH3,C:C,0)
としたら、今度は、#N/A と出ませんか？

これでは、無理なのですね。そこは、VBAではありませんから、回答者側からは、修正できないのです。

Wendy02 · Answer

こんにちは。

>いろいろなVBAに関するサイトで「On Error Resume Next」を使用しないほうが良いと書かれていました

今、いくつかのサイトを読んでみました。書いてある内容の多くは一般論ですが、それでは意味はないと思います。

On Error Resume Next というのは、次の行のコマンドや関数の戻り値のエラーをその中だけで回避できるか、という本質的なところに立ち返らない限りは、簡単に利用はしないほうがよいです。そういう私も、最初は分からなかったです。

On Error Resume Nextを入れると、何事もなかったように、次の行に行ってしまうということです。ところが、値を確保していたりすると、前のものがそのまま残っているので、間違った答えが返ってしまいます。

-------------------------------
Sub TestSample3R ()
　　　）
　　（
On Error Resume Next
For i = 1 To Range("B65536").End(xlUp).Row
　r = 0　　'←ここがミソですね。値をクリアさせる
　r = WorksheetFunction.Match(Cells(i, 2), Range("A:A"), 0)


--------------------------------

それとは別に、VBAでは、Variant 型は、エラー値という値自体も、入れられるのですが、ただ、WorksheetFunction としてしまったら、エラー値ではなく、実行時エラーが発生してしまうから、戻り値では、エラーを救済できないのです。

ただ、これは、XL2000 以降で、その前までは、

 Dim r As Variant 
　r = Application.Match(Cells(i, 2), Range("A:A"), 0)

として、書いていたわけです。そうすると、r には、エラー値が入れられるので、
If r >0 Then

の代わりとして、On Error Resume Next を入れずに

If IsError(r) Then

だけで済みます。

では、なぜ、そう書かないかというと、Microsoft 側では、もう古い書き方を採用していないからです。Application.Match　は、特別な使い方です。ただ、一度、こちらも試してみるとよいと思います。

Wendy02 · Answer

こんにちは。

>r = 0 を入力しましたが、どこが間違っていますか？

良く、サンプルをみてくださいね。

Sub TestSample3R ()
　　Dim r As Long
　　Dim i As Long
　　On Error Resume Next　　'←ここの部分が抜けています。
　　For i = 1 To Range("B65536").End(xlUp).Row
　　　　r = 0
　　　　r = WorksheetFunction.Match(Cells(i, 2), Range("A:A"), 0)
　　　　If r > 0 Then
　　　　　　MsgBox r,vbInformation
　　　　End If
　　Next i
　　On Error GoTo 0　　←ここの部分が抜けています。
End Sub

「On Error Resume Next」（オン・エラー・レジューム・ネックスト）を、ループの前に入れてあげる必要があるのです。それで、エラーが出ても、次のコードに行くように出来ますね。

そして、Match関数の前に、r = 0 を入れてあったので、0のままになりますから、

If r > 0 Then　で、分岐できるわけです。
これで、いかがですか？
ただ、まだ、他の部分は見ていませんので、試してみてください。

なお、「On Error GoTo 0」は、慣例的に、On Error Resume Next を入れた後に、そのトラップは終わりました、というステートメントを後のところに入れます。

Wendy02 · Answer

こんにちは。

aplication. つづりが違いますし、今の段階では、それは要りません。（それは、方針が決まれば必要ですが、変数の宣言の部分から変わってきます。）

namebook　は、オブジェクトではありません。Workbooks(namebook) ではないでしょうか？

Match の検査値の引数は、値(.Value)ですから、Range("C3:AH3")では違います。配列数式で行うとすれば、WorksheetFunction は使えません。
また、
　Worksheets("sheet1").Range("C")
このような範囲のRangeオブジェクトの仕方は出来ないと思います。
Range("C:C") か、Columns(3) などだと思います。
 
WorksheetFunction.Match(
と仮に括弧でくくって数式を正しくしても、この数式の変数のr の逃げ場がありません。それで、On Error Resume でトラップを置いても、On Error Resume では、今度は、r の値の更新がされませんので、今のままでは、r > 0　では分岐できる場合は、エラーがなかったときに限ります。エラートラップする場合は　r は、必ず、r = 0 としなければなりません。他の逃げ方もあります。一般的に、WorksheetFunction は、エラーを返すものは使い方が難しいです。
 
With Workbooks.Open(pathmacrobook & namebook) 
でブックを開いておいて、

If の中で、
With Workbooks.Open(pathmacrobook & namebook)
と、もう一度、開こうとしています。

申し訳ありませんが、おやりになろうとしている意味は分かるのですが、ちょっとミスが多すぎますね。

もう少し、変数を増やして、置き換えたら、分かりやすくなると思います。あまり、頭のオブジェクトから書いていくと、それだけ文字数が増えて、見にくくなってしまいます。がんばって、ひとつずつ、見やすく書き直してみてください。必ず、出来るようになるはずです。

imogasi · Answer

Match関数の後に引数を示す（　は要りませんか。
＞WorksheetFunction.MatchThisWorkbook
＞「Loopに対するDoがありません」とは、私の言うことは無関係のようですが、とりあえず。

pbforce · Answer

どの行でエラーがでるのですか？

VBA .WorksheetFunctionについて

こんにちは。

この回答への補足

こんにちは。

こんにちは。

こんにちは。

Match関数の後に引数を示す（ は要りませんか。

どの行でエラーがでるのですか？

似たような質問が見つかりました

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

関連するカテゴリからQ&Aを探す

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

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

VBA　.WorksheetFunctionについて

Match関数の後に引数を示す（　は要りませんか。