Code with formula interfering in worksheet change event

[Arnold]

Hello,
When this code is in the worksheet change event, it restricts the
function of other code in worksheet change. Why? Is it about the
placement of the Exit Sub? All this code does is carries formulas in
columns 3 and 10 down to new rows when inserted manually.

If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
On Error Goto CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
.Offset(0, 3).FormulaR1C1 = "=RC[-1]=R2C3"
.Offset(0, 10).FormulaR1C1 = "=RC[-1]=R2C10"
.Offset(0, 2).FormulaR1C1 = "=if(rc[-2]<>"""",rc[-1],"""")"
.Offset(0, 9).FormulaR1C1 =
"=if(rc[-8]<>0,if(rc[-7]<>rc[-8],rc[-9]&"",
""&left(rc[-7],2),rc[-9]&"", ""&left(rc[-8],1)),"""")"
End If
End With
CleanUp:
Application.EnableEvents = True

Also, could you help me with syntax for another vb formula that
essentially is this:
=OFFSET(Column10sHeading,0,246)
which just sets the last column equal to column 10.

Thanks greatly,
Eric

 

[Joerg]

Which other code?
And what do you mean by "restrict"?

 

[Arnold]

Here's the other code. By restrict, I meant that the code below
doesn't work when the above code is in the sheet event. The code below
puts the value of cells in the last column (range is AbbNames) equal to
the cells in column 10 (range Abbreviated), and then updates any
changed values on another sheet, Schedules.

On Error GoTo errHandler

If Target.Count > 1 Then Exit Sub

On Error Resume Next
Set rng = Target.Dependents
On Error GoTo 0
If Not rng Is Nothing Then
Set rng1 = Union(rng, Target)
Else
Set rng1 = Target
End If

On Error GoTo errHandler

If Not Application.Intersect(rng1, _
Application.Range("Abbreviated")) Is Nothing Then
strOld = Target.EntireRow.Range("IV1").Value
strNew = Target.EntireRow.Range("J1").Value

Application.EnableEvents = False
Application.ScreenUpdating = False

Application.Range("AbbNames").Value = _
Application.Range("Abbreviated").Value
End If


Set wsData = Worksheets("Schedules")

On Error GoTo errHandler

Set myRange = wsData.Range("Students")
For Each mycell In myRange
If mycell.Value = "" Then
Else
If strOld <> strNew And strOld <> "" Then

Application.EnableEvents = False
Application.ScreenUpdating = False

wsData.Range("Students").Replace What:=strOld, _
Replacement:=strNew, LookAt:=xlPart, _
SearchOrder:=xlByRows

End If
End If
Next

Application.Range("AbbNames").Value = _
Application.Range("Abbreviated").Value

errHandler:

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

If Err.Number <> 0 Then _
MsgBox "Error occured " & Err.Number & vbNewLine & _
Err.Description

exitHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

Exit Sub

End Sub