VBA FindメソッドとMatch関数のところ

質問

まだVBAに慣れていませんが、下記のソースを書いてみました。
★印の間の部分の処理を、最初はFor Nextで書いていたのですが、理由が解らないですが…うまく処理されない為、タイトルの2種類（セルのFindメソッドとMatch関数)を使って処理しようと思い書き直したのですがうまく処理されません。

どこがいけないのか解らず数時間も悩んでしまいました。
すみませんが、どなたか教えてください。よろしくお願いします。

Sub 外注別案内書作成()
Dim ws As Worksheet 'オブジェクト格納
Dim i As Long, j As Long '繰り返す回数格納
Dim annaicode As Variant '案内場所C格納
Dim addwsname As Variant 'シート名前格納(※案内場所名)
Dim flag As Boolean '真偽
Dim r As Range 'Findメソッドの返り値格納
Dim K As Long 'Match関数の返り値格納

'レポート元でQ列の情報が入っている時に、案内場所別で情報を作成する。
'レポート元でQ列に値がある時に、annaicode変数へ格納。
For i = 2 To Worksheets("レポート元").Cells(Rows.Count, "A").End(xlUp).Row
If Cells(i, "Q").Value <> "" Then
annaicode = Cells(i, "Q").Value
End If

★ココから--------
'外注一覧でannai変数と一致した時に、addwsname変数へ格納。　FindメソッドとMatch関数
With Worksheets("外注一覧").Columns("1:1")
Set r = .Find(What:=annaicode, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns)
If r Is Nothing Then
MsgBox i & "行目の案内場所Cの入力が不正です。" & vbCrLf & "処理を中断しますね", _
vbOKOnly + vbExclamation, "お知らせ"
Else
With Worksheets("外注一覧")
K = .Match(annaicode, .Range(.Cells(1, "A").Value, .Cells(.Rows.Count, "A").Value), 0)
addwsname = .Cells(K, "B").Value + "_案内"
End With
End If
End With
★ココまで--------

'ワークシートコレクション内でaddwsname変数と一致した時に、flag変数をTrueにする。
For Each ws In Worksheets
If ws.Name = addwsname Then
flag = True
End If
Next ws

'flag変数の値により、各々処理をする。
If flag = True Then
Worksheets("レポート元").Cells(i, "A").EntireRow.Copy _
Destination:=Worksheets(addwsname).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
flag = False
Else
Worksheets.Add
ActiveSheet.Name = addwsname
Worksheets("レポート元").Cells(i, "A").EntireRow.Copy _
Destination:=Worksheets(addwsname).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
Next i
End Sub

回答No.3

doara_2011 · ベストアンサー

#1です。With Worksheets("外注一覧").Columns("1:1")については
見逃してました。Columns("A")の他にColumns(1)としても同じです。

Worksheet と WorksheetFunction は単に名前が違う別のものと考えて下さい。
これが違えば.(ドット)以降に書けるメソッドやプロパティも違ってきます。ここら辺を
きっちりと認識しておかないと思わぬ間違いをする元です。
ちなみに、WorksheetFunction はワークシートの関数をVBAで使用する場合の仕掛けです。
Application.Matchも同じ結果を返しますが、検索値がなかった場合の処理が若干異なる
ので注意が必要です。
使用例については以下Urlを参照して下さい。

参考URL：

http://park7.wakwak.com/~efc21/cgi-bin/exqalounge.cgi?print+201005/10050009.txt

お礼

補足にお礼を書いてしまいました。
また質問した際はよろしくお願いいたします。

補足

お返事ありがとうございます。そして連絡遅くなりすみません。
解りやすい解説とURLまでありがとごうざいます。

とても参考になりました。

回答No.2

mu2011

>With Worksheets("外注一覧").Columns("1:1")
　⇒Columnsは列番号なので、Columns("1:1")はあり得ない。
　　ひょっとしてＡ列の間違いでは、Columns("A")

>K = .Match(annaicode, .Range(.Cells(1, "A").Value, .Cells(.Rows.Count, "A").Value), 0)
　⇒Matchはワークシート関数なのでこの使い方はあり得ない。
　　次の様なコードになります。
　　K = Application.Match(annaicode, .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A")), 0)

この二つは同じ事をしているのだから後述のMatchは不要と思う。
With Worksheets("外注一覧").Columns("A")
Set r = .Find(What:=annaicode, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns)
If r Is Nothing Then
MsgBox i & "行目の案内場所Cの入力が不正です。" & vbCrLf & "処理を中断しますね", _
vbOKOnly + vbExclamation, "お知らせ"
Else
addwsname = r.Offset(0,1) + "_案内"
End If
End With

お礼

いつもアドバイスありがとうございます。
身近に相談できる方がいないので、とても助かります。

補足で別の質問をしてしまったのですが、
タイトルと内容が違う為、一度この質問は終了してから新たに質問をしようと思います。

ベストアンサーは悩みましたが、すみませんが、No.１の方へつけようと思います。

ありがとうございました。
そしてまたよろしくお願いいたします。

補足

お返事ありがとうございます。毎回連絡が遅くなってすみません。

>ひょっとしてＡ列の間違いでは、Columns("A")
そこでしたか…思わぬ間違えでした＾＾；

>K = Application.Match(annaicode, .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A")), 0)
なるほど、No.１の方とは頭出しが違いますが、そういう書き方もあるのですね。
今度調べてみます。

>この二つは同じ事をしているのだから後述のMatchは不要と思う。
解りました。アドバイスいただいた通りに書き直してみました。
あと少しおかしい部分も直したのですが、思うように処理出来なかったです。
★１は毎回ワークシートをセレクトしていますが、このやり方でよいのでしょうか？
★２で、空白ではない場合として処理をしているのですが、最終的に　Q列空白セル行とＱ列(外注一覧にはない値)間違った値セル行を処理してしまいます。
どこが問題なのでしょうか？
次々と失敗ばかりで、すみませんがアドバイスよろしくお願いいたします。

Sub 外注別案内書作成()
Dim ws As Worksheet 'オブジェクト格納
Dim i As Long, j As Long '繰り返す回数格納
Dim annaicode As Variant '案内場所C格納
Dim addwsname As Variant 'シート名前格納(※案内場所名)
Dim flag As Boolean '真偽
Dim r As Range 'Findメソッドの返り値格納

'レポート元でQ列の情報が入っている時に、案内場所別で情報を作成する。
'レポート元でQ列に値がある時に、annaicode変数へ格納。
For i = 2 To Worksheets("レポート元").Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("レポート元").Select　　★ココ１
annaicode = ""
MsgBox i
If Cells(i, "Q").Value <> "" Then　　★ココ２
annaicode = Cells(i, "Q").Value
End If

'外注一覧でannai変数と一致した時に、addwsname変数へ格納。　Findメソッド
With Worksheets("外注一覧").Columns("A")
Set r = .Find(What:=annaicode, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns)
If r Is Nothing Then
MsgBox j - 1 & "行目の案内場所Cの入力が不正です。" & vbCrLf & "処理を中断しますね", _
vbOKOnly + vbExclamation, "お知らせ"
Else
addwsname = r.Offset(0, 1) + "_案内"
End If
End With

'ワークシートオブジェクト内でaddwsname変数と一致した時に、flag変数をTrueにする。
For Each ws In Worksheets
If ws.Name = addwsname Then
flag = True
End If
Next ws

'flag変数の値により、各々処理をする。
If flag = True Then
Worksheets("レポート元").Cells(i, "A").EntireRow.Copy _
Destination:=Worksheets(addwsname).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
flag = False
Else
Worksheets.Add
ActiveSheet.Name = addwsname
Worksheets("レポート元").Rows(1).EntireRow.Copy _
Destination:=Worksheets(addwsname).Rows(1)
Worksheets("レポート元").Cells(i, "A").EntireRow.Copy _
Destination:=Worksheets(addwsname).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
Next i
End Sub

回答No.1

doara_2011

取りあえずMatchメソッドはWorksheetオブジェクトのメソッドではありません。
WorksheetFunctionオブジェクトのメソッドです。
よって、
＞With Worksheets("外注一覧")
＞K = .Match(annaicode, .Range(.Cells(1, "A").Value, .Cells(.Rows.Count, "A").Value), 0)
は間違いで、WorksheetFunction.Match( ... と記述する必要があります。

また、Findメソッドが成功した時にMatchを実行していますが、せっかくFindメソッドが
成功しているのにその結果を使用しないのはもったいないです。
＞With Worksheets("外注一覧")
＞K = .Match(annaicode, .Range(.Cells(1, "A").Value, .Cells(.Rows.Count, "A").Value), 0)
の代わりに K = r.Row を使用します。

ようするにFindとMatchの両方を使用する必要はありません。
当然、Matchのみを使用しても目的は果たせるはず。その場合は検索できなかった場合
どうするかを考える必要があります。

以上

補足

お返事ありがとうございます。連絡遅くなりすみません。

>取りあえずMatchメソッドはWorksheetオブジェクトのメソッドではありません。
>WorksheetFunctionオブジェクトのメソッドです。
違いがいまいち解らないですが……FunctionがつくのはWorksheetの返り値を他で使うからってことですか？

>With Worksheets("外注一覧")
>K = .Match(annaicode, .Range(.Cells(1, "A").Value, .Cells(.Rows.Count, "A").Value), 0)
>の代わりに K = r.Row を使用します。
なるほど、そういう使い方があったのですね。
その変数Kを　Cell（K, "B")　として使えば、B列のK行目を取得出来そうですね。

>当然、Matchのみを使用しても目的は果たせるはず。その場合は検索できなかった場合
>どうするかを考える必要があります。
もしMatchで処理をしようとした場合、On Error Resume Next　On Error GoTo 0　を使えばよいのでしょうか？

最後に、アドバイスいただいたK = r.Rowで試しにやってみたのですが、
> With Worksheets("外注一覧").Columns("1:1")
実行時エラー１００４
アプリケーション定義またはオブジェクト定義のエラーです。と出てしまいました。
書き方が間違っているのでしょうか？

よろしくお願いします。