테스트결과 좀 더 빠른 버전-

배열을 안써서 좀 더 복잡해졌네요

 

Sub getJobFile()
    'get files
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    Dim vrtSelectedItem As Variant
   
    Cells(3, 1) = ""
    With fd
        .InitialFileName = "d:\temp\"
        '만약 취소하면 종료처리한다.
        If .Show = 0 Then
            Cells(3, 1) = "작업을 취소하였습니다."
            Cells(3, 1).Font.Color = RGB(240, 0, 0)
            Cells(3, 1).Font.Bold = True
            Exit Sub
        Else
            sPath = .SelectedItems(1)
            tPath = .InitialFileName
           
        End If
    End With
    Set fd = Nothing
   
    Cells(6, 2) = tPath
    Cells(8, 2) = tPath
   
    Set fs = CreateObject("Scripting.FileSystemObject")
    fPath = "D:\temp\jobFiles\"
    tPath = "D:\temp\jobFiles_back\"
   
    Set dDir = fs.getfile(sPath)
    sFName = fs.getfilename(sPath)
    Cells(8, 3) = sFName
   
    '아래 부분은 파일에서 작업일을 가져오는 부분
'    chkpos = Right(sFName, 11)
'    Cells(4, 9) = CDate("20" & Mid(chkpos, 1, 2) & "-" & Mid(chkpos, 3, 2) & "-" & Mid(chkpos, 5, 2))
    '연습용이므로 오늘에서 하루 뺀걸로 그냥 설정
    Cells(4, 9) = Date - 1

   
End Sub
Sub getAndAnalysis()
    Cells(1, 8) = Time
    extractResult
    severeAnalysis
    typeAnalysis
    statAnalysis
    Cells(1, 9) = Time
End Sub

Sub extractResult()

    Dim i As Integer
    Dim rowCnt As Integer
    Dim shtCnt As Integer
    Dim testCnt As Integer
   
    Dim d_name As String
    Dim f_name As String
    Dim file_name As String
   
    '기본 변수 할당 및 파일명 읽어오기
    i = 8
    orgWorkBookName = ActiveWorkbook.Name
   
    '기존 자료 정리
    '작업을 위한 첫 시트는 진척관리
    '두번째시트는 mainData
    Sheets("mainData").Activate
    Range(Cells(3, 2), Cells(20000, 30)).Clear
   
    '파일 가져오기
    '하나의 파일만 처리하는 형태임
    d_name = Sheets("진척관리").Cells(i, 2).Value
    f_name = Sheets("진척관리").Cells(i, 3).Value
   
    file_name = d_name + f_name
   
    '시작
    Dim fs, f, s
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.getfile(file_name)
   
    '파일열기
    Workbooks.Open Filename:=file_name, UpdateLinks:=0
    Workbooks(f_name).Activate
   
    '시트수를 확인하는 건데 이 프로시져에서는 사용하지 않는다
    shtCnt = Workbooks(f_name).Sheets.Count
   
    Sheets("결함").Select
    afCheck = Worksheets("결함").AutoFilterMode
   
    If afCheck = True Then
        Range(Cells(1, 1), Cells(100, 3000)).AutoFilter
    End If
   
    Range(Cells(1, 1), Cells(20000, 30)).Select
    Selection.Copy
   
    Workbooks(orgWorkBookName).Activate
    Sheets("mainData").Select
   
    Cells(5, 4).Select
    ActiveSheet.Paste
   
    Cells(3, 4) = "=counta(E6:E200000)"
   
    '작업후번호
    testCnt = Cells(3, 4)
    Cells(5, 3) = "번호"
    Cells(6, 3) = 1
    Cells(7, 3) = 2
    Range("C6:C7").Select
    Selection.AutoFill Destination:=Range(Cells(6, 3), Cells(testCnt + 5, 3))
   
    Range(Cells(1, 1), Cells(20000, 30)).Select
    Selection.UnMerge
   
    '파일닫기
    Application.DisplayAlerts = False
    Workbooks(f_name).Close SaveChanges:=False
   
    Sheets("mainData").Select
   
End Sub

Sub severeAnalysis()

    Sheets("진척관리").Select
   
    stdDate = Cells(4, 8)
       
    Range(Cells(6, 7), Cells(20000, 30)).Clear
    Cells(4, 9) = "심각도"
    
    Cells(4, 7) = "=counta(h6:h20000)"
   
    Sheets("mainData").Select
    '자동필터를 사용해야 하는데 이미 있으면 오류가 나니 체크해서 있으면 없앤다
    afCheck = Worksheets("mainData").AutoFilterMode
    If afCheck = True Then
        Range(Cells(1, 1), Cells(100, 3000)).AutoFilter
    End If
   
    '다시 자동필터 설정
    Range(Cells(5, 3), Cells(5, 23)).Select
    Selection.AutoFilter
   
   
    ActiveWorkbook.Worksheets("mainData").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("mainData").AutoFilter.Sort.SortFields.Add Key:=Range("E5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("mainData").AutoFilter.Sort .Apply
   
    ActiveWorkbook.Worksheets("mainData").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("mainData").AutoFilter.Sort.SortFields.Add Key:=Range("D5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("mainData").AutoFilter.Sort .Apply
   
    Range(Cells(6, 7), Cells(20000, 7)).Select
    Selection.Copy
    Sheets("진척관리").Select
    Cells(6, 8).Select
    ActiveSheet.Paste
   
    Sheets("mainData").Select
    Range(Cells(6, 9), Cells(20000, 9)).Select
    Selection.Copy
    Sheets("진척관리").Select
    Cells(6, 9).Select
    ActiveSheet.Paste
    '중복값 제거
    ActiveSheet.Range("$H$6:$I$20000").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
   
    Sheets("mainData").Select
    Range(Cells(6, 12), Cells(20000, 12)).Select
    Selection.Copy
    Sheets("진척관리").Select
    Cells(6, 10).Select
    ActiveSheet.Paste
   
    '중복값 제거
    ActiveSheet.Range("$J$6:$J$20000").RemoveDuplicates Columns:=Array(1), Header:=xlYes
       
    Cells(4, 10) = "=counta(j6:j30)"
   
    faultCnt = Cells(4, 10)
   
    Range(Cells(6, 10), Cells(6 + faultCnt, 10)).Select
    Selection.Copy
   
    Cells(5, 10).Select
   
    Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, SkipBlanks:=False, Transpose:=True
   
    Cells(4, 8) = "=counta(H6:H20000)"
    rowCnt = Cells(4, 8)
    Cells(4, 10) = "=cells(j5:az5)"
    faultCnt = Cells(4, 10)
   
    '처음 시작하는 곳은 row 10부터
    '처리가능한 변수는 20개로 설정
    n = 10
    flt1 = Cells(5, n + 0)
    flt2 = Cells(5, n + 1)
    flt3 = Cells(5, n + 2)
    flt4 = Cells(5, n + 3)
    flt5 = Cells(5, n + 4)
    flt6 = Cells(5, n + 5)
    flt7 = Cells(5, n + 6)
    flt8 = Cells(5, n + 7)
    flt9 = Cells(5, n + 8)
    flt10 = Cells(5, n + 9)
    flt11 = Cells(5, n + 10)
    flt12 = Cells(5, n + 11)
    flt13 = Cells(5, n + 12)
    flt14 = Cells(5, n + 13)
    flt15 = Cells(5, n + 14)
    flt16 = Cells(5, n + 15)
    flt17 = Cells(5, n + 16)
    flt18 = Cells(5, n + 17)
    flt19 = Cells(5, n + 18)
    flt20 = Cells(5, n + 19)
   
   
    Cells(5, faultCnt + 10) = "합계"
   
   
    i = 6
    Do While i < rowCnt + 15
   
        Sheets("진척관리").Select
        sysName = Cells(i, 8)
        If sysName = "" Then
            Exit Do
        End If
        dtlName = Cells(i, 9)
        allPtFaultCnt = 0
       
        Sheets("mainData").Select
        recordCnt = Cells(3, 4)
        ptFaultCnt = 0
       
        fltVal1 = 0
        fltVal2 = 0
        fltVal3 = 0
        fltVal4 = 0
        fltVal5 = 0
        fltVal6 = 0
        fltVal7 = 0
        fltVal8 = 0
        fltVal9 = 0
        fltVal10 = 0
        fltVal11 = 0
        fltVal12 = 0
        fltVal13 = 0
        fltVal14 = 0
        fltVal15 = 0
        fltVal16 = 0
        fltVal17 = 0
        fltVal18 = 0
        fltVal19 = 0
        fltVal20 = 0

       
        k = 6
        Do While k < recordCnt + 6
            cmpSysName = Cells(k, 7)
            cmpDtlName = Cells(k, 9)
            cmpFaultName = Cells(k, 12)
           
            If cmpSysName = "" Then
                Exit Do
            End If
           
            If sysName = cmpSysName And dtlName = cmpDtlName And flt1 = cmpFaultName Then fltVal1 = fltVal1 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt2 = cmpFaultName Then fltVal2 = fltVal2 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt3 = cmpFaultName Then fltVal3 = fltVal3 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt4 = cmpFaultName Then fltVal4 = fltVal4 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt5 = cmpFaultName Then fltVal5 = fltVal5 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt6 = cmpFaultName Then fltVal6 = fltVal6 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt7 = cmpFaultName Then fltVal7 = fltVal7 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt8 = cmpFaultName Then fltVal8 = fltVal8 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt9 = cmpFaultName Then fltVal9 = fltVal9 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt10 = cmpFaultName Then fltVal10 = fltVal10 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt11 = cmpFaultName Then fltVal11 = fltVal11 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt12 = cmpFaultName Then fltVal12 = fltVal12 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt13 = cmpFaultName Then fltVal13 = fltVal13 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt14 = cmpFaultName Then fltVal14 = fltVal14 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt15 = cmpFaultName Then fltVal15 = fltVal15 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt16 = cmpFaultName Then fltVal16 = fltVal16 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt17 = cmpFaultName Then fltVal17 = fltVal17 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt18 = cmpFaultName Then fltVal18 = fltVal18 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt19 = cmpFaultName Then fltVal19 = fltVal19 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt20 = cmpFaultName Then fltVal20 = fltVal20 + 1
            End If
            k = k + 1
        Loop
        Sheets("진척관리").Select
       
        If faultCnt1 = 1 Then
            Cells(i, n) = fltVal1
            Cells(i, n + 1) = fltVal1
        ElseIf faultCnt2 = 2 Then
            Cells(i, n) = fltVal2
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal1 + fltVal2
        ElseIf faultCnt3 = 3 Then
            Cells(i, n) = fltVal3
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal1 + fltVal2 + fltVal3
        ElseIf faultCnt4 = 4 Then
            Cells(i, n) = fltVal4
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal1 + fltVal2 + fltVal3 + fltVal4
        ElseIf faultCnt5 = 5 Then
            Cells(i, n) = fltVal5
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5
        ElseIf faultCnt6 = 6 Then
            Cells(i, n) = fltVal6
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6
        ElseIf faultCnt7 = 7 Then
            Cells(i, n) = fltVal7
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7
        ElseIf faultCnt8 = 8 Then
            Cells(i, n) = fltVal8
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8
        ElseIf faultCnt9 = 9 Then
            Cells(i, n) = fltVal9
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9
      ElseIf faultCnt10 = 10 Then
            Cells(i, n) = fltVal10
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10
        ElseIf faultCnt11 = 11 Then
            Cells(i, n) = fltVal11
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11
       ElseIf faultCnt12 = 12 Then
            Cells(i, n) = fltVal12
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12
        ElseIf faultCnt13 = 13 Then
            Cells(i, n) = fltVal13
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal13
            Cells(i, n + 13) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12 + fltVal13
        ElseIf faultCnt14 = 14 Then
            Cells(i, n) = fltVal14
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal13
            Cells(i, n + 13) = fltVal14
            Cells(i, n + 14) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12 + fltVal13 + fltVal14
        ElseIf faultCnt15 = 15 Then
            Cells(i, n) = fltVal15
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal13
            Cells(i, n + 13) = fltVal14
            Cells(i, n + 14) = fltVal15
            Cells(i, n + 15) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12 + fltVal13 + fltVal14 + fltVal15
        ElseIf faultCnt16 = 16 Then
            Cells(i, n) = fltVal16
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal13
            Cells(i, n + 13) = fltVal14
            Cells(i, n + 14) = fltVal15
            Cells(i, n + 15) = fltVal16
            Cells(i, n + 16) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12 + fltVal13 + fltVal14 + fltVal15 + fltVal16

        ElseIf faultCnt17 = 17 Then
            Cells(i, n) = fltVal17
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal13
            Cells(i, n + 13) = fltVal14
            Cells(i, n + 14) = fltVal15
            Cells(i, n + 15) = fltVal16
            Cells(i, n + 16) = fltVal17
            Cells(i, n + 17) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12 + fltVal13 + fltVal14 + fltVal15 + fltVal16 + fltVal17

        ElseIf faultCnt18 = 18 Then
            Cells(i, n) = fltVal18
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal13
            Cells(i, n + 13) = fltVal14
            Cells(i, n + 14) = fltVal15
            Cells(i, n + 15) = fltVal16
            Cells(i, n + 16) = fltVal17
            Cells(i, n + 17) = fltVal18
            Cells(i, n + 18) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12 + fltVal13 + fltVal14 + fltVal15 + fltVal16 + fltVal17 + fltVal18

        ElseIf faultCnt19 = 19 Then
            Cells(i, n) = fltVal19
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal13
            Cells(i, n + 13) = fltVal14
            Cells(i, n + 14) = fltVal15
            Cells(i, n + 15) = fltVal16
            Cells(i, n + 16) = fltVal17
            Cells(i, n + 17) = fltVal18
            Cells(i, n + 18) = fltVal19
            Cells(i, n + 19) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12 + fltVal13 + fltVal14 + fltVal15 + fltVal16 + fltVal17 + fltVal18 + fltVal19
        ElseIf faultCnt20 = 20 Then
            Cells(i, n) = fltVal20
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal13
            Cells(i, n + 13) = fltVal14
            Cells(i, n + 14) = fltVal15
            Cells(i, n + 15) = fltVal16
            Cells(i, n + 16) = fltVal17
            Cells(i, n + 17) = fltVal18
            Cells(i, n + 18) = fltVal19
            Cells(i, n + 19) = fltVal20
            Cells(i, n + 20) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12 + fltVal13 + fltVal14 + fltVal15 + fltVal16 + fltVal17 + fltVal18 + fltVal19 + fltVal20
       
        End If
                   
        j = j + 1
       
    Loop
   
    sumRowPsn = Cells(4, 8) + 6
    typeCnt = Cells(4, 10) + 10
   
    Cells(sumRowPsn, 8) = "합계"
    i = 10
    '옆으로 가면서 합계 넣기
    Do While i < typeCnt + 1
        j = 6
        faultSum = 0
        Do While j < sumRowPsn
            faultVal = Cells(i, j)
            faultSum = faultSum + faultVal
           
            j = j + 1
        Loop
        Cells(i, j) = faultSum
        i = i + 1
       
    Loop
       
    Cells(3, 4).Select
    
End Sub
 
Sub faultAnalysis()
    Sheets("진척관리").Select
       
    '작업위치를 잡는다.
    '위에서 7번째 줄에서부터 데이터가 있다.
    delPnt = Cells(4, 8) + 7
       
    Range(Cells(delPnt, 7), Cells(20000, 30)).Select
    Range(Cells(delPnt, 7), Cells(20000, 30)).Clear
   
    Cells(delPnt + 2, 8).Select
    '상대참조를 해야 할 경우 사용한다.
    ActiveCell.FormulaR1C1 = "=Counta(R[2]c:r[20000]c)"
    Cells(delPnt + 2, 9) = "fault유형"
   
    Sheets("mainData").Select
   
    Range(Cells(6, 7), Cells(20000, 7)).Select
   
    Selection.Copy
   
    Sheets("진척관리").Select
    Cells(delPnt + 3, 8).Select
    ActiveSheet.Paste
   
    Sheets("mainData").Select
    Range(Cells(5, 9), Cells(20000, 9)).Select
   
    Selection.Copy
   
    Sheets("진척관리").Select
    Cells(delPnt + 3, 9).Select
    ActiveSheet.Paste
   
    delPnt0 = delPnt
   
    Cells(delPnt + 2, 8).Select
    ActiveCell.FormulaR1C1 = "=Counta(R[2]c:r[20000]c)"
    delPnt = Cells(delPnt + 2, 8)
   
    '중복값 제거
    ActiveSheet.Range(Cells(delPnt0 + 4, 8), Cells(delPnt + delPnt0 + 4, 9)).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
   
    Cells(delPnt0 + 2, 8).Select
    ActiveCell.FormulaR1C1 = "=Counta(R[2]c:r[20000]c)"
       
    'fault타입 처리
    Sheets("mainData").Select
    Range(Cells(6, 13), Cells(20000, 13)).Select
   
    Selection.Copy
   
    Sheets("진척관리").Select
    Cells(delPnt0 + 4, 10).Select
    ActiveSheet.Paste
       
   
    '중복값 제거
    Range(Cells(delPnt0 + 4, 10), Cells(delPnt + delPnt0 + 3, 10)).Select
    '1
    ActiveSheet.Range(Cells(delPnt0 + 4, 10), Cells(delPnt + delPnt0 + 3, 10)).RemoveDuplicates Columns:=Array(1), Header:=xlNo
    Cells(delPnt0 + 2, 10).Select
    ActiveCell.FormulaR1C1 = "=Counta(R[2]c:r[40]c)"
    '2
    faultCnt = Cells(delPnt0 + 3, 10)
    Range(Cells(delPnt0 + 4, 10), Cells(delPnt + 40, 10)).Select
    Selection.Copy
    '3
    Cells(delPnt0 + 3, 10).Select
    Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, SkipBlanks:=False, Transpose:=True
   
    '4
    Cells(delPnt0 + 2, 10).Select
    ActiveCell.FormulaR1C1 = "=Counta(R[1]c:r[40]c)"
    faultCnt = Cells(delPnt0 + 2, 10)
   
        '처음 시작하는 곳은 row 10부터
    '처리가능한 변수는 20개로 설정
    n = 10
    flt1 = Cells(5, n + 0)
    flt2 = Cells(5, n + 1)
    flt3 = Cells(5, n + 2)
    flt4 = Cells(5, n + 3)
    flt5 = Cells(5, n + 4)
    flt6 = Cells(5, n + 5)
    flt7 = Cells(5, n + 6)
    flt8 = Cells(5, n + 7)
    flt9 = Cells(5, n + 8)
    flt10 = Cells(5, n + 9)
    flt11 = Cells(5, n + 10)
    flt12 = Cells(5, n + 11)
    flt13 = Cells(5, n + 12)
    flt14 = Cells(5, n + 13)
    flt15 = Cells(5, n + 14)
    flt16 = Cells(5, n + 15)
    flt17 = Cells(5, n + 16)
    flt18 = Cells(5, n + 17)
    flt19 = Cells(5, n + 18)
    flt20 = Cells(5, n + 19)
   
   
    Cells(delPnt0 + 3, faultCnt + 10) = "합격"
   
    rowCnt = Cells(delPnt0 + 2, 8)
   
    '5
    i = delPnt0 + 4
    Do While i < rowCnt + delPnt0 + 15
   
        Sheets("진척관리").Select
        sysName = Cells(i, 8)
        If sysName = "" Then
            Exit Do
        End If
        dtlName = Cells(i, 9)
        allPtFaultCnt = 0
        
        Sheets("mainData").Select
        recordCnt = Cells(3, 4)
        ptFaultCnt = 0

        fltVal1 = 0
        fltVal2 = 0
        fltVal3 = 0
        fltVal4 = 0
        fltVal5 = 0
        fltVal6 = 0
        fltVal7 = 0
        fltVal8 = 0
        fltVal9 = 0
        fltVal10 = 0
        fltVal11 = 0
        fltVal12 = 0
        fltVal13 = 0
        fltVal14 = 0
        fltVal15 = 0
        fltVal16 = 0
        fltVal17 = 0
        fltVal18 = 0
        fltVal19 = 0
        fltVal20 = 0
       
        k = 6
        Do While k < recordCnt + 6
            cmpSysName = Cells(k, 7)
            cmpDtlName = Cells(k, 9)
            cmpFaultName = Cells(k, 13)
           
            If cmpSysName = "" Then
                Exit Do
            End If
            
            If sysName = cmpSysName And dtlName = cmpDtlName And flt1 = cmpFaultName Then fltVal1 = fltVal1 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt2 = cmpFaultName Then fltVal2 = fltVal2 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt3 = cmpFaultName Then fltVal3 = fltVal3 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt4 = cmpFaultName Then fltVal4 = fltVal4 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt5 = cmpFaultName Then fltVal5 = fltVal5 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt6 = cmpFaultName Then fltVal6 = fltVal6 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt7 = cmpFaultName Then fltVal7 = fltVal7 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt8 = cmpFaultName Then fltVal8 = fltVal8 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt9 = cmpFaultName Then fltVal9 = fltVal9 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt10 = cmpFaultName Then fltVal10 = fltVal10 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt11 = cmpFaultName Then fltVal11 = fltVal11 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt12 = cmpFaultName Then fltVal12 = fltVal12 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt13 = cmpFaultName Then fltVal13 = fltVal13 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt14 = cmpFaultName Then fltVal14 = fltVal14 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt15 = cmpFaultName Then fltVal15 = fltVal15 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt16 = cmpFaultName Then fltVal16 = fltVal16 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt17 = cmpFaultName Then fltVal17 = fltVal17 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt18 = cmpFaultName Then fltVal18 = fltVal18 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt19 = cmpFaultName Then fltVal19 = fltVal19 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt20 = cmpFaultName Then fltVal20 = fltVal20 + 1
            End If
            k = k + 1
        Loop
        Sheets("진척관리").Select
       
        If faultCnt1 = 1 Then
            Cells(i, n) = fltVal1
            Cells(i, n + 1) = fltVal1
        ElseIf faultCnt2 = 2 Then
            Cells(i, n) = fltVal2
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal1 + fltVal2
        ElseIf faultCnt3 = 3 Then
            Cells(i, n) = fltVal3
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal1 + fltVal2 + fltVal3
        ElseIf faultCnt4 = 4 Then
            Cells(i, n) = fltVal4
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal1 + fltVal2 + fltVal3 + fltVal4
        ElseIf faultCnt5 = 5 Then
            Cells(i, n) = fltVal5
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5
        ElseIf faultCnt6 = 6 Then
            Cells(i, n) = fltVal6
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6
        ElseIf faultCnt7 = 7 Then
            Cells(i, n) = fltVal7
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7
        ElseIf faultCnt8 = 8 Then
            Cells(i, n) = fltVal8
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8
        ElseIf faultCnt9 = 9 Then
            Cells(i, n) = fltVal9
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9
      ElseIf faultCnt10 = 10 Then
            Cells(i, n) = fltVal10
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10
        ElseIf faultCnt11 = 11 Then
            Cells(i, n) = fltVal11
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11
       ElseIf faultCnt12 = 12 Then
            Cells(i, n) = fltVal12
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12
        ElseIf faultCnt13 = 13 Then
            Cells(i, n) = fltVal13
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal13
            Cells(i, n + 13) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12 + fltVal13
        ElseIf faultCnt14 = 14 Then
            Cells(i, n) = fltVal14
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal13
            Cells(i, n + 13) = fltVal14
            Cells(i, n + 14) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12 + fltVal13 + fltVal14
        ElseIf faultCnt15 = 15 Then
            Cells(i, n) = fltVal15
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal13
            Cells(i, n + 13) = fltVal14
            Cells(i, n + 14) = fltVal15
            Cells(i, n + 15) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12 + fltVal13 + fltVal14 + fltVal15
        ElseIf faultCnt16 = 16 Then
            Cells(i, n) = fltVal16
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal13
            Cells(i, n + 13) = fltVal14
            Cells(i, n + 14) = fltVal15
            Cells(i, n + 15) = fltVal16
            Cells(i, n + 16) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12 + fltVal13 + fltVal14 + fltVal15 + fltVal16

        ElseIf faultCnt17 = 17 Then
            Cells(i, n) = fltVal17
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal13
            Cells(i, n + 13) = fltVal14
            Cells(i, n + 14) = fltVal15
            Cells(i, n + 15) = fltVal16
            Cells(i, n + 16) = fltVal17
            Cells(i, n + 17) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12 + fltVal13 + fltVal14 + fltVal15 + fltVal16 + fltVal17

        ElseIf faultCnt18 = 18 Then
            Cells(i, n) = fltVal18
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal13
            Cells(i, n + 13) = fltVal14
            Cells(i, n + 14) = fltVal15
            Cells(i, n + 15) = fltVal16
            Cells(i, n + 16) = fltVal17
            Cells(i, n + 17) = fltVal18
            Cells(i, n + 18) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12 + fltVal13 + fltVal14 + fltVal15 + fltVal16 + fltVal17 + fltVal18

        ElseIf faultCnt19 = 19 Then
            Cells(i, n) = fltVal19
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal13
            Cells(i, n + 13) = fltVal14
            Cells(i, n + 14) = fltVal15
            Cells(i, n + 15) = fltVal16
            Cells(i, n + 16) = fltVal17
            Cells(i, n + 17) = fltVal18
            Cells(i, n + 18) = fltVal19
            Cells(i, n + 19) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12 + fltVal13 + fltVal14 + fltVal15 + fltVal16 + fltVal17 + fltVal18 + fltVal19
        ElseIf faultCnt20 = 20 Then
            Cells(i, n) = fltVal20
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal13
            Cells(i, n + 13) = fltVal14
            Cells(i, n + 14) = fltVal15
            Cells(i, n + 15) = fltVal16
            Cells(i, n + 16) = fltVal17
            Cells(i, n + 17) = fltVal18
            Cells(i, n + 18) = fltVal19
            Cells(i, n + 19) = fltVal20
            Cells(i, n + 20) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12 + fltVal13 + fltVal14 + fltVal15 + fltVal16 + fltVal17 + fltVal18 + fltVal19 + fltVal20
       
        End If
       
        j = j + 1
       
    Loop
 
           
    sysItemCnt = Cells(delPnt0 + 2, 8)
    sumRowPsn = delPnt0 + sysItemCnt + 4
   
    typeCnt = Cells(delPnt0 + 2, 10) + 10
   
    Cells(sumRowPsn, 8) = "합계"
    i = 10
    '옆으로 가면서 합계 넣기
    Do While i < typeCnt + 1
        j = delPnt0 + 4
        faultSum = 0
        Do While j < sumRowPsn
            faultVal = Cells(j, i)
            faultSum = faultSum + faultVal
           
            j = j + 1
        Loop
        Cells(j, i) = faultSum
        i = i + 1
       
    Loop
       
    Cells(3, 4).Select
    
End Sub
 

 

Sub statAnalysis()
    Sheets("진척관리").Select
       
    '작업위치를 잡는다.
    '위에서 7번째 줄에서부터 데이터가 있다.
    delPnt = Cells(4, 8) + 7
       
    Range(Cells(delPnt, 7), Cells(20000, 30)).Select
    Range(Cells(delPnt, 7), Cells(20000, 30)).Clear
   
    Cells(delPnt + 2, 8).Select
    '상대참조를 해야 할 경우 사용한다.
    ActiveCell.FormulaR1C1 = "=Counta(R[2]c:r[20000]c)"
    Cells(delPnt + 2, 9) = "현재상태"
   
    Sheets("mainData").Select
   
    Range(Cells(6, 7), Cells(20000, 7)).Select
   
    Selection.Copy
   
    Sheets("진척관리").Select
    Cells(delPnt + 3, 8).Select
    ActiveSheet.Paste
   
    Sheets("mainData").Select
    Range(Cells(5, 9), Cells(20000, 9)).Select
   
    Selection.Copy
   
    Sheets("진척관리").Select
    Cells(delPnt + 3, 9).Select
    ActiveSheet.Paste
   
    delPnt0 = delPnt
   
    Cells(delPnt + 2, 8).Select
    ActiveCell.FormulaR1C1 = "=Counta(R[2]c:r[20000]c)"
    delPnt = Cells(delPnt + 2, 8)
   
    '중복값 제거
    ActiveSheet.Range(Cells(delPnt0 + 4, 8), Cells(delPnt + delPnt0 + 4, 9)).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
   
    Cells(delPnt0 + 2, 8).Select
    ActiveCell.FormulaR1C1 = "=Counta(R[2]c:r[20000]c)"
       
    'fault타입 처리
    Sheets("mainData").Select
    Range(Cells(6, 4), Cells(20000, 4)).Select
   
    Selection.Copy
   
    Sheets("진척관리").Select
    Cells(delPnt0 + 4, 10).Select
    ActiveSheet.Paste
       
   
    '중복값 제거
    Range(Cells(delPnt0 + 4, 10), Cells(delPnt + delPnt0 + 3, 10)).Select
    '1
    ActiveSheet.Range(Cells(delPnt0 + 4, 10), Cells(delPnt + delPnt0 + 3, 10)).RemoveDuplicates Columns:=Array(1), Header:=xlNo
    Cells(delPnt0 + 2, 10).Select
    ActiveCell.FormulaR1C1 = "=Counta(R[2]c:r[40]c)"
    '2
    faultCnt = Cells(delPnt0 + 3, 10)
    Range(Cells(delPnt0 + 4, 10), Cells(delPnt + 40, 10)).Select
    Selection.Copy
    '3
    Cells(delPnt0 + 3, 10).Select
    Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, SkipBlanks:=False, Transpose:=True
   
    '4
    Cells(delPnt0 + 2, 10).Select
    ActiveCell.FormulaR1C1 = "=Counta(R[1]c:r[40]c)"
    faultCnt = Cells(delPnt0 + 2, 10)
   
   
   
        '처음 시작하는 곳은 row 10부터
    '처리가능한 변수는 20개로 설정
    n = 10
    flt1 = Cells(5, n + 0)
    flt2 = Cells(5, n + 1)
    flt3 = Cells(5, n + 2)
    flt4 = Cells(5, n + 3)
    flt5 = Cells(5, n + 4)
    flt6 = Cells(5, n + 5)
    flt7 = Cells(5, n + 6)
    flt8 = Cells(5, n + 7)
    flt9 = Cells(5, n + 8)
    flt10 = Cells(5, n + 9)
    flt11 = Cells(5, n + 10)
    flt12 = Cells(5, n + 11)
    flt13 = Cells(5, n + 12)
    flt14 = Cells(5, n + 13)
    flt15 = Cells(5, n + 14)
    flt16 = Cells(5, n + 15)
    flt17 = Cells(5, n + 16)
    flt18 = Cells(5, n + 17)
    flt19 = Cells(5, n + 18)
    flt20 = Cells(5, n + 19)
   
   
   
    Cells(delPnt0 + 3, faultCnt + 10) = "합격"
   
    rowCnt = Cells(delPnt0 + 2, 8)
   
    '5
    i = delPnt0 + 4
    Do While i < rowCnt + delPnt0 + 15
   
        Sheets("진척관리").Select
        sysName = Cells(i, 8)
        If sysName = "" Then
            Exit Do
        End If
        dtlName = Cells(i, 9)
        allPtFaultCnt = 0
 
        Sheets("mainData").Select
        recordCnt = Cells(3, 4)
        ptFaultCnt = 0

        fltVal1 = 0
        fltVal2 = 0
        fltVal3 = 0
        fltVal4 = 0
        fltVal5 = 0
        fltVal6 = 0
        fltVal7 = 0
        fltVal8 = 0
        fltVal9 = 0
        fltVal10 = 0
        fltVal11 = 0
        fltVal12 = 0
        fltVal13 = 0
        fltVal14 = 0
        fltVal15 = 0
        fltVal16 = 0
        fltVal17 = 0
        fltVal18 = 0
        fltVal19 = 0
        fltVal20 = 0
       
        k = 6
        Do While k < recordCnt + 6
            cmpSysName = Cells(k, 7)
            cmpDtlName = Cells(k, 9)
            cmpFaultName = Cells(k, 4)
           
            If cmpSysName = "" Then
                Exit Do
            End If

            If sysName = cmpSysName And dtlName = cmpDtlName And flt1 = cmpFaultName Then fltVal1 = fltVal1 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt2 = cmpFaultName Then fltVal2 = fltVal2 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt3 = cmpFaultName Then fltVal3 = fltVal3 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt4 = cmpFaultName Then fltVal4 = fltVal4 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt5 = cmpFaultName Then fltVal5 = fltVal5 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt6 = cmpFaultName Then fltVal6 = fltVal6 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt7 = cmpFaultName Then fltVal7 = fltVal7 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt8 = cmpFaultName Then fltVal8 = fltVal8 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt9 = cmpFaultName Then fltVal9 = fltVal9 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt10 = cmpFaultName Then fltVal10 = fltVal10 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt11 = cmpFaultName Then fltVal11 = fltVal11 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt12 = cmpFaultName Then fltVal12 = fltVal12 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt13 = cmpFaultName Then fltVal13 = fltVal13 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt14 = cmpFaultName Then fltVal14 = fltVal14 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt15 = cmpFaultName Then fltVal15 = fltVal15 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt16 = cmpFaultName Then fltVal16 = fltVal16 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt17 = cmpFaultName Then fltVal17 = fltVal17 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt18 = cmpFaultName Then fltVal18 = fltVal18 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt19 = cmpFaultName Then fltVal19 = fltVal19 + 1
            ElseIf sysName = cmpSysName And dtlName = cmpDtlName And flt20 = cmpFaultName Then fltVal20 = fltVal20 + 1
            End If
           
            k = k + 1
        Loop
        Sheets("진척관리").Select

        If faultCnt1 = 1 Then
            Cells(i, n) = fltVal1
            Cells(i, n + 1) = fltVal1
        ElseIf faultCnt2 = 2 Then
            Cells(i, n) = fltVal2
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal1 + fltVal2
        ElseIf faultCnt3 = 3 Then
            Cells(i, n) = fltVal3
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal1 + fltVal2 + fltVal3
        ElseIf faultCnt4 = 4 Then
            Cells(i, n) = fltVal4
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal1 + fltVal2 + fltVal3 + fltVal4
        ElseIf faultCnt5 = 5 Then
            Cells(i, n) = fltVal5
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5
        ElseIf faultCnt6 = 6 Then
            Cells(i, n) = fltVal6
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6
        ElseIf faultCnt7 = 7 Then
            Cells(i, n) = fltVal7
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7
        ElseIf faultCnt8 = 8 Then
            Cells(i, n) = fltVal8
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8
        ElseIf faultCnt9 = 9 Then
            Cells(i, n) = fltVal9
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9
      ElseIf faultCnt10 = 10 Then
            Cells(i, n) = fltVal10
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10
        ElseIf faultCnt11 = 11 Then
            Cells(i, n) = fltVal11
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11
       ElseIf faultCnt12 = 12 Then
            Cells(i, n) = fltVal12
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12
        ElseIf faultCnt13 = 13 Then
            Cells(i, n) = fltVal13
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal13
            Cells(i, n + 13) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12 + fltVal13
        ElseIf faultCnt14 = 14 Then
            Cells(i, n) = fltVal14
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal13
            Cells(i, n + 13) = fltVal14
            Cells(i, n + 14) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12 + fltVal13 + fltVal14
        ElseIf faultCnt15 = 15 Then
            Cells(i, n) = fltVal15
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal13
            Cells(i, n + 13) = fltVal14
            Cells(i, n + 14) = fltVal15
            Cells(i, n + 15) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12 + fltVal13 + fltVal14 + fltVal15
        ElseIf faultCnt16 = 16 Then
            Cells(i, n) = fltVal16
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal13
            Cells(i, n + 13) = fltVal14
            Cells(i, n + 14) = fltVal15
            Cells(i, n + 15) = fltVal16
            Cells(i, n + 16) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12 + fltVal13 + fltVal14 + fltVal15 + fltVal16

        ElseIf faultCnt17 = 17 Then
            Cells(i, n) = fltVal17
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal13
            Cells(i, n + 13) = fltVal14
            Cells(i, n + 14) = fltVal15
            Cells(i, n + 15) = fltVal16
            Cells(i, n + 16) = fltVal17
            Cells(i, n + 17) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12 + fltVal13 + fltVal14 + fltVal15 + fltVal16 + fltVal17

        ElseIf faultCnt18 = 18 Then
            Cells(i, n) = fltVal18
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal13
            Cells(i, n + 13) = fltVal14
            Cells(i, n + 14) = fltVal15
            Cells(i, n + 15) = fltVal16
            Cells(i, n + 16) = fltVal17
            Cells(i, n + 17) = fltVal18
            Cells(i, n + 18) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12 + fltVal13 + fltVal14 + fltVal15 + fltVal16 + fltVal17 + fltVal18

        ElseIf faultCnt19 = 19 Then
            Cells(i, n) = fltVal19
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal13
            Cells(i, n + 13) = fltVal14
            Cells(i, n + 14) = fltVal15
            Cells(i, n + 15) = fltVal16
            Cells(i, n + 16) = fltVal17
            Cells(i, n + 17) = fltVal18
            Cells(i, n + 18) = fltVal19
            Cells(i, n + 19) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12 + fltVal13 + fltVal14 + fltVal15 + fltVal16 + fltVal17 + fltVal18 + fltVal19
        ElseIf faultCnt20 = 20 Then
            Cells(i, n) = fltVal20
            Cells(i, n + 1) = fltVal2
            Cells(i, n + 2) = fltVal3
            Cells(i, n + 3) = fltVal4
            Cells(i, n + 4) = fltVal5
            Cells(i, n + 5) = fltVal6
            Cells(i, n + 6) = fltVal7
            Cells(i, n + 7) = fltVal8
            Cells(i, n + 8) = fltVal9
            Cells(i, n + 9) = fltVal10
            Cells(i, n + 10) = fltVal11
            Cells(i, n + 11) = fltVal12
            Cells(i, n + 12) = fltVal13
            Cells(i, n + 13) = fltVal14
            Cells(i, n + 14) = fltVal15
            Cells(i, n + 15) = fltVal16
            Cells(i, n + 16) = fltVal17
            Cells(i, n + 17) = fltVal18
            Cells(i, n + 18) = fltVal19
            Cells(i, n + 19) = fltVal20
            Cells(i, n + 20) = fltVal1 + fltVal2 + fltVal3 + fltVal4 + fltVal5 + fltVal6 + fltVal7 + fltVal8 + fltVal9 + fltVal10 + fltVal11 + fltVal12 + fltVal13 + fltVal14 + fltVal15 + fltVal16 + fltVal17 + fltVal18 + fltVal19 + fltVal20
       
        End If
        
        j = j + 1
       
    Loop
 
    sysItemCnt = Cells(delPnt0 + 2, 8)
    sumRowPsn = delPnt0 + sysItemCnt + 4
   
    typeCnt = Cells(delPnt0 + 2, 10) + 10
   
    Cells(sumRowPsn, 8) = "합계"
    i = 10
    '옆으로 가면서 합계 넣기
    Do While i < typeCnt + 1
        j = delPnt0 + 4
        faultSum = 0
        Do While j < sumRowPsn
            faultVal = Cells(j, i)
            faultSum = faultSum + faultVal
           
            j = j + 1
        Loop
        Cells(j, i) = faultSum
        i = i + 1
       
    Loop
       
    Cells(3, 4).Select
    
End Sub