みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

投稿日時 - 2007-12-15 03:55:21

QNo.3599522

困ってます

質問者が選んだベストアンサー

こんにちは。

*
>・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ではありませんから、回答者側からは、修正できないのです。

投稿日時 - 2007-12-18 11:09:15

補足

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 を入力しましたが、どこが間違っていますか?

投稿日時 - 2007-12-19 04:30:45

ANo.4

このQ&Aは役に立ちましたか?

2人が「このQ&Aが役に立った」と投票しています

回答(6)

ANo.6

こんにちは。

>いろいろな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 は、特別な使い方です。ただ、一度、こちらも試してみるとよいと思います。

投稿日時 - 2007-12-21 10:41:50

お礼

Wendy02様
ご教授ありがとうございました。

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

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

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

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

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

投稿日時 - 2007-12-22 01:10:49

ANo.5

こんにちは。

>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 を入れた後に、そのトラップは終わりました、というステートメントを後のところに入れます。

投稿日時 - 2007-12-19 13:34:21

お礼

Wendy02さん
お返事が遅くなって申し訳ありません。

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

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

投稿日時 - 2007-12-21 03:26:41

ANo.3

こんにちは。

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

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

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

投稿日時 - 2007-12-15 12:29:16

お礼

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

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

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

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

投稿日時 - 2007-12-18 03:32:35

ANo.2

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

投稿日時 - 2007-12-15 09:31:59

ANo.1

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

投稿日時 - 2007-12-15 09:05:31

あなたにオススメの質問