Excel VBA Error Handler Looping Problem 2

[wadjssd]

Hi all,
I've found a similar thread to this as posted previously, but the solution given doesn't seem to work for me.

So, here goes:
I'm performing a VLOOKUP using VBA (it's a massive spreadsheet so the efficiency benefits are huge - plus, it forms part of a much larger automated process).
Clearly there needs to be an error-handler in place to catch the #N/A's that pop up, so I've created the following, which populates the #N/A cells with a 0.

The problem is that the error-handing is only working once. The second VLOOKUP that effectively 'finds' a #N/A doesn't trigger the handler and I get a runtime error - thus bringing my overall process to a grinding and messy halt.
I've cleared the error using Err.Clear (as posted in the simialal problem mentioned above), but it's still happening.

Any help appreciated on this - I'm pulling my hair out.

Cheers,
wadjssd


Sub MyVLookup()

On Error GoTo Handler

For Each c In Selection.Cells
c.Offset(0, 6).Value = Application.WorksheetFunction.VLookup(c.Value, Range("InviteLookup"), 2, False)


Handler:
If Err.Number <> 0 Then
c.Offset(0, 6).Value = 0
Err.Clear
End If

Next c

On Error GoTo 0
End Sub

 

[SkipVought]

Hi,

Sub MyVLookup()
   dim c as range, p


For Each c In Selection.Cells
   p = Application.WorksheetFunction.VLookup(c.Value, Range("InviteLookup"), 2, False)

   If iserror(p) Then
      p = 0
   End If

   c.Offset(0, 6).Value = p

Next c

End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wadjssd]

Thanks Skip,

Unfortunately, when I run your version the code falls over at the first occurrence of a non-matched value in the lookup, almost like it's unable to 'activate' the VLOOKUP procedure without having an Error Handler to refer to....weird. Any more thoughts?

"Runtime error '1004':
Unable to get the vlookup property of the Worksheet Function class"

Cheers,
wadjssd

 

[SkipVought]

Sub MyVLookup()
    Dim c As Range, p

    On Error Resume Next
    
    For Each c In Selection.Cells
       p = Application.WorksheetFunction.VLookup(c.Value, Range("InviteLookup"), 2, False)
    
        If Err.Number <> 0 Then
             p = 0
             Err.Clear
        End If
    
       c.Offset(0, 6).Value = p
    
    Next c
    On Error GoTo 0
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wadjssd]

Excellent - it now works a treat.

Many thanks, Skip!

)

 

[PHV]

The problem is that the error-handing is only working once
FYI, an error-handling procedure should always terminate with a Resume instruction ...