Error Handling with failed vlookup

[Ark68]

I'm looking for some direction with enhancing this code:

       Case "L18"
            Dim dfcust As String
            Dim wshgrp As Worksheet
            Set wshmain = Worksheets("Main")
            Set wshgrp = Worksheets("Group_Data")
                 
            rnum = Application.Match(Worksheets("varhold").Range("A17"), Worksheets("CONTROL_1").Range("A:A"), 0)
                 
            With Worksheets("CONTROL_1")
                    .Range("F" & rnum) = wshmain.Range("L18")
            End With
                  
            With wshmain
                .Unprotect
                With .Range("Y18:AF18")
                    '.Locked = False
                    .Interior.Color = RGB(112, 156, 148)
                End With
                dfcust = Left(wshmain.Range("L18"), 3)
                .Range("Y18") = Application.VLookup(dfcust & "*", wshgrp.Range("I:J"), 1, False)
                .Protect
            End With

With this code, wshmain.range("Y18") is populated with the value associated with the vlookup. However, problems exist when the vlookup fails. If the vlookup fails, I don't want to try to populate wshmain.range, just simply .protect and abandon.

 

[Norie]

Use a variable for the result of the VLookup just as you do earlier in the code for Match.

Then check to see if it's failed using IsError.

Dim Ans As Variant

' other code

Ans =  Application.VLookup(dfcust & "*", wshgrp.Range("I:J"), 1, False)

If Not IsError(Ans) Then
    .Range("Y18").Value = Ans
End If

PS Why are you using VLookup anyway?

 

[Jonmo1]

Try changing

.Range("Y18") = Application.VLookup(dfcust & "*", wshgrp.Range("I:J"), 1, False)

to

x = Application.VLookup(dfcust & "*", wshgrp.Range("I:J"), 1, False)
If Not IsError(x) Then .Range("Y18") = x

 

[Ark68]

Thanks Folks ...
That seems to have done the trick.

 

[Jonmo1]

Glad to help, thanks for the feedback.