rivate Sub CommandButton1_Click()

''
' Interpolate data in the Up direction.'
''
' This subroutine uses the existing information on the worksheet about the indiecies'
' of the corresponding train data to search for the desired distance marker. The routine'
' then performs a linear interpolation between successive points.'
''
' Note: The only real difference between the Up and Down interpolation is the Match type'
' in the match function (-1) for UP.'
''
    Dim a As Integer
    ' Declare the loop integers.'
    Dim startIndex As Long, endIndex As Long
    Dim loopIndex As Integer, searchRange As Integer
    Dim offset As Integer, endInterpolation As Long
    Dim lastCell As Range

    ' The number of trains to interpolate data for.'
    Dim numberOfTrains As Integer

    ' Declare the X and Y values for calculating the gradient and intercept.'
    Dim X1 As Double, X2 As Double
    Dim Y1 As Double, Y2 As Double

    ' Gradient and intercept placeholders.'
    Dim gradient As Double, yIntercept As Double
    ' Calculated speed placeholder.'
    Dim interpolatedSpeed As Double

    ' Switch to manual calculation for faster processing.'
    Application.Calculation = xlCalculationManual

    ' Determine the number of trains to interpolate the data for'
    numberOfTrains = WorksheetFunction.Count(Sheet2.Range("I:I"))
    offset = WorksheetFunction.Match("Distance", Sheet3.Range("A:A"), 0) + 1
    Set lastCell = Sheet3.Cells(Sheet3.Rows.Count, "A").End(xlUp)
    endInterpolation = lastCell.Row

    ' Search through each train.'
    For searchRange = 1 To numberOfTrains

        ' Get the start and end indecies of each train.'
        startIndex = Sheet3.Cells(4, 2 + searchRange)
        endIndex = Sheet3.Cells(5, 2 + searchRange)

        ' Loop through the data for interpolation.'
        For loopIndex = offset To endInterpolation

        ' Error Handler accounts for match function not finding the desired index, or a divide by zero error.'
        On Error GoTo ErrHandler:
            ' Get the closest distance values to the interpolated distance form the data.'
            X1 = WorksheetFunction.Index(Sheet2.Range("G" & startIndex, "G" & endIndex), _
                                    WorksheetFunction.Match(Sheet3.Range("A" & loopIndex), Sheet2.Range("G" & startIndex, "G" & endIndex), -1))
            X2 = WorksheetFunction.Index(Sheet2.Range("G" & startIndex, "G" & endIndex), _
                                    WorksheetFunction.Match(Sheet3.Range("A" & loopIndex), Sheet2.Range("G" & startIndex, "G" & endIndex), -1) + 1)
            ' Get the closest speed values for the corresponding distance values form the data.'

            Y1 = WorksheetFunction.Index(Sheet2.Range("H" & startIndex, "H" & endIndex), _
                                    WorksheetFunction.Match(Sheet3.Range("A" & loopIndex), Sheet2.Range("G" & startIndex, "G" & endIndex), -1))
            Y2 = WorksheetFunction.Index(Sheet2.Range("H" & startIndex, "H" & endIndex), _
                                    WorksheetFunction.Match(Sheet3.Range("A" & loopIndex), Sheet2.Range("G" & startIndex, "G" & endIndex), -1) + 1)

            ' Calculate the gradient and y-intercept.'
            gradient = (Y2 - Y1) / (X2 - X1)
            yIntercept = Y1 - gradient * X1

            ' Calculate the new interpolated speed.'
            interpolatedSpeed = gradient * Sheet3.Range("A" & loopIndex) + yIntercept

            ' Place the value in the desired cell.'
Result:
            Sheet3.Cells(loopIndex, 2 + searchRange) = interpolatedSpeed

        Next loopIndex

    Next searchRange

' If there is a zero in the data or a divide by zero error, replace the speed with 0.'
ErrHandler:
    If Err.Number <> 0 Then
        interpolatedSpeed = 0
        Resume Result:
    End If

    ' Revert back to automatic calculation mode.'
    Application.Calculation = xlCalculationAutomatic

    ' Save the active workbook.'
    ActiveWorkbook.Save
End Sub