|
* 답변하시는 분들께 도움이 되도록 자신의 환경을 아래 항목 옆에 기재해 주세요.
- 엑셀 버전(95,97,2000,xp,2003,2007): 2016
* 아래줄에 질문을 작성하세요 >>
해당 VBA 코드를 실행시
W3 = Q3 / Z3
부분에서 런타임오류 6 오버플로가 발생합니다.
변수 정의도 다 변경해봤는데 해결이 안되네요 ㅠㅠ
목적은 일정 코드에 따라 해당 행에 있는 몇개의 셀을 노란색으로 변경하고
하나의 열 마다 노란색으로 변경된 셀의 값을 가져와 평균을 계산하는데 있습니다.
읽어주셔서 감사합니다!
Option Explicit
Sub 색입히기()
Dim RowA As Integer
Dim X As Double
Dim i As Integer
RowA = Range("e3").CurrentRegion.Rows.Count '행 수 세기
Range("a1:ae2000").Interior.ColorIndex = 0 '색 없애기
'해당되는 코드를 찾아 Color(6) = "노랑" 을 입힙니다.
For i = 1 To RowA - 2
X = Cells(i + 2, 5)
If X = 306388 Or X = 306482 Then
Cells(i + 2, 10).Interior.ColorIndex = 6
Cells(i + 2, 12).Interior.ColorIndex = 6
Cells(i + 2, 14).Interior.ColorIndex = 6
ElseIf X = 306389 Or X = 306483 Then
Cells(i + 2, 12).Interior.ColorIndex = 6
Cells(i + 2, 14).Interior.ColorIndex = 6
Cells(i + 2, 16).Interior.ColorIndex = 6
Cells(i + 2, 18).Interior.ColorIndex = 6
Cells(i + 2, 20).Interior.ColorIndex = 6
Cells(i + 2, 22).Interior.ColorIndex = 6
ElseIf X = 306390 Or X = 306484 Then
Cells(i + 2, 10).Interior.ColorIndex = 6
Cells(i + 2, 12).Interior.ColorIndex = 6
Cells(i + 2, 14).Interior.ColorIndex = 6
Cells(i + 2, 16).Interior.ColorIndex = 6
Cells(i + 2, 18).Interior.ColorIndex = 6
Cells(i + 2, 20).Interior.ColorIndex = 6
Cells(i + 2, 22).Interior.ColorIndex = 6
ElseIf X = 306391 Or X = 306485 Then
Cells(i + 2, 16).Interior.ColorIndex = 6
Cells(i + 2, 18).Interior.ColorIndex = 6
Cells(i + 2, 20).Interior.ColorIndex = 6
Cells(i + 2, 22).Interior.ColorIndex = 6
Cells(i + 2, 24).Interior.ColorIndex = 6
Cells(i + 2, 26).Interior.ColorIndex = 6
Cells(i + 2, 28).Interior.ColorIndex = 6
ElseIf X = 306392 Or X = 306486 Then
Cells(i + 2, 16).Interior.ColorIndex = 6
Cells(i + 2, 18).Interior.ColorIndex = 6
Cells(i + 2, 20).Interior.ColorIndex = 6
Cells(i + 2, 22).Interior.ColorIndex = 6
Cells(i + 2, 24).Interior.ColorIndex = 6
Cells(i + 2, 26).Interior.ColorIndex = 6
Cells(i + 2, 28).Interior.ColorIndex = 6
ElseIf X = 306393 Or X = 306487 Then
Cells(i + 2, 10).Interior.ColorIndex = 6
Cells(i + 2, 12).Interior.ColorIndex = 6
Cells(i + 2, 14).Interior.ColorIndex = 6
Cells(i + 2, 16).Interior.ColorIndex = 6
Cells(i + 2, 18).Interior.ColorIndex = 6
Cells(i + 2, 20).Interior.ColorIndex = 6
Cells(i + 2, 22).Interior.ColorIndex = 6
ElseIf X = 306394 Or X = 306488 Then
Cells(i + 2, 10).Interior.ColorIndex = 6
Cells(i + 2, 12).Interior.ColorIndex = 6
Cells(i + 2, 14).Interior.ColorIndex = 6
Cells(i + 2, 16).Interior.ColorIndex = 6
Cells(i + 2, 18).Interior.ColorIndex = 6
Cells(i + 2, 20).Interior.ColorIndex = 6
Cells(i + 2, 22).Interior.ColorIndex = 6
ElseIf X = 306395 Or X = 306489 Then
Cells(i + 2, 10).Interior.ColorIndex = 6
Cells(i + 2, 12).Interior.ColorIndex = 6
Cells(i + 2, 14).Interior.ColorIndex = 6
Cells(i + 2, 16).Interior.ColorIndex = 6
Cells(i + 2, 18).Interior.ColorIndex = 6
Cells(i + 2, 20).Interior.ColorIndex = 6
Cells(i + 2, 22).Interior.ColorIndex = 6
ElseIf X = 306396 Or X = 306490 Then
Cells(i + 2, 10).Interior.ColorIndex = 6
Cells(i + 2, 12).Interior.ColorIndex = 6
Cells(i + 2, 14).Interior.ColorIndex = 6
Cells(i + 2, 16).Interior.ColorIndex = 6
Cells(i + 2, 18).Interior.ColorIndex = 6
Cells(i + 2, 20).Interior.ColorIndex = 6
Cells(i + 2, 22).Interior.ColorIndex = 6
ElseIf X = 306397 Or X = 306491 Then
Cells(i + 2, 10).Interior.ColorIndex = 6
Cells(i + 2, 12).Interior.ColorIndex = 6
Cells(i + 2, 14).Interior.ColorIndex = 6
Cells(i + 2, 16).Interior.ColorIndex = 6
Cells(i + 2, 18).Interior.ColorIndex = 6
Cells(i + 2, 20).Interior.ColorIndex = 6
Cells(i + 2, 22).Interior.ColorIndex = 6
ElseIf X = 306398 Or X = 306492 Then
Cells(i + 2, 10).Interior.ColorIndex = 6
Cells(i + 2, 12).Interior.ColorIndex = 6
Cells(i + 2, 14).Interior.ColorIndex = 6
Cells(i + 2, 16).Interior.ColorIndex = 6
Cells(i + 2, 18).Interior.ColorIndex = 6
Cells(i + 2, 20).Interior.ColorIndex = 6
Cells(i + 2, 22).Interior.ColorIndex = 6
ElseIf X = 306399 Or X = 306493 Then
Cells(i + 2, 10).Interior.ColorIndex = 6
Cells(i + 2, 12).Interior.ColorIndex = 6
Cells(i + 2, 14).Interior.ColorIndex = 6
Cells(i + 2, 16).Interior.ColorIndex = 6
Cells(i + 2, 18).Interior.ColorIndex = 6
Cells(i + 2, 20).Interior.ColorIndex = 6
Cells(i + 2, 22).Interior.ColorIndex = 6
ElseIf X = 306400 Or X = 306494 Then
Cells(i + 2, 10).Interior.ColorIndex = 6
Cells(i + 2, 12).Interior.ColorIndex = 6
Cells(i + 2, 14).Interior.ColorIndex = 6
Cells(i + 2, 16).Interior.ColorIndex = 6
Cells(i + 2, 18).Interior.ColorIndex = 6
Cells(i + 2, 20).Interior.ColorIndex = 6
Cells(i + 2, 22).Interior.ColorIndex = 6
ElseIf X = 306401 Or X = 306495 Then
Cells(i + 2, 10).Interior.ColorIndex = 6
Cells(i + 2, 12).Interior.ColorIndex = 6
Cells(i + 2, 14).Interior.ColorIndex = 6
Cells(i + 2, 16).Interior.ColorIndex = 6
Cells(i + 2, 18).Interior.ColorIndex = 6
Cells(i + 2, 20).Interior.ColorIndex = 6
Cells(i + 2, 22).Interior.ColorIndex = 6
ElseIf X = 306402 Or X = 306496 Then
Cells(i + 2, 10).Interior.ColorIndex = 6
Cells(i + 2, 12).Interior.ColorIndex = 6
Cells(i + 2, 14).Interior.ColorIndex = 6
Cells(i + 2, 16).Interior.ColorIndex = 6
Cells(i + 2, 18).Interior.ColorIndex = 6
Cells(i + 2, 20).Interior.ColorIndex = 6
Cells(i + 2, 22).Interior.ColorIndex = 6
ElseIf X = 306403 Or X = 306497 Then
Cells(i + 2, 10).Interior.ColorIndex = 6
Cells(i + 2, 12).Interior.ColorIndex = 6
Cells(i + 2, 14).Interior.ColorIndex = 6
Cells(i + 2, 16).Interior.ColorIndex = 6
Cells(i + 2, 18).Interior.ColorIndex = 6
Cells(i + 2, 20).Interior.ColorIndex = 6
Cells(i + 2, 22).Interior.ColorIndex = 6
ElseIf X = 306404 Or X = 306498 Then
Cells(i + 2, 10).Interior.ColorIndex = 6
Cells(i + 2, 12).Interior.ColorIndex = 6
Cells(i + 2, 14).Interior.ColorIndex = 6
Cells(i + 2, 16).Interior.ColorIndex = 6
Cells(i + 2, 18).Interior.ColorIndex = 6
Cells(i + 2, 20).Interior.ColorIndex = 6
Cells(i + 2, 22).Interior.ColorIndex = 6
ElseIf X = 306405 Or X = 306499 Then
Cells(i + 2, 10).Interior.ColorIndex = 6
Cells(i + 2, 12).Interior.ColorIndex = 6
Cells(i + 2, 14).Interior.ColorIndex = 6
Cells(i + 2, 16).Interior.ColorIndex = 6
Cells(i + 2, 18).Interior.ColorIndex = 6
Cells(i + 2, 20).Interior.ColorIndex = 6
Cells(i + 2, 22).Interior.ColorIndex = 6
ElseIf X = 306406 Or X = 306500 Then
Cells(i + 2, 10).Interior.ColorIndex = 6
Cells(i + 2, 12).Interior.ColorIndex = 6
Cells(i + 2, 14).Interior.ColorIndex = 6
Cells(i + 2, 16).Interior.ColorIndex = 6
Cells(i + 2, 18).Interior.ColorIndex = 6
Cells(i + 2, 20).Interior.ColorIndex = 6
Cells(i + 2, 22).Interior.ColorIndex = 6
ElseIf X = 306407 Or X = 306501 Then
Cells(i + 2, 10).Interior.ColorIndex = 6
Cells(i + 2, 12).Interior.ColorIndex = 6
Cells(i + 2, 14).Interior.ColorIndex = 6
Cells(i + 2, 16).Interior.ColorIndex = 6
Cells(i + 2, 18).Interior.ColorIndex = 6
Cells(i + 2, 20).Interior.ColorIndex = 6
Cells(i + 2, 22).Interior.ColorIndex = 6
ElseIf X = 306408 Or X = 306502 Then
Cells(i + 2, 10).Interior.ColorIndex = 6
Cells(i + 2, 12).Interior.ColorIndex = 6
Cells(i + 2, 14).Interior.ColorIndex = 6
Cells(i + 2, 16).Interior.ColorIndex = 6
Cells(i + 2, 18).Interior.ColorIndex = 6
Cells(i + 2, 20).Interior.ColorIndex = 6
Cells(i + 2, 22).Interior.ColorIndex = 6
End If
Next i
Dim Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9, Q10 As Double
Dim W1, W2, W3, W4, W5, W6, W7, W8, W9, W10 As Double
Dim Y As Double
Dim Z1, Z2, Z3, Z4, Z5, Z6, Z7, Z8, Z9, Z10 As Double
'시트2에 각 과목의 평균값을 계산합니다.
Worksheets("sheet2").Range("a2:j2") = 0 '각 과목 평균 0으로 만들기
For Y = 1 To RowA
If Cells(Y, 10).Interior.ColorIndex = 6 Then
Q1 = Q1 + Cells(Y, 10)
Z1 = Z1 + 1
ElseIf Cells(Y, 12).Interior.ColorIndex = 6 Then
Q2 = Q2 + Cells(Y, 12)
Z2 = Z2 + 1
ElseIf Cells(Y, 14).Interior.ColorIndex = 6 Then
Q3 = Q3 + Cells(Y, 14)
Z3 = Z3 + 1
ElseIf Cells(Y, 16).Interior.ColorIndex = 6 Then
Q4 = Q4 + Cells(Y, 16)
Z4 = Z4 + 1
ElseIf Cells(Y, 18).Interior.ColorIndex = 6 Then
Q5 = Q5 + Cells(Y, 18)
Z5 = Z5 + 1
ElseIf Cells(Y, 20).Interior.ColorIndex = 6 Then
Q6 = Q6 + Cells(Y, 20)
Z6 = Z6 + 1
ElseIf Cells(Y, 22).Interior.ColorIndex = 6 Then
Q7 = Q7 + Cells(Y, 22)
Z7 = Z7 + 1
ElseIf Cells(Y, 24).Interior.ColorIndex = 6 Then
Q8 = Q8 + Cells(Y, 24)
Z8 = Z8 + 1
ElseIf Cells(Y, 26).Interior.ColorIndex = 6 Then
Q9 = Q9 + Cells(Y, 26)
Z9 = Z9 + 1
ElseIf Cells(Y, 28).Interior.ColorIndex = 6 Then
Q10 = Q10 + Cells(Y, 28)
Z10 = Z10 + 1
End If
Next Y
W1 = Q1 / Z1
W2 = Q2 / Z2
W3 = Q3 / Z3
W4 = Q4 / Z4
W5 = Q5 / Z5
W6 = Q6 / Z6
W7 = Q7 / Z7
W8 = Q8 / Z8
W9 = Q9 / Z9
W10 = Q10 / Z10
Worksheets("sheet2").Cells(2, 1) = W1
Worksheets("sheet2").Cells(2, 2) = W2
Worksheets("sheet2").Cells(2, 3) = W3
Worksheets("sheet2").Cells(2, 4) = W4
Worksheets("sheet2").Cells(2, 5) = W5
Worksheets("sheet2").Cells(2, 6) = W6
Worksheets("sheet2").Cells(2, 7) = W7
Worksheets("sheet2").Cells(2, 8) = W8
Worksheets("sheet2").Cells(2, 9) = W9
Worksheets("sheet2").Cells(2, 10) = W10
End Sub
|
|