Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2019)
IndexとMatch構文を用いたVBAでエラーが解消されず困っています。
投稿日時: 22/03/10 01:55:39
投稿者: takasagoo

都内の高校1年です。
 
情報科目の宿題で別シートを参照して、条件に合致したセル情報を転記するというプログラムをVBAで作成しなければなりません。
 
とりあえずテストを作成し、実行しましたがエラーが解消されず困っています。
以下詳細を記載しますので、エラーの解消のためのアドバイスを頂けると助かります。環境はWin10 OS、Excel2019になります。プログラムは初心者です。
 
 
■前提条件
・Excelに2つのシート("原本"と"転記")がある。
・"原本"に「製品型番」(P列)と「注文番号」(Z列)と「納品予定日」(AX列)の3つの情報がある。各注文レコードが何行にも連なっている。
・"転記"にも"原本"と同じ3情報:「製品型番」(O列)と「注文番号」(AD列)があるが「納品予定日」(AE列)は空欄のまま。こちらも"原本"同様、各注文レコードが何行にも連なっている。
・本VBAは"転記"にある「納品予定日」を埋めるために作成されなければならない。
・VBAの作成にあたり、"原本"の情報を参照し、"転記"の「納品予定日」を埋めるプログラムを作成する。
 
■作成したVBA
 
Sub test()
 
Dim ws As Object
Dim ws2 As Object
Dim i As Long
 
Set ws = Worksheets("原本")
Set ws2 = Worksheets("転記")
maxrow = ws2.Cells(Rows.Count, "AD").End(xlUp).Row
i = ActiveCell.Row
 
For i = i To maxrow
 
    Key = ws2.Cells(i, "O")
    Key2 = ws2.Cells(i, "AD")
 
    With Application
    myR = .Index(ws.Range("A1:AX"), .Match(Key & Key2, ws.Range("P:P") & ws.Range("Z:Z"), 0), 50)
 
    If IsError(myR) = True Then
        ws2.Cells(i, "AE") = "型番、注文番号を再度確認してください"
    Else
        ws2.Cells(i, "AE") = myR
    End If
 
    End With
 
Next i
 
End Sub
 
■VBAの補足
・VBAの実行前にアクティブセルを"転記"の「納品予定日」※セル番地上、一番最初にあるもの(セル:AE7) に合わせる。
・上記VBAの中段にあるIndex文の列番号引数にある"50"は"原本"のセルA1から数えて、「納品予定日」(AX列)が50番目に位置しているため設定。
・作成にあたっては以下のサイトを参考。
https://keiyu.xyz/2020/02/27/indexmatchvba/
 
 
■上記VBAを実行したときのエラーメッセージ
実行時エラー'1004'
アプリケーション定義またはオブジェクト定義のエラーです。
 
※ちなみにデバッグは中段の以下構文で黄色表示されます。
myR = .Index(ws.Range("A1:AX"), .Match(Key & Key2, ws.Range("P:P") & ws.Range("Z:Z"), 0), 50)
 

回答
投稿日時: 22/03/10 08:54:40
投稿者: WinArrow
投稿者のウェブサイトに移動

問題は、2つ
(1)ActiveCellについて
>i = ActiveCell.Row
この「ActiveCell」にあります。
 
ActiveCellは、この時にアクテイブになっているシートの
カーソルがあるセルを意味します。
このプロシジャ実行前に、どこんシートのどこのセルのカーソルが存在するかが、明確でない場合、
ActiveCell(Activesheet)を不用意に使用しないことです。
 
(2)Index関数の使い方
Index関数は、ワークシートのIndex関数ですから、
ワークシートの文法に従って記述しなければいけません。
@ws.Range("A1:AX")
ここは、セル範囲を指定したことになっていませんよね。
A引数の指定について
セル範囲の行と列の交差したセルを取得するのですから
列の指定がありません。
 
細かいところでは、まだ、改善するところがあります。
 
 

回答
投稿日時: 22/03/10 08:58:49
投稿者: R-

■原因
Rangeを&で結合しようとしているのが原因です。
Rangeを&で結合はできません。
1つ目は定義エラーになりますが、2つ目はRangeの結合をしていないので定義エラーにはなりません。
 

myR = .Index(ws.Range("A1:AX100"), .Match(Key & Key2, ws.Range("P:P") & ws.Range("Z:Z"), 0), 50)
myR = .Index(ws.Range("A1:AX100"), .Match(Key, ws.Range("P:P"), 0), 50)

 
またMatch関数の使い方が間違えています。
複数条件をMatch関数で検索する場合、条件となる列を1つにまとめる必要があります。
 
■解決策
複数条件で一致させるのであれば、素直にFor文を2回入れるのが無難です。
本来は配列を使った方が良いのですが、今回は理解しやすいようにセルを直接参照するようにしました。
またなるべく原型を留めるようにしております。
 
Sub test()
 
    Dim ws As Worksheet
    Dim ws2 As Worksheet
    Dim maxrow As Long
    Dim maxrow2 As Long
    Dim i As Long, a As Long
    Dim Key As String, Key2 As String
    
    Set ws = Worksheets("原本")
    Set ws2 = Worksheets("転記")
    maxrow = ws.Cells(Rows.Count, "Z").End(xlUp).Row
    maxrow2 = ws2.Cells(Rows.Count, "AD").End(xlUp).Row
    
    With ws2
        For i = 2 To maxrow2
            Key = .Cells(i, "O")
            Key2 = .Cells(i, "AD")
            
            For a = 2 To maxrow
                If ws.Cells(a, "P") = Key And ws.Cells(a, "Z") = Key2 Then
                    .Cells(i, "AE") = ws.Cells(a, "AX")
                End If
            Next a
            
            If .Cells(i, "AE") = "" Then
                .Cells(i, "AE") = "型番、注文番号を再度確認してください"
            End If
         
        Next i
    End With
 
End Sub

回答
投稿日時: 22/03/10 09:04:11
投稿者: WinArrow
投稿者のウェブサイトに移動

追加レス
 
今回の場合、セル範囲の中の2つの列を検索し、一致した別セルの値を取得する仕様なので、
INDEX関数では無理だと思います。
 
 
同一「製品型番」には、複数の「注文」があります。
しかし、「注文番号」が一意であるならば、「注文番号」だけをキーにできないのでしょうか?
「注文番号」だけをキーにできるならば、Vlookup関数でも対応可能と思います

回答
投稿日時: 22/03/10 14:14:03
投稿者: WinArrow
投稿者のウェブサイトに移動

レスの訂正

引用:

(2)Index関数の使い方
Index関数は、ワークシートのIndex関数ですから、
ワークシートの文法に従って記述しなければいけません。
@ws.Range("A1:AX")
ここは、セル範囲を指定したことになっていませんよね。
A引数の指定について
セル範囲の行と列の交差したセルを取得するのですから
列の指定がありません。

↑で、「列の指定がありません」と書きましたが、「50」が列指定だったんですね・・・
ここは、訂正します。
 
ところで
データ件数が分からないので、的確ン表現はでいませんが、
R-さんの提案の「For」ループの2階建て方式は、
検索回数を計算してみた方がよいですよ。
「原本」が200件、「転記」が100円だと仮定して
200x100=20000回検索することになります。
ヒットしたら、途中で抜けるというコードを追加しても、平均で半分かな?
10000回検索します。
データ件数を考えたら、配列関数を使うべきと思います。
それは、質問者さんが判断することなので、検討してみてください。

回答
投稿日時: 22/03/10 15:31:41
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:
実行時エラー'1004'
アプリケーション定義またはオブジェクト定義のエラーです。
 

 
このエラーの直接の原因は、
>.Index(ws.Range("A1:AX"), .Match(Key & Key2, ws.Range("P:P") & ws.Range("Z:Z"), 0), 50)
の中の
>ws.Range("A1:AX")
ここです。
「A1:AX」では、セル範囲の指定になっていません。
 
A1:AX100
のように、行指定をすると、エラーは解消します。
 
勿論
MATCH関数の中の
> ws.Range("P:P") & ws.Range("Z:Z")
はNGです。

回答
投稿日時: 22/03/10 15:52:04
投稿者: R-

今回の目的はあくまでも【宿題】である認識です。
質問者さんが理解不能だが処理速度の速いものではなく、理解しやすいロジックを提案しております。
なぜなら実務ではないからです。
その点ご了承のうえ検討ください。

回答
投稿日時: 22/03/10 17:49:40
投稿者: WinArrow
投稿者のウェブサイトに移動

注意事項
  
VBAの中で、ワークシート関数を使う場合
  
今回、Applicationオブジェクトを使っていますが、
WorkSheetFunctionオブジェクトを使う方法もあります。
掲示のコードでは、Index関数と、Match関数。
  
Match関数について、説明します。
Applicationを使った場合、指定の検索キーが存在しない場合、エラーにはなりません。
※今回のMatch関数の引数は問題がありますが、エラーにはならりません。(デバッグで止まりません)
INDEX関数の戻り値に「エラー2042」が返ります。
一方、Worksheetfunctionを使うと実行時エラー1004が発生します。
  
今回の実行時エラー1004は、Index関数に与えたセル範囲が間違っていたが原因です。
前レス参照
 

回答
投稿日時: 22/03/10 18:38:46
投稿者: simple

こんにちは。
高校1年生さんですか。
>プログラムは初心者です。
って、そりゃそうでしょうね、高校生なら。
学習されているのは何という科目ですか?
「情報1,2」などは令和4年度からのようなので、情報A,B,Cのなかのどれかですか?
それらは、Excelのオブジェクトに特化したこうしたことを学習するのですか?
私見では、個別のアプリケーションに余り依存しない、
もっとプログラミングの基礎的なことを学習するのではないかと思っていたのですが、正直驚きました。
 
もちろん、こちらの質問掲示板で普通にやりとりされる内容で回答することもできます。
(極端な話、dictionaryを使って検索するなどという方法もありえます。)
しかし、それで本当に「情報」科目の趣旨に沿った回答なのか、疑問が湧きます。
そういう観点からは、R-さんの二重の繰り返しを使ったものが適切かなと感じます。
 
出題として、Match,Indexの使用が指示されているのですか?それも教えてもらいたいですね。
ちなみに、三学期はまだ終わっていないのですか?

回答
投稿日時: 22/03/10 23:28:00
投稿者: WinArrow
投稿者のウェブサイトに移動

同じことを何回もレスしてすみません。
エラー対応の仕方・・・解析の仕方(エラーの原因を特定すること)は、
今後、VBAに限らず、どんな言語のプログラムにもかならず発生するエラーですから、
きっと勉強になると思います。
 
前提条件は、一応理解しました。
掲示されているコードの中に、「INDEX関数」があります。
これは、INDEX関数を使いなさいという指定があるのでしょうか?
若し、「INDEX関数」が条件でしたら、既に回答があるように、
行検索のMATCH関数の第2引数に複数の列を指定することはできません。
そこでP列セルとZ列セルを文字列結合した作業列を使うことで、
最初の思想を活かすことが可能になります。(対応策)
 
再掲になりますが、
今、問題にしている「実行時エラー1004」は、MATCH関数のところで出ているわけではありません。
INDEX関数の第一引数のセル範囲の指定が間違っていることが原因です。
これを解決すると、次はMATCH関数のチェックになります。
文法として、間違っていませんが、第一引数で指定した検索キーが第2引数のリスト内に存在しないと
エラーになります。
しかし、「Application」オブジェクトを使用している場合、実行時エラーにはならず、
戻り値に「エラー番号」が返ります。
実行時エラーになってくれた方が、救いようがあるのですが、
何の問題もなかったように処理が終了してしまうと、
その結果を使っている個所(同一ブック内とは限らない)でエラーが出ます。
その原因を探すのは、かなり大変です。
ですから、「Application」ではなく「WorksheetFunction」を使う様お勧めします。
 
 

投稿日時: 22/03/11 13:35:13
投稿者: takasagoo

>WinArrowさん
 
丁寧なご解説を頂きありがとうございました。
 
Activecellのご指摘、まさに納得しました。
人の操作によって前提条件が変わる恐れがあるものは排除した方がいいですね。
ありがとうございます。
 
INDEX関数のご指摘もありがとうございます。
コメントを拝見して、INDEX(行、列)Cells(行、列)の構造が同じで、速度の点も考えると
検索用の配列関数がベストだと理解できました。
 
エラー対応の重要性についてもありがとうございます。今後留意します。
また、今後はワークシート関数を利用するようにします。
 
ありがとうございました。
 
 
 
 

投稿日時: 22/03/11 13:39:06
投稿者: takasagoo

>R-さん
 
丁寧なご解説頂きありがとうございました。
 
一部、WinArrowさんへの御礼コメントと重複致しますが、Rangeのご指摘部分について、やはり言語自体の体系的な知識習得がまず必要なことがわかりました。
またFor文についても実務の点ではなく、まず私の習熟度に応じたご提案を用意頂きありがとうございました。
おかげで素早く問題点が理解できました。
 
ありがとうございました。

投稿日時: 22/03/11 13:45:38
投稿者: takasagoo

>Simpleさん
 
コメントありがとうございます。
情報Bになりますが、私が私立高校で担当の先生が結構教科書の内容に触れない独自の問題をいつも出してきます。たまに今回のようにちょっとわからない問題をだしてくるので困っています笑
 
この先生はPythonの講義も授業とは別に、希望する生徒に対して補講、一から教えてくれるので面白いです。
この先生は去年から普通の会社から先生に赴任したみたいで、教科書の中身を見てこれじゃ糞とか、給料もらえないとか毎回言って、いつも教科書と違うことを教えています。
 
ちなみにMatch,Indexの使用が指示されてはおらず、回答があってればなんでもOKということでした。
 
 
 
 

回答
投稿日時: 22/03/11 14:03:11
投稿者: simple

情報Bだそうですが、本当ですか?
私も間違っていたのですが、
情報A,B,Cというのは一世代前のものでした。
https://ja.wikipedia.org/wiki/%E6%83%85%E5%A0%B1_(%E6%95%99%E7%A7%91)
現行の「情報A」、「情報B」、「情報C」の3科目構成を見直し、
平成25年度から「社会と情報」、「情報の科学」の2科目が設けられる。

なお、令和4年度からのものは、情報Tと情報Uでした。
 
ですから高校生を騙った質問ということでしょう。
フラットに質問することを推奨します。

投稿日時: 22/03/11 14:54:31
投稿者: takasagoo

takasagoo さんの引用:
都内の高校1年です。
 
情報科目の宿題で別シートを参照して、条件に合致したセル情報を転記するというプログラムをVBAで作成しなければなりません。
 
とりあえずテストを作成し、実行しましたがエラーが解消されず困っています。
以下詳細を記載しますので、エラーの解消のためのアドバイスを頂けると助かります。環境はWin10 OS、Excel2019になります。プログラムは初心者です。
 
 
■前提条件
・Excelに2つのシート("原本"と"転記")がある。
・"原本"に「製品型番」(P列)と「注文番号」(Z列)と「納品予定日」(AX列)の3つの情報がある。各注文レコードが何行にも連なっている。
・"転記"にも"原本"と同じ3情報:「製品型番」(O列)と「注文番号」(AD列)があるが「納品予定日」(AE列)は空欄のまま。こちらも"原本"同様、各注文レコードが何行にも連なっている。
・本VBAは"転記"にある「納品予定日」を埋めるために作成されなければならない。
・VBAの作成にあたり、"原本"の情報を参照し、"転記"の「納品予定日」を埋めるプログラムを作成する。
 
■作成したVBA
 
Sub test()
 
Dim ws As Object
Dim ws2 As Object
Dim i As Long
 
Set ws = Worksheets("原本")
Set ws2 = Worksheets("転記")
maxrow = ws2.Cells(Rows.Count, "AD").End(xlUp).Row
i = ActiveCell.Row
 
For i = i To maxrow
 
    Key = ws2.Cells(i, "O")
    Key2 = ws2.Cells(i, "AD")
 
    With Application
    myR = .Index(ws.Range("A1:AX"), .Match(Key & Key2, ws.Range("P:P") & ws.Range("Z:Z"), 0), 50)
 
    If IsError(myR) = True Then
        ws2.Cells(i, "AE") = "型番、注文番号を再度確認してください"
    Else
        ws2.Cells(i, "AE") = myR
    End If
 
    End With
 
Next i
 
End Sub
 
■VBAの補足
・VBAの実行前にアクティブセルを"転記"の「納品予定日」※セル番地上、一番最初にあるもの(セル:AE7) に合わせる。
・上記VBAの中段にあるIndex文の列番号引数にある"50"は"原本"のセルA1から数えて、「納品予定日」(AX列)が50番目に位置しているため設定。
・作成にあたっては以下のサイトを参考。
https://keiyu.xyz/2020/02/27/indexmatchvba/
 
 
■上記VBAを実行したときのエラーメッセージ
実行時エラー'1004'
アプリケーション定義またはオブジェクト定義のエラーです。
 
※ちなみにデバッグは中段の以下構文で黄色表示されます。
myR = .Index(ws.Range("A1:AX"), .Match(Key & Key2, ws.Range("P:P") & ws.Range("Z:Z"), 0), 50)