Dim iri_left As Single, iri_right As Single
Dim rut_left As Single, rut_right As Single
Dim psi As Single
Dim sinBegin As String, sinEnd As Single
Dim dApply As Date

Sub Search()
'
' search column, creat new sheet, copy data
'
' 快捷键: Ctrl+q
'
    ActiveSheet.Name = "all"                                     '命名主表
   
    Sheets.Add(before:=Sheets(Sheets.Count)).Name = "Total"      '建立Total表,列好表格
    Sheets("Total").Range("A1") = "Year"
    Sheets("Total").Range("B1") = "Time"
    Sheets("Total").Range("C1") = "IRI"
    Sheets("Total").Range("D1") = "Rut"
    Sheets("Total").Range("E1") = "PSI"
    Sheets("Total").Range("F1") = "IRI_left"
    Sheets("Total").Range("G1") = "IRI_right"
    Sheets("Total").Range("H1") = "Rut_left"
    Sheets("Total").Range("I1") = "Rut_right"
   
    Sheets("all").Select                                    '重新激活主表
   
    Dim m As Integer, i As Integer                                   '用于计数
    Dim iBegin(10) As Integer, iEnd(10) As Integer            '记录起始行号
    Dim str As String, str1 As String                                 '输入列号
    Dim sName(10) As String, sTime(10) As String            '记录年份的数组
    m = 0                                                                      '记录年数
    iBegin(m) = 2
    i = 2
    str = "K"                                                                       '输入时间列，K或者L
    sinBegin = InputBox("Please input the begin log mile:")     '输入里程段
    sinEnd = InputBox("Please input the end log mile:")
    dApply = InputBox("Please input the application time:")     '输入应用时间
   
    While Range(str & i) <> ""                           '遍历时间列，存储年份和相应行范围，此处range命令也可改为Range("A" & i)
        If (Range(str & i) <> Range(str & i + 1)) Then
            iEnd(m) = i
            sName(m) = Range(str & i)
            sTime(m) = Range("L" & i)
            m = m + 1
            iBegin(m) = i + 1
        End If
        i = i + 1
    Wend

    Dim n As Integer, time As Integer
    n = 2
    For i = 0 To m - 1                                    '依次建立分表,拷贝数据，并计算
        Sheets.Add(after:=Sheets(Sheets.Count)).Name = sName(i)
        str = iBegin(i) & ":" & iEnd(i)
        str1 = sName(i)
        Sheets("all").Rows(1).copy Sheets(str1).Rows(1)
        Sheets("all").Rows(str).copy Sheets(str1).Rows(2)
       
        Call Calculate                                       '在分表处于激活状态时，计算
       
        Sheets("Total").Range("A" & n) = sTime(i)
        time = sName(i) - dApply
        Sheets("Total").Range("B" & n) = time
        Sheets("Total").Range("E" & n) = psi
        Sheets("Total").Range("E" & n).NumberFormat = "0.00"
        Sheets("Total").Range("F" & n) = iri_left
        Sheets("Total").Range("F" & n).NumberFormat = "0"
        Sheets("Total").Range("G" & n) = iri_right
        Sheets("Total").Range("G" & n).NumberFormat = "0"
        Sheets("Total").Range("H" & n) = rut_left
        Sheets("Total").Range("H" & n).NumberFormat = "0.00"
        Sheets("Total").Range("I" & n) = rut_right
        Sheets("Total").Range("I" & n).NumberFormat = "0.00"
        Sheets("Total").Range("C" & n).FormulaR1C1 = "=AVERAGE(R[0]C[3]:R[0]C[4])"
        Sheets("Total").Range("C" & n).NumberFormat = "0"
        Sheets("Total").Range("D" & n).FormulaR1C1 = "=AVERAGE(R[0]C[4]:R[0]C[5])"
        Sheets("Total").Range("D" & n).NumberFormat = "0.00"
      
        n = n + 1
    Next

End Sub

Public Sub Calculate()
'
'在各分表中选择里程并计算均值
'
'Input the begin and end log mile

    Dim iBegin As Integer, iEnd As Integer, i As Integer       '此iBegin, iEnd用于记录里程范围
    Dim str As String
   
    i = 1                                                      '查询起始行和终止行
    While Range("M" & i) <> ""
        If (Range("M" & i) = sinBegin) Then
            iBegin = i
        End If
        If (Range("M" & i) = sinEnd) Then
            iEnd = i
        End If
        i = i + 1
    Wend
   
    str = "P" & iBegin & ":" & "P" & iEnd                       '计算左IR
    iri_left = Application.WorksheetFunction.average(Range(str))

    str = "O" & iBegin & ":" & "O" & iEnd                       '计算右IRI
    iri_right = Application.WorksheetFunction.average(Range(str))
   
    str = "R" & iBegin & ":" & "R" & iEnd                       '计算左rut
    rut_left = Application.WorksheetFunction.average(Range(str))
   
    str = "Q" & iBegin & ":" & "Q" & iEnd                       '计算右rut
    rut_right = Application.WorksheetFunction.average(Range(str))
   
    str = "U" & iBegin & ":" & "U" & iEnd                       '计算PSI
    psi = Application.WorksheetFunction.average(Range(str))

End Sub