I've taken over management of a spreadsheet essentially used to log the comming and going of phsyical files.
The main problem I can't seem to fix lies with Marcos in the 'entry' page laid out for users to enter the data, i.e data, author, etc - a code used to skip the cells between entry fields.

(there is a cell seperating entry fields below one another for asthetics only)

the code currently sits as follows:

' oldCell variable used to store the cell which has focus at the end of the macro below
Public oldCell As Range

Private Sub Worksheet_Activate()
Range("E10").Select
Set oldCell = Cells.Range("E10")
End Sub

' RUN EVERY TIME A NEW CELL IS SELECTED IN THE Add Patient sheet.
'
' Used to skip the cells between the entry fields
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

' Variables for the loop
Dim x As Integer
Dim y As Integer
y = 9 ' As in one below the first entry field
' Iterate through the number of entry cells
For x = 0 To (UBound(CellsToAdd) - 1)
' If we are on a cell which is between an entry cell then...
If Target.Address = Range("E" & CStr(y)).Address Then
' If we came from the entry cell above then...
If oldCell.Address = Range("E" & CStr(y - 1)).Address Then
' Change focus to the next entry cell down (1 cell down)
Range("E" & CStr(y + 1)).Select
' If we came from the entry cell below then...
ElseIf oldCell.Address = Range("E" & CStr(y + 1)).Address Then
' Change focus to the previous entry cell (1 cell down)
Range("E" & CStr(y - 1)).Select
End If
End If
' Increase y by 2 (cell spacing in this case)
y = y + 2
Next
' Set the oldCell variable to the current sell ready for when the macro is next called
Set oldCell = ActiveCell
End Sub

(the bold red writing being the point of error - runs a error '9' - subscript out of range)

Any help is much appreciated, I'm at a dead end!! The only ideas I can think of is that I might have to define CellsToAdd?