Improving Cell Change Macro

[mikeoly]

Hi Everyone!

I have some code that does what I want, but if you change the entire 'Target' range at once, it takes a while to cycle through. Any thoughts on how to circumvent the 'For' loop, or optimize in another way?

Thank you in advance!



Private Sub worksheet_change(ByVal Target As Range)

For i = 26 To 40
If Not Intersect(Target, Me.Range("B" & i)) Is Nothing Then

Worksheets("Primary").Range("$c$" & i & ":$f$" & i).Formula = "=IF(B" & i & "="""","""",VLOOKUP(B" & i & ",Class,2,FALSE))"

End If
Next

End Sub

[Leith Ross]

Hello mikeoly,

This should run faster.



Private Sub worksheet_change(ByVal Target As Range)


Dim i As Long

Application.EnableEvents = False
Application.Calculation = xlCalculationManual

If Not Intersect(Target, Range("B26:B40")) Is Nothing Then
For i = 26 To 40
Worksheets("Primary").Cells(i, "C").Resize(1, 4).Formula = "=IF(B" & i & "="""","""",VLOOKUP(B" & i & ",Class,2,FALSE))"
Next i
End If

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic


End Sub

[mikeoly]

Thanks, Leith!!