Vvlookup on cell change

Question

I am trying to perform a Vlookup in VBA when a cell value changes, looking up a value based on the cell that has changed.

At the moment I have:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("Frametype")) Is Nothing Then

 On Error GoTo MyErrorHandler:

 Range("Framewidth").Value = Application.WorksheetFunction.VLookup(Sheets("Frame Info").Range("FrameType"), Sheets("Frame Info").Range("FrameTable"), 7, False)

MyErrorHandler:
    If Err.Number = 1004 Then
        Range("Framewidth").Value = "error"
    End If
End If
End Sub

I've tried assigning the VBA VLOOKUP on it's own to a button and it returns the correct value, but when it's inserted into this worksheet change, it only returns the error.

Any help would be great thanks.

Answer 1

The 1004 error probably comes from here:

If Not Intersect(Target, Range("Frametype")) Is Nothing Then

If the Range("Frametype") is not on the worksheet, where the code is. Then the Target cannot intersect it, and it throws an error. Depending on what you are trying to do, change the Intersect() line to this:

If Not Intersect(Target, Range([Frametype].Address)) Is Nothing Then

---

When working with Worksheet_Change event, it is a good idea to disable the events, otherwise the code may probably come into a loop.

Another best practice idea is to exit before the Error Handler, if you can with Exit Sub:

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo MyErrorHandler:
    Application.EnableEvents = False

    If Not Intersect(Target, Range([Frametype].Address)) Is Nothing Then
    'If Not Intersect(Target, Range("Frametype")) Is Nothing Then
        With Worksheets("Frame Info")
            Range("Framewidth").Value = Application.WorksheetFunction.VLookup(.Range("FrameType"), _
                                                                .Range("FrameTable"), 7, False)
        End With
    End If

    Application.EnableEvents = True
    Exit Sub

MyErrorHandler:
    If Err.Number = 1004 Then Range("Framewidth").Value = "error"
    Application.EnableEvents = True

End Sub