ON error fetches only first occurance

[Oldenhage]

Hi,

in the below small script the "Vlookup" function throws an error message in case no matching value is found. This is correct and expected and should be handled by the "on Error".
But it works only on the first run.
On the second time the "On Error" does not fetch the problem anymore and the code breaks with error 1004 "Unable to get Vlookup property of worksheetfunction class"

Any idea what I need to do to make the "on Error" statement work every time?
I tried "Err.Clear" to start with a "blank sheet" on every new loop, but that did not change anything.

Sub set_comment()
    Zeile = 3
    Spalte = 2
    On Error GoTo gibts_nich
    Do Until Spalte > 52
        Do Until Zeile > 22
            Screen = Cells(Zeile, Spalte).Value
            Name = Application.WorksheetFunction.VLookup(Screen, Range("A40:B173"), 2, False)
            With Cells(Zeile, Spalte)
                .AddComment
                .Comment.Visible = False
                .Comment.Text Text:=Name
            End With
gibts_nich:
           Zeile = Zeile + 1
        Loop
        Spalte = Spalte + 2
    Loop
    On Error GoTo 0
End Sub

Thanks a lot for any hint - or maybe completely different solution to handle the failing vlookup.

regards Ralf

[Corbett]

You need to resume to clear the error

Sub set_comment()
    Zeile = 3
    Spalte = 2
    On Error GoTo gibts_nich
    Do Until Spalte > 52
        Do Until Zeile > 22
            Screen = Cells(Zeile, Spalte).Value
            Name = Application.WorksheetFunction.VLookup(Screen, Range("A40:B173"), 2, False)
            With Cells(Zeile, Spalte)
                .AddComment
                .Comment.Visible = False
                .Comment.Text Text:=Name
            End With
trynext:
           Zeile = Zeile + 1
        Loop
        Spalte = Spalte + 2
    Loop
    On Error GoTo 0
    Exit Sub
gibts_nich:
    Resume trynext
End Sub

[romperstomper]

Alternatively, use Application.Vlookup which returns an error value, not a runtime error:

Sub set_comment()

    For Spalte = 2 to 52
        For Zeile = 3 to 22
            Screen = Cells(Zeile, Spalte).Value
            Name = Application.VLookup(Screen, Range("A40:B173"), 2, False)
            If Not Iserror(Name) then
                With Cells(Zeile, Spalte)
                    .AddComment
                    .Comment.Visible = False
                    .Comment.Text Text:=Name
                End With
            End If
        Next Zeile
    Next SPalte
End Sub

[Oldenhage]

Two relevant answers both including working solutions. Thanks a lot!