Excel (VBA)

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

 
(Windows 10全般 : Excel 2016)
VLookupのエラー回避
投稿日時: 20/01/22 11:27:07
投稿者: vaioyuki

いつもお世話になっております。
今回はエラー回避が終わりません?の悩みです。
 

With ThisWorkbook.Worksheets(2).Activate
    For i = 2 To GLastRow
        On Error GoTo ErrHandl
        Cells(i, 3) = WorksheetFunction.VLookup(Cells(i, 2), CSerchRange, 20, False) 
        Cells(i, 4) = WorksheetFunction.VLookup(Cells(i, 2), CSerchRange, 21, False) 
        Cells(i, 5) = WorksheetFunction.VLookup(Cells(i, 2), CSerchRange, 23, False) 
        Cells(i, 6) = Trim(WorksheetFunction.VLookup(Cells(i, 2), CSerchRange, 22, False)) 
        Cells(i, 8) = WorksheetFunction.VLookup(Cells(i, 2), CSerchRange, 6, False) 
        Cells(i, 9) = WorksheetFunction.VLookup(Cells(i, 2), CSerchRange, 9, False) 
        Cells(i, 10) = WorksheetFunction.VLookup(Cells(i, 2), CSerchRange, 10, False) 
        Cells(i, 12) = WorksheetFunction.VLookup(Cells(i, 2), RSerchRange, 2, False) 
        Cells(i, 13) = Int(WorksheetFunction.SumIfs(Worksheets(5).Range("G2:G" & DoLastRow), Worksheets(5).Range("A2:A" & DoLastRow), _
                        Cells(i, 2), Worksheets(5).Range("I2:I" & DoLastRow), Paho)) 
        Cells(i, 14) = Int(WorksheetFunction.SumIfs(Worksheets(4).Range("J2:J" & WWLastRow), Worksheets(4).Range("A2:A" & WWLastRow), _
                        Cells(i, 2), Worksheets(4).Range("L2:L" & WWLastRow), PahoGai))
        
        Cells(i, 11) = Cells(i, 10) - Cells(i, 13) - Cells(i, 14)
        Cells(i, 7) = Cells(i, 8) + Cells(i, 9) + Cells(i, 11)
        
        Cells(i, 1) = i - 1
    Next
    
        Exit Sub
ErrHandl:
        Cells(i, 12) = 0
'        err.Clear
        Resume Next
End With

 
Worksheets(2)にはきちんと反映されていますが、次の処理に進みません。
1039件データがありますが試しに10件にしてF8でひとつひとつ処理してみました。
すると Exit Sub で止まり動きません。
 
気になったのは一番最初に全て
 
ErrHandl:
        Cells(i, 12) = 0
        err.Clear
        Resume Next

 
と通過しました。
 
Cells(i,12)に対してだけ行いたい処理なのですが書く場所が間違っているのでしょうか。
 
よろしくお願いします。

回答
投稿日時: 20/01/22 13:59:19
投稿者: mattuwan44

Sub test()
    Dim rngTable As Range
    Dim CSerchRange As Range
    Dim ixRow As Long
    Dim ix As Long
    Dim errNum As Long

    Set CSerchRange = ThisWorkbook.Worksheets(1).Range("A1").CurrentRegion
    With ThisWorkbook.Worksheets(2).UsedRange
        Set rngTable = Intersect(.Cells, .Offset(1))
    End With

    With rngTable
        For ixRow = 1 To .Rows.Count
            errNum = 0
            On Error Resume Next
            ix = WorksheetFunction.Match(.Cells(ixRow, 2), CSerchRange, 0)
            errNum = Err.Number
            On Error GoTo 0

            If errNum = 0 Then
                .Cells(ixRow, 1).Value = ixRow
                .Cells(ixRow, 3).Value = CSerchRange.Cells(ix, 20).Value
                .Cells(ixRow, 4).Value = CSerchRange.Cells(ix, 21).Value
            Else
                .Cells(ixRow, 12).Value = 0
            End If
        Next
    End With
End Sub

 
このような形でエラー回避してはいかがでしょうか?
あと、たくさんWorksheetFunction.VLookupを繰り返すと処理が重くないですか?
Match関数で位置だけ取得しておいて、それを利用してはいかがでしょう?

回答
投稿日時: 20/01/22 15:54:36
投稿者: simple


With ThisWorkbook.Worksheets(2).Activate
がおかしいですね。
With ThisWorkbook.Worksheets(2)
か、
ThisWorkbook.Worksheets(2).Activate
か、どちらかでしょう。
.(ドット)を使っていないから、Withは不要ですねたぶん。
Withの意味を再確認して下さい。
 

>Worksheets(2)にはきちんと反映されていますが、次の処理に進みません。
「次の処理」とは具体的にどのことを言っていますか?

>すると Exit Sub で止まり動きません。
これも意味がわかりません。エラーか何かが起きて、それを実行せずに終了するんですか?
Exit Subとは、プロシージャから抜ける処理ですよね。普通エラーにはならないけどねえ。

引用:
一番最初に全て
ErrHandl:
        Cells(i, 12) = 0
        err.Clear
        Resume Next
と通過しました。
これもおっしゃる意味がわかりません。
エラーが起きなければ実行はされませんよね。そう言う意味じゃなくてですか?
「一番最初に全て通過されました」という日本語の意味が不明です。一番最初?
 
逆質問だらけで恐縮です。

回答
投稿日時: 20/01/22 16:04:48
投稿者: simple

ちなみに、
WorksheetFunction.VLookupではなく

Application.VLookup
を使うと、
エラーが発生してストップせずに、
エラー値という特別な値が返りますから、
IsError(返り値)でそうした事態になっているかの判定ができます。
 
このあたりの話は、↓を参照。
https://www.moug.net/tech/exvba/0100035.html
 
>Cells(i,12)に対してだけ行いたい処理なのですが
ということなら、そこだけ上記の対応すれば、On Errorによるエラー処理は不要です。

回答
投稿日時: 20/01/22 16:42:59
投稿者: simple

もちろん、VLOOKUPを使って何度も該当行を探すのは無駄で、
一度だけMachで行を探せばよいはず、というご指摘はそのとおりです。
 
質問者さんのコードに沿って指摘をしたまでです。そのことを指摘することも無駄ではないでしょう。

投稿日時: 20/01/22 16:56:40
投稿者: vaioyuki

mattuwan44さま
 
ありがとうございます。
すっかり様変わりしてしまって私には高度すぎて理解するのに時間がかかりそうです。(^^;
Match関数も含めてじっくり検討して考えていきます。
 
 
simpleさま
 
ありがとうございます。
この前の作業でWorksheets(3)からWorksheets(2)にコピーをしてくるのですが、
ドットを付けていなかったからそのままWorksheets(3)でどんどん作業が進んでしまってActivateをつけました。
確かにドットを付けていなかったらそうなりますね。アホでした。。。
 
次に、
 

With ThisWorkbook.Worksheets(2).Range("G" & Goukei)  '合計行を記入
    Goukei = GLastRow + FirstRow - 1 '原本の合計記入行

    .Formula = "=INT(SUM(G2:G" & (Goukei - 1) & "))"
    .AutoFill Destination:=.Resize(1, 8)
                
    With ThisWorkbook.Worksheets("原本").Range("A2:N" & Goukei) '罫線を記入
        .Borders.LineStyle = xlContinuous
                    
    End With
End With

 
こちらに進みたいのですが、ひとつずつステップ踏んでいたのですがカーソルが Exit Sub で止まったまま何分経っても実行中のままです。
しかし、ここで止めて確認するとエラー対象のデータには 0 が入力しています。
 
引用:
一番最初に全て
ErrHandl:
        Cells(i, 12) = 0
        err.Clear
        Resume Next
と通過しました。

 
これもひとつずつステップ踏んでいたのですが、最初にエラーもないのに、
 
Cells(i, 3) = WorksheetFunction.VLookup(Cells(i, 2), CSerchRange, 20, False)  のあとに、
 
ErrHandl:
        Cells(i, 12) = 0
' err.Clear
        Resume Next
 
Cells(i, 4) = WorksheetFunction.VLookup(Cells(i, 2), CSerchRange, 21, False)  のあとに、
 
ErrHandl:
        Cells(i, 12) = 0
' err.Clear
        Resume Next
 
という風に一通り終わってから最初は2行目から始まりますので、Cells(2,3)に値が入っていきました。
 
 
わかりにくい説明で申し訳ありませんが、これで伝わりますでしょうか。
よろしくお願いします。

回答
投稿日時: 20/01/22 17:56:17
投稿者: mattuwan44

>ひとつずつステップ踏んでいたのですがカーソルが 
>Exit Sub で止まったまま何分経っても実行中のままです。
 
Exit Sub は 「Subプロシージャを抜ける」という宣言文です。
 
つまり、途中でプログラムを終わりたいというときに書きます。
なので、その次の行には実行は移りません。
終っているので何分たっても次の行が黄色く反転することはありません。
 
>ErrHandl:
> Cells(i, 12) = 0
>' err.Clear
> Resume Next
>End With

の部分はエラーが出たときに
On Error GoTo ErrHandl
という命令文に従ってそのラベル(ErrHandl:)へジャンプしてきたときに、
実行されます。
で、
>Resume Next
という命令でエラーが出た次の行へ戻ります。
 
http://officetanaka.net/excel/vba/tips/tips104.htm

回答
投稿日時: 20/01/24 08:07:10
投稿者: simple

念のためコメントしておきます。
 
(1) 
Exit Subを使う意味はこういうことです。
 ・もしそれが無ければ、エラーが起きないときにも、
     ErrHandl:以下のコードが実行されてしまいます。
  ・それを避けるために、ErrHandl:の直前に Exit SUbを入れているのです。
    そこでプロシージャを脱出しているのです。
 
(2)
ステップ実行したときに
ErrHandl:以下のコードが実行されるということは、
VLOOKUP使用時に、キーが表にないということです。
その場合、
WorksheetFunction.VLookup(Cells(i, 2), CSerchRange,列番号,False)
とすると、キーがなければ、どの列番号を指定しても同様にすべてエラーになるはずで、
そのつどErrHandl:以下が実行されてしまうわけです。
 
ですから、すでに指摘があったように、
最初にMatchを使ってキーの有無をチェックし、
存在する場合に限り、
(VLookupなり、Indexなりを使うか)、行番号、列番号を指定したデータ取得を
すればよいのです。
(なお、VLOOKUPでは何度も行番号を検索しますから、無駄と言えば無駄なわけです。)
 
つまり、

    a = Application.Match(Cells(ixRow, 2), CSerchRange.Columns(1), 0)
    If IsError(a) Then
        'ここにエラー(キーがない)時の対応を記載
    Else
        '正常の場合(キーがある)の対応を記載
        '各列のデータを転記等
    End If
などとすればよいと思います。
On Error GoTo ErrHandlなどの記載は不要です。

投稿日時: 20/01/24 13:52:41
投稿者: vaioyuki

ありがとうございます。
昨日は忙しくて返事が出来なくて申し訳ありませんでした。
 
Match関数、Index関数、頑張って勉強したいと思います。
 
今回は勉強せずに使用するのは怖かったのでとりあえずで仕上げました。
 

With ThisWorkbook.Worksheets(2)
    GLastRow = .Range("B" & Rows.Count).End(xlUp).Row

    FirstRow = 2
    Goukei = GLastRow + FirstRow - 1 '原本の合計記入行
    
    For i = 2 To GLastRow
        .Cells(i, 3) = WorksheetFunction.VLookup(.Cells(i, 2), CSerchRange, 20, False) 
        .Cells(i, 4) = WorksheetFunction.VLookup(.Cells(i, 2), CSerchRange, 21, False) 
        .Cells(i, 5) = WorksheetFunction.VLookup(.Cells(i, 2), CSerchRange, 23, False) 
        .Cells(i, 6) = Trim(WorksheetFunction.VLookup(.Cells(i, 2), CSerchRange, 22, False)) 
        .Cells(i, 8) = WorksheetFunction.VLookup(.Cells(i, 2), CSerchRange, 6, False) 
        .Cells(i, 9) = WorksheetFunction.VLookup(.Cells(i, 2), CSerchRange, 9, False) 
        .Cells(i, 10) = WorksheetFunction.VLookup(.Cells(i, 2), CSerchRange, 10, False) 
        .Cells(i, 13) = Int(WorksheetFunction.SumIfs(Worksheets(5).Range("G2:G" & DoLastRow), Worksheets(5).Range("A2:A" & DoLastRow), _
                        Cells(i, 2), Worksheets(5).Range("I2:I" & DoLastRow), Paho)) 
        Cells(i, 14) = Int(WorksheetFunction.SumIfs(Worksheets(4).Range("J2:J" & WWLastRow), Worksheets(4).Range("A2:A" & WWLastRow), _
                        Cells(i, 2), Worksheets(4).Range("L2:L" & WWLastRow), PahoGai))
        
        .Cells(i, 11) = .Cells(i, 10) - .Cells(i, 13) - .Cells(i, 14)
        .Cells(i, 7) = .Cells(i, 8) + .Cells(i, 9) + .Cells(i, 11)
        
        .Cells(i, 1) = i - 1
    Next
Stop
    
On Error GoTo ErrHandl

    For i = 2 To GLastRow
        .Cells(i, 12) = WorksheetFunction.VLookup(.Cells(i, 2), RSerchRange, 2, False) 
    Next 
        
ErrHandl:
        Cells(i, 12) = 0
        err.Clear
        Resume Next

Stop
     
        With ThisWorkbook.Worksheets("原本").Range("G" & Goukei)  '合計行を記入
            .Formula = "=INT(SUM(G2:G" & (Goukei - 1) & "))"
            .AutoFill Destination:=.Resize(1, 8)
                
            With ThisWorkbook.Worksheets("原本").Range("A2:N" & Goukei) '罫線を記入
                .Borders.LineStyle = xlContinuous
                    
            End With
        End With
End With

 
こちらでWithを抜けた後に Exit Sub をつけたら出来ました。
こちらで話してみて、Cells(i,12)にだけの処理だなと気付くことも出来ました
 
ありがとうございました。