Error message in VBA code

[hellcat83]

Hi

I wrote some code with significant help from forum members about 5 months back. It worked great, but I've had to change the sheet since and now the code is throwing up the error message 'duplicate declaration in current scope' when I run it. The original code would make entries on different sheets within the same workbook, but I've taken that out. The highlighted error area seems to the the routine title declaration line;

Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''
' This section appends any new exam mark entry with an "e" for later formatting.
Dim DivRg As Range
'''''''''''
'Year 7
If Target.Count > 1 Then Exit Sub
Set DivRg = Range("BI11:BK317")
Set DivRg = Application.Intersect(Target, DivRg)

If Not DivRg Is Nothing Then

Application.EnableEvents = False
Target = Target & "e"
Application.EnableEvents = True

Set DivRg = Nothing
End If
'Year 8
Dim DivRg As Range
If Target.Count > 1 Then Exit Sub
Set DivRg = Range("CR11:CT317")
Set DivRg = Application.Intersect(Target, DivRg)

If Not DivRg Is Nothing Then

Application.EnableEvents = False
Target = Target & "e"
Application.EnableEvents = True

Set DivRg = Nothing
End If
'''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''
Dim Row As Integer
''''''''''''''''''''''''''''''''''''''''
' This section monitors assessment columns, placing the most recent entry for each pupil in
' additional columns in management sheet.
''''''''''''''''''''''''''''''''''''''''
' Biology Assessments Y7
For Row = 11 To 317
    FirstCol = "AA" & Row
    SecCol = "AD" & Row
    ThirdCol = "AM" & Row
    FourthCol = "AO" & Row
    FifthCol = "BI" & Row
    OutCol = "HK" & Row
    If Not Intersect(Target, Range(FirstCol & ":" & SecCol & "," & ThirdCol & ":" & FourthCol & "," & FifthCol)) Is Nothing Then Range(OutCol).Value = Target.Value
Next Row
''''''''''''''''''''''''''''''''''''''''
' Chemistry Assessments Y7
For Row = 11 To 317
    FirstCol = "AE" & Row
    SecCol = "AH" & Row
    ThirdCol = "AP" & Row
    FourthCol = "AV" & Row
    FifthCol = "BJ" & Row
    OutCol = "HL" & Row
    If Not Intersect(Target, Range(FirstCol & ":" & SecCol & "," & ThirdCol & ":" & FourthCol & "," & FifthCol)) Is Nothing Then Range(OutCol).Value = Target.Value
Next Row
''''''''''''''''''''''''''''''''''''''''
' Physics Assessments Y7
For Row = 11 To 317
    FirstCol = "AI" & Row
    SecCol = "AL" & Row
    ThirdCol = "AW" & Row
    FourthCol = "BH" & Row
    FifthCol = "BK" & Row
    OutCol = "HM" & Row
    If Not Intersect(Target, Range(FirstCol & ":" & SecCol & "," & ThirdCol & ":" & FourthCol & "," & FifthCol)) Is Nothing Then Range(OutCol).Value = Target.Value
Next Row
''''''''''''''''''''''''''''''''''''''''
' Biology Assessments Y8
For Row = 11 To 317
    FirstCol = "BL" & Row
    SecCol = "BO" & Row
    ThirdCol = "BX" & Row
    FourthCol = "CA" & Row
    FifthCol = "CR" & Row
    OutCol = "JC" & Row
    If Not Intersect(Target, Range(FirstCol & ":" & SecCol & "," & ThirdCol & ":" & FourthCol & "," & FifthCol)) Is Nothing Then Range(OutCol).Value = Target.Value
Next Row
''''''''''''''''''''''''''''''''''''''''
' Chemistry Assessments Y8
For Row = 11 To 317
    FirstCol = "BP" & Row
    SecCol = "BS" & Row
    ThirdCol = "CB" & Row
    FourthCol = "CE" & Row
    FifthCol = "CS" & Row
    OutCol = "JD" & Row
    If Not Intersect(Target, Range(FirstCol & ":" & SecCol & "," & ThirdCol & ":" & FourthCol & "," & FifthCol)) Is Nothing Then Range(OutCol).Value = Target.Value
Next Row
''''''''''''''''''''''''''''''''''''''''
' Physics Assessments Y8
For Row = 11 To 317
    FirstCol = "BT" & Row
    SecCol = "BW" & Row
    ThirdCol = "CF" & Row
    FourthCol = "CQ" & Row
    FifthCol = "CT" & Row
    OutCol = "JE" & Row
    If Not Intersect(Target, Range(FirstCol & ":" & SecCol & "," & ThirdCol & ":" & FourthCol & "," & FifthCol)) Is Nothing Then Range(OutCol).Value = Target.Value
Next Row
'''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''
End Sub

I'm no expert so its probably an easy fix, but I have no idea at all.

Any ideas???
Thanks
James

 

[Rick Rothstein]

You have this declaration twice within your code...

Dim DivRg As Range

once before "Year 7" and again after "Year 8". You can only declare a variable once in a procedure, so you should remove one of them (it doesn't matter which as VB read all declaration when it is first run before it executes any code).