VBA Worksheet_Change crashing

133 Views Asked by At

In the following code, when I change Cell B7 the code runs up till the clear command (Line 7). Then it just stops after executing the clear command and if I change B7 again it doesn't trigger Worksheet_Change anymore.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo lastline
If Not Intersect(Target, Target.Worksheet.Range("B7")) Is Nothing Then
    Dim i As Integer
    Dim j As Integer
    Target.Worksheet.Range("A10", "A300").Clear
    j = 10
    For i = 4 To 10
        If ThisWorkbook.Sheets(1).Cells(i, 2) = Target Then
            Application.EnableEvents = False
            Target.Worksheet.Cells(j, 1) = ThisWorkbook.Sheets(1).Cells(i, 3)
            Application.EnableEvents = True
            j = j + 1
        End If
    Next i
End If
Application.EnableEvents = True

lastline:
Application.EnableEvents = True
End Sub

I have tried adding a MsgBox before Line 2 to see if it is a loop, but that message box only runs once.

Edit: In sheet 1 is a list of items along with their tag. There are duplicates in sheet 1, for example: Apple 1, Apple 3. What the code should do is when B7 in Sheet 3 is changed (sheet 3 is the target), It will find all the items in Sheet 1 with the same name as the new value of B7 and update the tag in Sheet 3.

0

There are 0 best solutions below