VBA .WorksheetFunctionについて

質問

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"閉じ、そうでなければコピーするというようにしたいです。
どなたかご教授お願いします。

回答No.4

Wendy02 · ベストアンサー

こんにちは。

*
>・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様
非常に長くにわたり、ご解説いただきありがとうございます。本当に感謝しています。
まず、MATCH関数の使い方が間違っていました。
いろいろ研究した結果、下記のようにコードを書きました。
IFの部分で、r = 1 になった場合の動作は問題ありませんでした。

Dim DestBook As Workbook
Dim pathmacrobook As String
Dim namebook As String
Dim myb As Range
Dim myasheet As Worksheet
Dim mybsheet As Worksheet
Dim r As Variant
Dim mypath As Workbooks

Application.ScreenUpdating = False '画面の更新をさせない
pathmacrobook = ThisWorkbook.Path & "\" & Worksheets("sheet1").Cells(1, 3).Value & "\" '変数の設定
Set DestBook = Workbooks("残高集計用.xls") '変数の設定
namebook = Dir(pathmacrobook & "*.xls") '変数の設定
Do While Not namebook = "" 'namebookのファイルを全部開くまで続ける
Workbooks.Open (pathmacrobook & namebook)
Set myb = DestBook.Worksheets("sheet3").Range("A65536").End(xlUp) 'sheet3の一番下のセルを選択する
Set myasheet = DestBook.Worksheets("sheet3")
Set mybsheet = Workbooks(namebook).Worksheets("sheet1")
r = 0
r = Application.WorksheetFunction.Match(mybsheet.Range("C2"), myasheet.Range("C2:C65536"), 0)

If r > 0 Then
lngREC2 = lngREC2 + 1
Workbooks(namebook).Close False
Else
mybsheet.UsedRange.Offset(1).Copy myb.Offset(1)
'sheet1の使っている範囲を一段下げて、mybにコピーする
lngREC = lngREC + 1 '回数をカウントする
Workbooks(namebook).Close False 'ファイルを閉じる

End If

namebook = Dir()

Loop
Set DestBook = Nothing
MsgBox lngREC2 & "日分は読込されていました" & vbCr & lngREC & "日分" & "読込完了しました"

※r = Application.WorksheetFunction.Match(mybsheet.Range("C2"), myasheet.Range("C2:C65536"), 0)

ご指摘いただいた通り、上記の部分で
-----------------------------------------
実行時エラー '1004':
　WorksheetFunction クラスのMatch プロパティを取得できません。
-----------------------------------------
が出てしまいます。
r = 0 を入力しましたが、どこが間違っていますか？

回答No.6

Wendy02

こんにちは。

>いろいろな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様
ご教授ありがとうございました。

On Error Resume Nextの使い方は様々なんですね。

・エラーが出ないと分からなくなるので使用しない場合

・エラーが仕様上出てしまうので、使用する場合

※値がリセットされないと言う事はかなり勉強になりました。

詳細にいろいろと教えてくださり本当にありがとうございました。

回答No.5

Wendy02

こんにちは。

>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さん
お返事が遅くなって申し訳ありません。

「On Error Resume Next」をつけてうまくいきました。
いろいろなVBAに関するサイトで「On Error Resume Next」を
使用しないほうが良いと書かれていましたが、このように
使用しないとうまく動かない事もあるんですね。

本当にありがとうございました。

回答No.3

Wendy02

こんにちは。

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)
と、もう一度、開こうとしています。

申し訳ありませんが、おやりになろうとしている意味は分かるのですが、ちょっとミスが多すぎますね。

もう少し、変数を増やして、置き換えたら、分かりやすくなると思います。あまり、頭のオブジェクトから書いていくと、それだけ文字数が増えて、見にくくなってしまいます。がんばって、ひとつずつ、見やすく書き直してみてください。必ず、出来るようになるはずです。

お礼

Wendy02さん
ご回答ありがとうございます。お礼が遅くなってすみません。

考えていたら頭が混乱してきましたので、わからない事を
箇条書きにしてみます。

・MATCH関数を使うのがナンセンスという事でしょうか？
・Set = DestBook.Worksheets("sheet3").Range("C:C")は
ローカルウィンドウを見たところ、大丈夫な気がします。
・Set = Workbook(namebook).Worksheets("sheet1").Range("C")
とすると、ローカルウィンドウでは”Nothing”となっていました。
・Dim ○ As ○としたときに、オブジェクトがありません。とういう
時は、Workbook()が足りないという事でよろしいでしょうか？

何とか自分の手で完成させたいので、変数を増やすヒントを頂けませんか？

回答No.2

imogasi

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

回答No.1

pbforce

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