| |
[액셀VBA] SelectionChange프로시저에
대해서
오늘은
7월2일(토)에 진행되는
장미희님의
Excel 매크로 & VBA 과정 중
이벤트 프로시저에 대해서 알아보도록 하겠습니다.
(사용버전: 2003)
두 개 시트의 값이 서로 영향을 미쳐서 결과를 구하는 수식으로 순환참조가 발생하는 수식입니다.
이벤트 프로시저 중에서 SelectionChange프로시저를 이용하여 셀의 선택이 변경되면 자동으로 실행되는
프로시저를 작성하겠습니다.
우선 다음과 같이 두 개 시트의 내용을 살펴봅시다. 두 시트의 데이터는 더 방대하나 설명을 위해 간단하게
줄였습니다. 대출신청서 접수대장에는 대출을 신청한 거래처와
대출신청금액, 심사자 배정이 있습니다. 심사자별 신청서 보유 내역표에는 심사한 횟수와 심사금액,
신규거래처가 대출신청을 할 경우에 심사할 심사자 이름이 있습니다.
대출신청서 접수 대장이 있는 시트에서 테두리가 있는 A4셀에서 C9셀까지는 주요 거래처이고 이미 심사자가 배정되어
있는 표입니다. A10부터 거래처에서 대출신청이 오면 정해진 심사자가 배정됩니다. 이 부분은 Vlookup함수를
이용하면 되지요. 그런데 새로운 거래처가 대출신청을 해올 경우에 어떤 심사자를 배정하느냐가 바로 이벤트 프로시저로
작성하려는 내용입니다.
이때 심사자는 다른 시트에 작성된 심사자별 신청서 보유내역표에 의해 심사횟수가 가장 적은 사람이 배정되거나
심사횟수가 동일하다면 심사한 금액이 가장 적은 사람이 심사를 하도록 배정합니다. 거래처와 대출금액이 입력되고 심사자
배정셀을 선택하면 심사자 이름을 셀에 자동으로 입력하는 프로시저를 작성할 겁니다.
심사자별 신청서 보유 내역표의 E4,E5,E6셀에는 심사횟수가 가장 작은 사람의 보유금액을 찾는 수식이 입력되어
있습니다.
|
E4셀에 입력된 수식입니다. =IF(C4=MIN($C$4:$C$6),D4,"")
F4셀에 입력된 수식입니다. =IF(E4=MIN($E$4:$E$6),A4,"")
A9셀에 입력된 수식입니다. =MIN(F4:F6)
C9셀에 입력된 수식입니다. =VLOOKUP(A9,A4:B6,2,0)
수식에 대한 설명은 생략합니다 |
<1> 데이터가 입력된 파일에서 VBE화면의 프로젝트 창에서 대출신청서 접수대장이 작성된 시트의
코드창을 표시합니다. 개체는 Worksheet를, 이벤트 프로시저는 Selectionchange를
선택합니다. Selectionchange프로시저는 셀을 선택할 때 발생하는
프로시저입니다. A1셀에 셀포인터가 있는데 B1셀을 마우스나 키보드의 화살표로 이동하면 그 때 자동을
실행됩니다.
<2> 코드창에 다음과 같이 입력합니다. 코드가 너무 길어서 복잡하게 느껴지시겠지만 설명을 보시면
대략적으로 이해가 되실 겁니다.
[그림
확대해서 보기]
|
On Error GoTo ERRORCHECK
는 VBA코드에 예측가능한 오류가 발생하면 ErrorCheck라는 레이블이 있는 곳으로
이동하라는 의미입니다.
Dim wks As Worksheet
Set wks = Worksheets("심사자별보유내역표")
는 wks라는 변수를 심사자별조유내역표라는 시트의 대신으로 사용하겠다는 의미입니다.
If Mid(Target.Address, 2, 1) =
"C" And Target.Offset(0, -2).Value <> "" Then
는 셀포인터가 있는 셀의 주소가 C열이고 그 셀의 A열에 데이터가 입력되어 있으면, 즉
대출심사 거래처가 입력되어 있고 심사자를 배정하는 셀에 셀포인터가 있으면 이라는 의미입니다.
Target =
Application.WorksheetFunction.VLookup(Target.Offset(0,
-2), Range(Range("a4:c4"), Range("A4:C4").End(xlDown)),
3, 0) 는 Vlookup함수로 이미 지정된 거래처라면 심사자를 찾으라는
의미입니다.
If Target.Value = "" Then
With wks
.Range("c4").Value =
Application.WorksheetFunction.CountIf(Range(Range("c4"),
Range("c4").End(xlDown)), .Range("b4"))
는 Vlookup함수로 찾는 값이 없으면 보유내역표에서 심사횟수를 Countif함수로
계산합니다.
.Range("d4").Value =
Application.WorksheetFunction.SumIf(Range(Range("c4"),
Range("c4").End(xlDown)), .Range("b4"), Range(Range("b4"),
Range("b4").End(xlDown)))
Sumif함수로 심사금액의 합계를 구합니다.
Target = .Range("c9") 수식에 의해서 구해진 심사자의 이름을 Target으로
지칭되는 셀에 입력합니다. |
<3> 접수대장시트에서 A14셀에 대전상사와 B14에 100,000을 입력하고
C14셀을 선택하면 자동으로 해당 심사자인 이민경이 입력됩니다.
<4> 심사자별 보유내역표의 표는 즉시 계산이 되면서 다음 심사자가 김성만임을 표시합니다.
<5> 기존에 심사자가 배정되지 않은 새로운 거래처가 대출을 신청하는 경우를 알아봅니다. 분당실업,
5,000,000을 입력하고 C15셀을 선택하면 심사자별 보유내역표에서 대기하고 있던 김성만이 심사자에
지정되는 것을 볼 수 있습니다.
|
|
OfficeTutor 365