'분류 전체보기'에 해당되는 글 184건
- 2016.10.28 Vote with one's feet
- 2016.10.28 테스트결과 좀 더 빠른 버전-
- 2016.10.27 테스트 결과표 정리 매크로
- 2016.10.26 커피 탬퍼 받침대
- 2016.10.25 알리에서 주문한 와아파이+블루투스 USB adapter 가 도착했습니다.
- 2016.10.21 엑셀에서 특정위치에서 문자 가져오기
- 2016.10.18 자동저장시 시트위치
- 2016.10.17 프로젝트 진척 관리 팁
- 2016.10.17 폴더가 있는지 확인 후 작업하는 방법
- 2016.10.14 엑셀 차트관련 소소한 팁
떠나버리는 것으로 마음에 들지 않음을 표현하다.
연극이 마음에 들지 않았던 관객들은 제 2막을 하는 동안 떠나버렸다.
vote with one's feet
'잡동사니' 카테고리의 다른 글
| 자전거용 헬멧 (0) | 2016.10.29 |
|---|---|
| He who pays the piper calls the tune (0) | 2016.10.28 |
| 커피 탬퍼 받침대 (0) | 2016.10.26 |
| 알리에서 주문한 와아파이+블루투스 USB adapter 가 도착했습니다. (0) | 2016.10.25 |
| Shakira - Waka Waka (This Time for Africa) (The Official 2010 FIFA World Cup™ Song) (0) | 2016.05.30 |
배열을 안써서 좀 더 복잡해졌네요
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 |
커피 가루를 다지고 나면 아무래도 탬퍼가 지저분해집니다.
청소를 해도 커피 머신 주위가 지저분해지는 걸 막을 수 없고 해서 탬퍼 거치대를 하나 샀습니다.
알리에서 저렴하게 ....
배송도 빠르네요. 10월 15일 주문했는데 25일 도착했으니 열흘만이네요.
보통 한달은 걸리고 짧아도 2주일은 걸리던데 말입니다.
Coffee Tamper Holder Stainless Steel Stand Rack Shelf Coffee Brewing Tools Coffee Machine Accessories Caffe Making Gadgets
$11.00에 무료배송입니다.
상태는 뭐 그냥 그냥..
이렇게 분리가 되고..
탬퍼가 지저분하게 돌아다나지 않아서 좋네요.
'잡동사니' 카테고리의 다른 글
| He who pays the piper calls the tune (0) | 2016.10.28 |
|---|---|
| Vote with one's feet (0) | 2016.10.28 |
| 알리에서 주문한 와아파이+블루투스 USB adapter 가 도착했습니다. (0) | 2016.10.25 |
| Shakira - Waka Waka (This Time for Africa) (The Official 2010 FIFA World Cup™ Song) (0) | 2016.05.30 |
| 이마트 에브리데이.김치제육 덮밥소스 실사 (0) | 2015.07.19 |
새로 컴퓨터를 샀는데 저가 제품이라 그런지 무선 네트워크와 블루투스를 지원하지 않네요.
그동안 USB형 네트워크 카드를 사용하고 있었는데 블루투스를 사용해야 할 상황이 됐습니다.
동글을 사야하는데 기왕 살거면 네트워크와 블루투스 둘 다 지원하는 걸로 사야겠다고 생각하고 알아봤습니다.
저렴한 건 2만원 정도고 비싼 건 9만원도 넘습니다.
그래서 알리를 검색해서 샀는데 똑같은게 한국에서는 2배 가격으로 팔리네요.
2배는 양심적인거고 5배로 팔리는 곳도 있군요.
유통마진인지... 어차피 해외 구매대행이구만...
Order ID: 300000000 l Order time & date: 07:09 Oct. 09 2016 | Store name: Shenzhen CarNival Trading Co., Ltd. | Order amount: $ 9.03 | ||
 | Open Dispute | Awaiting delivery Your order will be closed in: 9 days 15 hours 6 minutes | ||
배송비가 없어서 좋지만 16일이나 걸린다는게 ....
검소한 포장...
금방 인식되네요.
네트워크도 저절로 잡히고...
'잡동사니' 카테고리의 다른 글
| Vote with one's feet (0) | 2016.10.28 |
|---|---|
| 커피 탬퍼 받침대 (0) | 2016.10.26 |
| Shakira - Waka Waka (This Time for Africa) (The Official 2010 FIFA World Cup™ Song) (0) | 2016.05.30 |
| 이마트 에브리데이.김치제육 덮밥소스 실사 (0) | 2015.07.19 |
| 드롱기 청소하기 (0) | 2015.04.11 |
매크로 사용 시 자주 사용하는 것 중 하나가 문장의 특정 위치에 있는 문자를 가져오는 것입니다.
예) 업무일지_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 |
프로그램 개발 계획 확인
엑셀에서 좌측 세로열에 프로그램리스트를 적고 오른쪽 가로열에 일자를 입력한다.
각 일자별로 프로젝트 개발 대상의 수를 확인한다.
특정 날짜에 개발이 몰려 있는지의 여부를 확인할 수 있다.
인력별 등 다양하게 활용할 수 있다.
'기술적문제' 카테고리의 다른 글
| Fedex 사칭 바이러스 조심 (0) | 2014.12.24 |
|---|---|
| 데이터아키텍처 전문가 실기문제 출제방향 및 고려사항 (0) | 2013.11.20 |
| Big Data] 정보화 진흥원 - 빅데이터_분석활용_가이드v1.0.pdf (0) | 2013.11.15 |
| Big Data] 정보화 진흥원 자료-새로운_미래를_여는_빅데이터_시대.pdf (0) | 2013.11.15 |
| DAP 합격했습니다 (0) | 2013.06.25 |
매크로를 실행 중에 폴더를 생성해야 할 때가 있습니다.
실행 중에 폴더가 있어야 하는데 없으면 오류가 납니다.
있는 데 또 만들려고 해도 오류가 발생하지요.
아래 체크 로직을 사용해서 폴더를 확인 후 생성합니다.
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 |
Prev
Rss Feed