Excel (VBA)

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

 
(Windows 10全般 : Excel 2013)
Index関数の結果が出たり出なかったり
投稿日時: 20/10/14 15:40:29
投稿者: vaioyuki

先ほどは失礼しました。
私の大いなる勘違いでした。
お恥ずかしい。。。
 
リストシートにある会社コートと会社名のリストから、
特殊シートのE列にある会社コードを読み込んで特殊シートのF列に表記したいと思っています。
 

Set SerchI = listSH.Range("A1").CurrentRegion
Set SerchM = listSH.Range("A1:A" & r_cntL)

With tokSH
       
    'F列の会社名をリストシートより取得
    For i = 5 To r_cntT
        On Error Resume Next

        ix = WorksheetFunction.Match(.Cells(i, 5), SerchM, 0) '会社名用
        
        If Err.Number = 0 Then
            .Cells(i, 6).Value = WorksheetFunction.Index(SerchI, ix, 2) '会社名取得
        Else
            .Cells(i, 6).Value = ""
        End If

        On Error GoTo 0

    Next i

End With

 
全く一致してくれないコードもあれば、
同じ「000134」なのに検索結果が表示するのもあればしないのもあります。
(ここで前スレの私の大きな勘違いが出ました。(^^;)
検索結果が表示されないコードに余分は空白があるのでは?と思ったのですがありませんでした。
ちなみに、
 
If Err.Number = 0 Or Err.Number <> 0 Then

 
こちらだと全て表示されました。
でもこれだとエラーも表示されてしまうということですよね?(^^;)
 
毎度毎度申し訳ありませんがよろしくお願いします。

回答
投稿日時: 20/10/14 16:46:44
投稿者: sk

引用:
Set SerchM = listSH.Range("A1:A" & r_cntL)

引用:
On Error Resume Next
 
ix = WorksheetFunction.Match(.Cells(i, 5), SerchM, 0)

引用:
全く一致してくれないコードもあれば、
同じ「000134」なのに検索結果が表示するのもあればしないのもあります

例えば、一方のセルに格納されている値のデータ型が Double 型
(実際の値は 134 という数値。セル書式の設定で見かけ上 000134 と表示されている)で、
もう片方のセルに格納されている値のデータ型が String 型(実際の値は "000134" という
文字列)であるなら、Match メソッドの失敗によって実行時エラーが発生し、
On Error Resume Next ステートメントの効果によって処理が継続され、
その後 Else パートに入ることになるでしょう。
 
とりあえず、それぞれのセルに格納されている値を、
そのデータ型も含めて精査されることをお奨めします。
 
特にワークシート[リスト]側の A 列に関しては、
データ行に数値データと文字列データが混在しないよう、
各セルの値のデータ型を厳格に統一することが
望ましいでしょう。

回答
投稿日時: 20/10/14 17:11:20
投稿者: Suzu

WorkSheetFunction なのですから、マクロにする前に
一般機能の 数式としてセルに 数式を入れ、希望通りに動作する事を確認しましょう。
 
 
セルに 数式を指定し、計算結果を表示するのではなく 値を保存 したいのであれば
対象セルに数式を指定し、計算結果をコピーし値として貼り付ける事も考えてみてはいかがでしょう。
 
その際には、A1形式の 数式ではなく、RC形式の数式を使用すればループ処理は不要となりませんか?

投稿日時: 20/10/14 17:23:31
投稿者: vaioyuki

skさん
 
前スレに引き続き、ありがとうございます。
 
ほとんどのコードが0から始まるので私もそこらへんが関係しているのかな?と思い、
Excelの区切り位置などで特殊シートもリストシートも全て文字列にしたり、
検索結果で出てくるコードをコピーして検索結果に出てこないコードに貼付したりするのですが、
やはり出てきません。
 
Next i の下に stop をかけて確認したのですが、
ix も確認できておりリストシートの行数と変わりありません。
でも結果は出てきません。(;▽;)
 
セルを文字列にするのとは別の方法がありますでしょうか?
 
ちなみに、
セルに同じようにIndex関数とMacth関数を入れて数式を入力したら結果は出てきました。
 

回答
投稿日時: 20/10/14 17:27:55
投稿者: WinArrow
投稿者のウェブサイトに移動

 
素朴な疑問
  
エラー-トラップは、想定できない事象発生に対して使用するのが望ましい。
 指定セル範囲にキー値の存在有無を確認するだけならば、エラートラップは使わないことです。
もし、Match関数以外でエラーがあったときでも、それをキー値無しと判断している・・・なので、
 別なところでエラーになる可能性があります。
むやみにエラートップを使わないようにしましょう。
  
キー値の単純有無判定だったら、Match関数ではなく、CountIf関数で充分です。
  
キー値の存在が確認できたら、Vlookup関数で検索します。
COUNTIF関数で存在を確認していうから、エラーにはなりません。
  
If Worksheetunction.Countif(SerchM, .Cells(i, 5).Value) > 0 then
    .Cells(i, 6).Value = WorksheetFunction.Vlookup(.Cells(i, 5).Value), SerchI, 2, False) nnmtti'
 Else
 アンマッチ
End If
   
なお、キー値とセル範囲の照合列のデータ型は合わせなくていけません。
 

投稿日時: 20/10/14 17:45:08
投稿者: vaioyuki

ありがとうございます。
 
なるほど。
COUNTIFの考えもありますね!!
 
If Worksheetunction.Countif(SerchM, .Cells(i, 5).Value) > 0 then
    .Cells(i, 6).Value = WorksheetFunction.Vlookup(.Cells(i, 5).Value), SerchI, 2, False) nnmtti'
 Else
 アンマッチ
End If
 
あと、教えてください。
このVLOOKUPのあとの「nnmtti」はどういう意味でしょうか?[/code]

回答
投稿日時: 20/10/14 17:58:50
投稿者: WinArrow
投稿者のウェブサイトに移動

>このVLOOKUPのあとの「nnmtti」はどういう意味でしょうか?
 
すみません、入力ミスです。
無視してください。

回答
投稿日時: 20/10/14 18:23:51
投稿者: sk

引用:
ちなみに、
セルに同じようにIndex関数とMacth関数を入れて数式を入力したら結果は出てきました。

ならば、それぞれの関数に渡している[リスト]側のセル範囲を
正しく参照出来ていないか、[特殊]の E 列の走査範囲が
ずれているのではないでしょうか。
 
引用:
Set SerchI = listSH.Range("A1").CurrentRegion

例えば、[リスト]側におけるコード表の範囲が A1:B200 まで
( 201 行目以降は全て空白行、 C 列以降は全て空白列)だとして、
もし A100 セルと B100 セルの両方ともが空白セルである場合、
上記の CurrentRegion プロパティは A1:B99 への参照を返し、
更にそれを SerchI に渡すことになります。
 
引用:
.Cells(i, 6).Value = WorksheetFunction.Index(SerchI, ix, 2)

その場合、当然 A100 以降のセル範囲は Index メソッドにおける
参照範囲から除外されることになります。
 
引用:
Set SerchM = listSH.Range("A1:A" & r_cntL)

引用:
With tokSH
        
    'F列の会社名をリストシートより取得
    For i = 5 To r_cntT

また上記の各変数の値についても、CurrentRegion プロパティを
介して参照した Range オブジェクトから求めているのであれば、
同様に誤った行番号を取得してしまう可能性があります。
 
関連スレッド:
https://www.moug.net/faq/viewtopic.php?t=79903
 
vaioyuki さんの引用:
r_cntL = listSH.Range("A1").CurrentRegion.Rows.Count
r_cntT = tokSH.Range("B3").CurrentRegion.Rows.Count + 2 '特殊シートの最終行…タイトル行があるためプラス2

ともかく、それぞれの CurrentRegion プロパティによって取得されるセル範囲や、
変数 r_cntL, r_cntT の値を再度チェックしてみて下さい。
 
また、Index メソッドが失敗するケースについては、
次のような感じでデバッグを行なってみて下さい。
 
引用:
If Err.Number = 0 Then
    .Cells(i, 6).Value = WorksheetFunction.Index(SerchI, ix, 2) '会社名取得
Else

If Err.Number = 0 Then
    .Cells(i, 6).Value = WorksheetFunction.Index(SerchI, ix, 2) '会社名取得
    If Err.Number <> 0 Then
        Debug.Print Err.Number & ": " & Err.Description
        Debug.Print "SerchI のアドレス:" & SerchI.Address
        Debug.Print "ix の値:" & ix
    End If
Else

回答
投稿日時: 20/10/14 18:47:07
投稿者: mattuwan44

Sub test()
    Dim rngList As Range
    Dim rngKeyWords As Range
    Dim i As Long
    Dim ixRow As Long
    Dim sName As Variant

    Set rngList = Worksheets(1).Range("A1").CurrentRegion.Columns(1)
    With Worksheets("特殊").UsedRange.Columns(5)
        Set rngKeyWord = .Range(.Cells(5), .Cells(.Count))
    End With

    'F列の会社名をリストシートより取得
    For ixRow = 1 To rngNames.Count
        '変数の初期化
        sName = Empty
        i = 0
        
        '検索
        On Error Resume Next
        i = WorksheetFunction.Match(rngKeyWords(ixRow).Text, rngList, 0) '会社名用
        On Error GoTo 0
        
        If i > 0 Then sName = rngList.Cells(i, 2).Value '会社名取得
                
        rngNames.Cells(ixRow, 2).Value = sName
    Next
End Sub

 
1) on error ステートメントでエラー回避をする場合、適用範囲は必要最小限にしましょう。
デバッグが難しくなります。
 
2)何度も再利用する変数は必ず初期化しましょう。
エラーが出た場合無視するようにしているので、
変数への代入式が無視され、前回より前の結果の値が意図せず変数に残ってしまいます。
 
3)変数の用意は必要最小限に。
後で読んで「これは何が入っている?」っていう確認が減ります。
 
4)ステップ実行等のデバッグのやり方を覚えましょう。
今回の件の場合、
ステップ実行しながら、ローカルウィンドウで変数の中身の値を確認していくと、
どこで意図しないことになっているか発見できると思います。
 
5)意図するセルを指定する語彙を増やしましょう。
エクセルVBAではシート上のセルを操作することが多いです。
なので、状況に応じて自在に指定したいです。
 
6)VBAの場合、CellsプロパティがIndex関数と同じ役割をしますので、
敢えてIndex関数を使う必要がないかもです。

投稿日時: 20/10/15 10:01:56
投稿者: vaioyuki

skさん
 
ありがとうございます。
デバッグ、
表示するところと表示しないところの境目を指定して、少し変えて実行してみました。
 

If Err.Number = 0 Then
    .Cells(i, 6).Value = WorksheetFunction.Index(SerchI, ix, 2) '会社名取得
        Debug.Print Err.Number & ": " & Err.Description
        Debug.Print "SerchI のアドレス:" & SerchI.Address
        Debug.Print "SerchM のアドレス:" & SerchM.Address
        Debug.Print "ix の値:" & ix
    ElseIf Err.Number <> 0 Then
        Debug.Print Err.Number & ": " & Err.Description
        Debug.Print "SerchI のアドレス:" & SerchI.Address
        Debug.Print "SerchM のアドレス:" & SerchM.Address
        Debug.Print "ix の値:" & ix
End If

 
【結果】
0:
SerchI のアドレス:$A$1:$B$46
SerchM のアドレス:$A$1:$A$46
ix の値:2
1004: WorksheetFunction クラスの Match プロパティを取得できません。
SerchI のアドレス:$A$1:$B$46
SerchM のアドレス:$A$1:$A$46
ix の値:3
 
Matchプロパティが取得できないということですが、変数ixは「3」と取得できている?
ひとつずつステップ踏んでいても、
 
ix = WorksheetFunction.Match(.Cells(i, 5), SerchM, 0) 

 
の時にはixは「3」となっているんですけどね。。。うーん
 
 
mattuwan44さん
ありがとうございます。
 
変数の変化などを見ているつもりなのですがどうもうまくつかめません。
mattuwan44さんのコードも理解しながらあとで実行してみます。

回答
投稿日時: 20/10/15 10:43:56
投稿者: sk

引用:
ElseIf Err.Number <> 0 Then
    Debug.Print Err.Number & ": " & Err.Description
    Debug.Print "SerchI のアドレス:" & SerchI.Address
    Debug.Print "SerchM のアドレス:" & SerchM.Address
    Debug.Print "ix の値:" & ix

引用:
1004: WorksheetFunction クラスの Match プロパティを取得できません。
SerchI のアドレス:$A$1:$B$46
SerchM のアドレス:$A$1:$A$46
ix の値:3
  
Matchプロパティが取得できないということですが、変数ixは「3」と取得できている?

その時の ix の値は「最後に Match 関数が成功した時の戻り値」です。
Match 関数が失敗したなら ix の値は変更されません。
 
引用:
ix = WorksheetFunction.Match(.Cells(i, 5), SerchM, 0)

この場合は i 行目の E 列のセルにどんな値が格納されているかや、
その値のデータ型を調べるようにして下さい。
 
-------------------------------------------------------------------
With tokSH
    'F列の会社名をリストシートより取得
    For i = 5 To r_cntT
        ix = 0
        On Error Resume Next
        ix = WorksheetFunction.Match(.Cells(i, 5), SerchM, 0) '会社名用
        If Err.Number = 0 Then
            .Cells(i, 6).Value = WorksheetFunction.Index(SerchI, ix, 2) '会社名取得
            'Index 関数の実行に失敗した場合
            If Err.Number <> 0 Then
                Debug.Print Err.Number & ": " & Err.Description
                Debug.Print "i の値:" & i
                Debug.Print "SerchI のアドレス:" & SerchI.Address
                Debug.Print "ix の値:" & ix
                Stop 'デバッグのため処理を一時中断
            End If
        'Match 関数の実行に失敗した場合
        ElseIf Err.Number <> 0 Then
            Debug.Print Err.Number & ": " & Err.Description
            Debug.Print "i の値:" & i
            Debug.Print "SerchM のアドレス:" & SerchM.Address
            Debug.Print .Cells(i, 5).Address & "セルの値:" & .Cells(i, 5).Value
            Debug.Print .Cells(i, 5).Address & "セルのデータ型:" & TypeName(.Cells(i, 5).Value)
            .Cells(i, 6).Value = Empty
        End If
        Err.Clear
        On Error GoTo 0
    Next i
End With
-------------------------------------------------------------------

投稿日時: 20/10/15 11:02:16
投稿者: vaioyuki

skさん
ありがとうございます。
 
そのコードを実行してみたら、
先ほどまで出なかったものも表示するようになりました。(^^;)
 
初期化?
ix=0
とか、
Err.Clear
とかでしょうかね?
 
ちなみに、
その前に
 

        If WorksheetFunction.CountIf(SerchM, .Cells(i, 5).Value) > 0 Then
            .Cells(i, 6).Value = WorksheetFunction.VLookup(.Cells(i, 5), SerchI, 2, False)
        Else
            .Cells(i, 6).Value = ""
        End If

 
こちらに試したところ全て表示しました。

回答
投稿日時: 20/10/15 14:21:57
投稿者: sk

引用:
そのコードを実行してみたら、
先ほどまで出なかったものも表示するようになりました。(^^;)
  
初期化?
ix=0
とか、
Err.Clear
とかでしょうかね?

データ部分の検証内容がはっきりとしていませんので、
今のところ何とも申し上げられません。

投稿日時: 20/10/15 14:36:48
投稿者: vaioyuki

ありがとうございます。
 

    For i = 5 To r_cntT
         ix = 0
        On Error Resume Next
        
        ix = WorksheetFunction.Match(.Cells(i, 5), SerchM, 0) '会社名用

        If WorksheetFunction.CountIf(SerchM, .Cells(i, 5).Value) > 0 Then
            .Cells(i, 6).Value = WorksheetFunction.VLookup(.Cells(i, 5), SerchI, 2, False)
        Else
            .Cells(i, 6).Value = ""
        End If

        Err.Clear
        On Error GoTo 0

    Next i

 
こちらで全て出ました。(^^;)
んー。なんだろう。。。
でも、
初期化する方法を覚えました。
あと、変数をたくさん使わないのも。苦笑
 
いろいろありがとうございました。