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

Question

まだ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

doara_2011 · Accepted Answer

#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

mu2011 · Answer

>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

doara_2011 · Answer

取りあえず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のみを使用しても目的は果たせるはず。その場合は検索できなかった場合
どうするかを考える必要があります。

以上

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

#1です。

この回答への補足

>With Worksheets("外注一覧").Columns("1:1")

この回答への補足

取りあえずMatchメソッドはWorksheetオブジェクトのメソッドではありません。

この回答への補足

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

今、見られている記事はコレ!

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

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