VBA stops working when updating formula

[MatthewField]

Good morning,

I have the following code in my excel sheet, however if i update the range table (In bold below) for the VLOOKUP then the code stops working and I have no clue why it's happening or how to stop it. The range table needs to have the flexibility of having new additions added to it.

The code is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)


Application.EnableEvents = False
If Target.CountLarge > 1 Then Exit Sub
If Target.Column = 2 Then
Target.Offset(, 1).FormulaR1C1 = "=VLOOKUP(rc[-1], Fields!r4c2:r23c3, 2, FALSE)"
ElseIf Target.Column = 7 Then
Target.Offset(, 1).FormulaR1C1 = "=VLOOKUP(rc[-1], Fields!r4c6:r79c7, 2, FALSE)"
End If
Application.EnableEvents = True


End Sub

Could anyone possibly help?

Many thanks,

 

[FormR]

Hi, if your first test is true, i.e. more than one cell has been changed, then you exit the sub and never turn events back on - maybe that is the problem?

 

[MatthewField]

Hi, if your first test is true, i.e. more than one cell has been changed, then you exit the sub and never turn events back on - maybe that is the problem?

Thank you for your reply - how would i amend the code to do that? Sorry i'm a complete noob where VBA is concerned.

 

[FormR]

There's a few ways, the easiest is probably to move the line "Application.EnableEvents = False" after the line "If Target.CountLarge > 1 Then Exit Sub"

 

[MatthewField]

There's a few ways, the easiest is probably to move the line "Application.EnableEvents = False" after the line "If Target.CountLarge > 1 Then Exit Sub"

Thank you FormR, it seems to have worked. Much appreciated.