'MACRO/EXCEL-MACRO'에 해당되는 글 34건
- 2023.02.03 엑셀메크로에서 큰 따옴표 넣는 방법
- 2017.01.19 엑셀에서 단어 추출하는 매크로-2
- 2016.10.28 테스트결과 좀 더 빠른 버전-
- 2016.10.27 테스트 결과표 정리 매크로
- 2016.10.21 엑셀에서 특정위치에서 문자 가져오기
- 2016.10.18 자동저장시 시트위치
- 2016.10.17 폴더가 있는지 확인 후 작업하는 방법
- 2016.10.14 엑셀 차트관련 소소한 팁
- 2016.10.14 엑셀 내에서 시트 함수 사용 하기
- 2016.10.14 엑셀의 열이나 행 추가 삭제 시
Countif, Sumif 등을 쓸 경우 아래의 방식으로 처리하면 됩니다.
#따옴표, #엑셀매크로
Cells(9, 3) = "=COUNTif(L3:L2009," & Chr(34) & "일치" & Chr(34) & ")"
'MACRO > EXCEL-MACRO' 카테고리의 다른 글
| 엑셀에서 단어 추출하는 매크로-2 (0) | 2017.01.19 |
|---|---|
| 테스트결과 좀 더 빠른 버전- (0) | 2016.10.28 |
| 테스트 결과표 정리 매크로 (0) | 2016.10.27 |
| 엑셀에서 특정위치에서 문자 가져오기 (0) | 2016.10.21 |
| 자동저장시 시트위치 (0) | 2016.10.18 |
엑셀의 여러 파일을 열어 단어를 추출하는 매크로입니다.
Sub getFolder()
'각종 변수 선언
Dim strPath As String
Dim strNm As String
Dim i As Integer
Dim fdFolder As FileDialog
Dim lngCount As Long
' 현재 있는 데이터를 모두 삭제해야 함.
Sheets("fileSheet").Activate
ActiveSheet.Range("d3").Value = ""
ActiveSheet.Range("b9:f10000").Value = ""
ActiveSheet.Cells(8, 6).Value = 9
Cells(1, 1) = "단어:"
Cells(1, 3) = "을"
Cells(1, 5) = "행"
Cells(1, 7) = "열부터"
Cells(1, 10) = "행"
Cells(1, 12) = "열까지에서 찾기"
Range("B1,D1,F1,I1,K1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Cells(8, 5) = "*.xls*"
'서브폴더의 내용을 가져옴
Set fdFolder = Application.FileDialog(msoFileDialogFolderPicker)
With fdFolder
.Title = "검색할 폴더를 선택 하세요"
If .Show = -1 Then
Range("D8") = .SelectedItems(1) '선택한 폴더명을 A3 셀에 저장
folderspec = Range("D8").Value
SearchSubFolders2
End If
End With
Cells(8, 6) = "=counta(C9:C2000)"
End Sub
Sub extractWord()
'
'변수 선언 integer 는 32767 까지의 값만을 지원한다.
Dim i As Integer
Dim maxVal As Integer
Dim startVal As Integer
Dim nextVal As Integer
Dim fileCnt As Integer ' 파일의 수
Dim sheetCnt As Integer ' 파일의 시트 수
Dim sNo As Integer ' 처리한 시트 수
Dim cellPnt As Integer
Dim rowCnt As Integer
Dim colCnt As Integer
Dim f_name As String '읽고자 하는 파일명
Dim t_name As String '매핑정의서에 기재된 소스테이블명
Dim file_name As String '파일명 전체
Dim targetWord As String '찾고자하는 단어명
Dim cellVal As String '단어를 찾은 셀의 내용
'변수 기본값 할당
i = 9 ' 첫 파일명이 세번째 줄에 있음.
cellPnt = 2 ' 두번째 줄부터 써야 함.
maxVal = 0 ' 초기화
startVal = 1 ' 파일 찾기 시작
nextVal = 0 ' 초기화
'처리할 파일의 갯수\
orgWorkBookName = ActiveWorkbook.Name
fileCnt = Cells(8, 6).Value
targetWord = Sheets("fileSheet").Cells(1, 2).Value
srVal = Sheets("fileSheet").Cells(1, 4).Value
scVal = Sheets("fileSheet").Cells(1, 6).Value
erVal = Sheets("fileSheet").Cells(1, 9).Value
ecVal = Sheets("fileSheet").Cells(1, 11).Value
' 현재 있는 데이터를 모두 삭제해야 함.
Sheets.Add after:=Sheets(1)
Sheets(2).Name = "단어추출-" & Date & Hour(Time) & Minute(Time) & Second(Time)
Sheets(2).Activate
Cells(1, 1).Value = "번호"
Cells(1, 2).Value = "폴더명"
Columns("b:b").ColumnWidth = 20
' Rows("8:8").RowHeight = 35.25
Cells(1, 3).Value = "파일명"
Columns("c:c").ColumnWidth = 40
Cells(1, 4).Value = "시트명"
Columns("d:d").ColumnWidth = 20
Cells(1, 5).Value = "셀위치"
Cells(1, 6).Value = "조회결과"
Columns("f:f").ColumnWidth = 20
Range(Cells(1, 1), Cells(1, 7)).Select
Selection.AutoFilter
'반복하며 파일 처리 함
Do While i < fileCnt + 9
sNo = 1
' ' 파일열기
d_name = Sheets("fileSheet").Cells(i, 2).Value
f_name = Sheets("fileSheet").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
sheetCnt = ActiveWorkbook.Sheets.Count
' '시트 수 만큼 반복하며 확인할 것
Do While sNo <= sheetCnt
Workbooks(f_name).Activate
If Sheets(sNo).Visible = False Then
Else
Sheets(sNo).Select
sName = Sheets(sNo).Name
With Worksheets(sNo).Range(Cells(srVal, scVal), Cells(erVal, ecVal))
Set c = .Find(targetWord, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
cellVal = c.Value
'확인된 시트명을 결과시트에 적기
Workbooks(orgWorkBookName).Activate
Sheets(2).Activate
ActiveSheet.Cells(cellPnt, 2).Select
ActiveSheet.Cells(cellPnt, 1).Value = Str(i - 8) + "/" + Str(fileCnt)
ActiveSheet.Cells(cellPnt, 2).Value = d_name
ActiveSheet.Cells(cellPnt, 3).Value = f_name
ActiveSheet.Cells(cellPnt, 4).Value = sName
anchorinfo = file_name + "#" + sName + "!" + c.Address
ActiveSheet.Hyperlinks.Add anchor:=Cells(cellPnt, 5), Address:=anchorinfo, TextToDisplay:=c.Address
ActiveSheet.Cells(cellPnt, 6).Value = cellVal
cellPnt = cellPnt + 1
Set c = .FindNext(c)
If c Is Nothing Then
Exit Do
End If
Loop While Not c Is Nothing And c.Address <> firstAddress
Else
'확인된 시트명을 결과시트에 적기
Workbooks(orgWorkBookName).Activate
Sheets(2).Activate
' ActiveSheet.Cells(cellPnt, 2).Select
ActiveSheet.Cells(cellPnt, 1).Value = Str(i - 8) + "/" + Str(fileCnt)
ActiveSheet.Cells(cellPnt, 2).Value = d_name
ActiveSheet.Cells(cellPnt, 3).Value = f_name
ActiveSheet.Cells(cellPnt, 4).Value = sName
ActiveSheet.Cells(cellPnt, 5).Value = "없음"
ActiveSheet.Cells(cellPnt, 6).Value = "없음"
cellPnt = cellPnt + 1
End If
End With
End If
Cells(cellPnt, 1).Select
sNo = sNo + 1
Loop
'파일 닫기
Application.DisplayAlerts = False
Workbooks(f_name).Close SaveChanges:=False
i = i + 1
Loop
Range("a1:k1000").Select
With Selection.Font
.Name = "맑은 고딕"
.Size = 10
End With
MsgBox ("작업을 완료하였습니다.")
End Sub
Sub SearchSubFolders2()
Dim result As String
Dim strFilter As String
Dim Msg As String
Dim strDir As String
Dim r As Long
strDir = Range("D8").Value
If strDir = "" Then
MsgBox ("선택된 폴더가 없습니다. 폴더를 선택하세요.")
Exit Sub
End If
r = 8
Sheets(1).Cells(r, 2) = "폴더명"
Sheets(1).Cells(r, 3) = "파일명"
Sheets(2).Range("a1:d1").Font.Name = "Arial"
r = r + 1
If Trim(Right(strDir, 1)) <> "\" Then strDir = strDir & "\"
strFilter = Range("E8").Value
result = sRetrieve(strDir, strFilter, r)
End Sub
Private Function sRetrieve(sPath As String, strFilter As String, r As LoadPictureConstants) As String
Set fs = CreateObject("Scripting.FileSystemObject")
Set dDirs = fs.getFolder(sPath)
For Each dDir In dDirs.SubFolders
sRetrieve = sRetrieve(dDir.Path, strFilter, r) ' Here is the recursion
Next
For Each fFile In dDirs.Files
If fFile.Name Like "~*" Then
ElseIf fFile.Name Like strFilter Then
Sheets(1).Cells(r, 2) = fFile.parentfolder.Path
Sheets(1).Cells(r, 3) = fFile.Name
r = r + 1
End If
Next
Set fs = Nothing
End Function
'MACRO > EXCEL-MACRO' 카테고리의 다른 글
| 엑셀메크로에서 큰 따옴표 넣는 방법 (0) | 2023.02.03 |
|---|---|
| 테스트결과 좀 더 빠른 버전- (0) | 2016.10.28 |
| 테스트 결과표 정리 매크로 (0) | 2016.10.27 |
| 엑셀에서 특정위치에서 문자 가져오기 (0) | 2016.10.21 |
| 자동저장시 시트위치 (0) | 2016.10.18 |
배열을 안써서 좀 더 복잡해졌네요
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
'MACRO > EXCEL-MACRO' 카테고리의 다른 글
| 엑셀메크로에서 큰 따옴표 넣는 방법 (0) | 2023.02.03 |
|---|---|
| 엑셀에서 단어 추출하는 매크로-2 (0) | 2017.01.19 |
| 테스트 결과표 정리 매크로 (0) | 2016.10.27 |
| 엑셀에서 특정위치에서 문자 가져오기 (0) | 2016.10.21 |
| 자동저장시 시트위치 (0) | 2016.10.18 |
업무설명:
- 프로젝트 수행 과정에서 개발이 완료되면 테스트를 수행하게 됩니다.
테스트를 수행하면 테스트 결과를 정리해야 하는데 보통 여러 군데서 수행한 테스트 결과서를 모아서 하나의 결과서에 취합하는 방식으로 진행합니다.
이 결과서의 내용을 분석하여 업무별로, 심각도 별로, 형태별로, 대응상태별로 결과표를 작성하는 매크로입니다.
이 매크로를 사용하는 매크로 파일에는
진척관리시트, mainData 시트가 있어야 합니다.
테스트결과표 파일에는 faultData 시트가 있어야 합니다.
결과는 severe_Level(결함의 심각성 정도), faultType(결함의 형태), presentStatus(현재 조치 상태)를 구분해서 관리해야 합니다.
관리하는 통합테스트결과서의 양식에 맞춰 아래의 매크로를 적당히 수정해서 사용하면 됩니다.
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("faultData").Select
afCheck = Worksheets("faultData").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)
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
j = 10
'심각도 하나씩
'좌에서 우로 하나씩 처리
Do While j < falutCnt + 10
faultName = Cells(5, j)
Sheets("mainData").Select
recordCnt = Cells(3, 4)
ptFaultCnt = 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 faultName = cmpFaultName Then
'테스트 결과
ptFaultCnt = ptFaultCnt + 1
allPtFaultCnt = allPtFaultCnt + 1
End If
k = k + 1
Loop
Sheets("진척관리").Select
Cells(i, j) = ptFaultCnt
j = j + 1
Loop
Cells(i, j).Select
Cells(i, j) = allPtFaultCnt
i = i + 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)
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
j = 10
'좌에서 우로 하나씩 처리
Do While j < falutCnt + 10
faultName = Cells(delPnt0 + 3, j)
If faultName = "" Then
Exit Do
End If
Sheets("mainData").Select
recordCnt = Cells(3, 4)
ptFaultCnt = 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 faultName = cmpFaultName Then
'테스트 결과
ptFaultCnt = ptFaultCnt + 1
allPtFaultCnt = allPtFaultCnt + 1
End If
k = k + 1
Loop
Sheets("진척관리").Select
Cells(i, j) = ptFaultCnt
j = j + 1
Loop
Cells(i, j).Select
Cells(i, j) = allPtFaultCnt
i = i + 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)
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
j = 10
'좌에서 우로 하나씩 처리
Do While j < falutCnt + 10
faultName = Cells(delPnt0 + 3, j)
If faultName = "" Then
Exit Do
End If
Sheets("mainData").Select
recordCnt = Cells(3, 4)
ptFaultCnt = 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 faultName = cmpFaultName Then
'테스트 결과
ptFaultCnt = ptFaultCnt + 1
allPtFaultCnt = allPtFaultCnt + 1
End If
k = k + 1
Loop
Sheets("진척관리").Select
Cells(i, j) = ptFaultCnt
j = j + 1
Loop
Cells(i, j).Select
Cells(i, j) = allPtFaultCnt
i = i + 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
'MACRO > EXCEL-MACRO' 카테고리의 다른 글
| 엑셀에서 단어 추출하는 매크로-2 (0) | 2017.01.19 |
|---|---|
| 테스트결과 좀 더 빠른 버전- (0) | 2016.10.28 |
| 엑셀에서 특정위치에서 문자 가져오기 (0) | 2016.10.21 |
| 자동저장시 시트위치 (0) | 2016.10.18 |
| 폴더가 있는지 확인 후 작업하는 방법 (0) | 2016.10.17 |
매크로 사용 시 자주 사용하는 것 중 하나가 문장의 특정 위치에 있는 문자를 가져오는 것입니다.
예) 업무일지_20160701-한국매크로연습주식회사.xls
위 파일에서 일자를 가져와서 처리일자의 기준으로 삼아야 함.
일단 연도를 기준으로 작업한다고 가정합시다
sub getInStr()
fileName2 = "업무일지_20160701-한국매크로연습주식회사.xls"
cells(1,1) = fileName2
'InStr은 텍스트에서 원하는 글자의 위치를 찾아주는 함수
chkPsn = InStr(1, fineName2, "2016",1)
'이걸 MID를 사용해서 자르고 날짜 형태로 만들어서 처리함
cells(2,1) = CDate(mid(fineName2, chkPsn, 4) & "-" & mid(fineName2, chkPsn+4, 2) & "-" & mid(fineName2, chkPsn+6, 2) & "-" & )
end sub
'MACRO > EXCEL-MACRO' 카테고리의 다른 글
| 테스트결과 좀 더 빠른 버전- (0) | 2016.10.28 |
|---|---|
| 테스트 결과표 정리 매크로 (0) | 2016.10.27 |
| 자동저장시 시트위치 (0) | 2016.10.18 |
| 폴더가 있는지 확인 후 작업하는 방법 (0) | 2016.10.17 |
| 엑셀 차트관련 소소한 팁 (0) | 2016.10.14 |
Sheets("workSheet").select
Sheets("workSheet").copy after:=Sheets(shtnum)
Sheets(shtnum+ 1).name = "copiedOne"
End sub
'MACRO > EXCEL-MACRO' 카테고리의 다른 글
| 테스트 결과표 정리 매크로 (0) | 2016.10.27 |
|---|---|
| 엑셀에서 특정위치에서 문자 가져오기 (0) | 2016.10.21 |
| 폴더가 있는지 확인 후 작업하는 방법 (0) | 2016.10.17 |
| 엑셀 차트관련 소소한 팁 (0) | 2016.10.14 |
| 엑셀 내에서 시트 함수 사용 하기 (0) | 2016.10.14 |
매크로를 실행 중에 폴더를 생성해야 할 때가 있습니다.
실행 중에 폴더가 있어야 하는데 없으면 오류가 납니다.
있는 데 또 만들려고 해도 오류가 발생하지요.
아래 체크 로직을 사용해서 폴더를 확인 후 생성합니다.
Sub createFolders()
Dim fs, f, s
NewFDName = Date & " 전체"
NewFDName = "D:\temp\" & NewFDName
'날짜가 필요한 곳이 있으므로 날짜를 추출
Cells(4, 8) = Left(Right(NewFDName, 13), 10)
'하루 전날로 셋팅
Cells(4, 28) = Cells(4, 8) - 1
'폴더가 있는지 확인
pathCheck = Dir(NewFDName, vbDirectory)
'없으면 만들고 있으면 무시
If pathCheck = "" Then
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.createFolder(NewFDName)
MsgBox ("폴더를 준비하였습니다 ")
Else
MsgBox ("폴더가 이미 존재합니다.")
End If
Cells(4, 7) = Right(NewFDName, 10)
End Sub
'MACRO > EXCEL-MACRO' 카테고리의 다른 글
| 엑셀에서 특정위치에서 문자 가져오기 (0) | 2016.10.21 |
|---|---|
| 자동저장시 시트위치 (0) | 2016.10.18 |
| 엑셀 차트관련 소소한 팁 (0) | 2016.10.14 |
| 엑셀 내에서 시트 함수 사용 하기 (0) | 2016.10.14 |
| 엑셀의 열이나 행 추가 삭제 시 (0) | 2016.10.14 |
차트 그릴 때
'크기 위치 고정
Activesheet.shapes(1).Placement = xlFreeFloating
'차트 제목입력
Activesheet.ChartObjects(1).Activate
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "월별실적"
'차트가 있는지 확인하고 삭제
chtCnt= Activesheet.ChartObjects.Count
if chtCnt > 0 then
Activesheet.ChartObjects(1).Delete
end if
'퍼센트 유형 변경
Range("t5:t50").select
selection.Style = "Percent"
'MACRO > EXCEL-MACRO' 카테고리의 다른 글
| 자동저장시 시트위치 (0) | 2016.10.18 |
|---|---|
| 폴더가 있는지 확인 후 작업하는 방법 (0) | 2016.10.17 |
| 엑셀 내에서 시트 함수 사용 하기 (0) | 2016.10.14 |
| 엑셀의 열이나 행 추가 삭제 시 (0) | 2016.10.14 |
| filedialogObject 사용시 초기 폴더위치 지정하기 (0) | 2016.10.13 |
엑셀 시트에서 제공하는 함수 중 편리하느게 다수 있는데 매크로에서 아래와 같이 간단히 사용한다.
예) countA
cells(3,4) = "count(d4:d5000)"
'내부의 위치에는 따옴표를 사용하지 않는다.
'MACRO > EXCEL-MACRO' 카테고리의 다른 글
| 폴더가 있는지 확인 후 작업하는 방법 (0) | 2016.10.17 |
|---|---|
| 엑셀 차트관련 소소한 팁 (0) | 2016.10.14 |
| 엑셀의 열이나 행 추가 삭제 시 (0) | 2016.10.14 |
| filedialogObject 사용시 초기 폴더위치 지정하기 (0) | 2016.10.13 |
| 엑셀 시트의 이미지 관련 소소한 팁 (0) | 2016.10.11 |
행 하나 추가할 때
rows(22).insert
열 하나 추가할 때
columns(22).insert
행 하나를 삭제할 때
rows(51).select
selection.delete shift :=xlUP
또는
rows(51).delete
열 하나를 삭제할 때
columns(30).delete
'MACRO > EXCEL-MACRO' 카테고리의 다른 글
| 엑셀 차트관련 소소한 팁 (0) | 2016.10.14 |
|---|---|
| 엑셀 내에서 시트 함수 사용 하기 (0) | 2016.10.14 |
| filedialogObject 사용시 초기 폴더위치 지정하기 (0) | 2016.10.13 |
| 엑셀 시트의 이미지 관련 소소한 팁 (0) | 2016.10.11 |
| macro 소소한 팁 (0) | 2016.10.11 |
Prev
Rss Feed