엑셀 기타작업-2 폼 완성 문제 중

② <원아조회> 폼의 '원아명(txt원아명)'에 조회할 원아의 이름을 입력하고 <조회(cmd조회)> 버튼을 클릭하면

워크시트의 [표1]에 해당 데이터를 찾아 리스트 상자(lst원아정보)에 표시하고 해당 원아의 정보가 없는 경우에는 "조건에 일치하는 자료가 없습니다."라는 메시지 박스를 표시하는 프로시저를 작성하시오.

▶For Each ~ Next문을 이용하시오.

2012년 문제를 어렵지 않게 풀고 2011년 3회를 푸는데 

갑자기 이런 문제가 나와버렸다.

⑴<원아조회> 폼의 '원아명(txt원아명)'에 조회할 원아의 이름을 입력하고 <조회(cmd조회)> 버튼을 클릭하면

   워크시트의 [표1]에 해당 데이터를 찾아 리스트 상자(lst원아정보)에 표시

⑵ 해당 원아의 정보가 없는 경우에는 "조건에 일치하는 자료가 없습니다."라는 메시지 박스를 표시

이 문제는 요따우로 크게 두 가지를 요구하고 있다.

일단 표에 나와 있는 원아명 중 하나를 txt원아명에 입력하고 조회 컨트롤을 누르면 

해당 원아의 정보가 폼에 예쁘게 표시되도록 하는 것!

[표1] 보탬유치원

원아명 생년월일 성별 전화번호 주소 반명 입학년도

장명수 2010-05-09 남 333-1254 서교동 햇살반 2010

김승철 2006-04-25 남 332-4521 동교동 방울반 2007

정민호 2009-11-08 여 333-6592 서교동 병아리반 2009

박근호 2008-05-07 남 332-9562 망원동 코끼리반 2008

손수호 2007-09-12 여 333-1122 서교동 방울반 2007

김병수 2010-07-03 여 332-1595 성산동 햇살반 2011

구웅희 2008-05-06 남 332-3232 합정동 코끼리반 2008

양경민 2009-04-03 여 336-2562 동교동 병아리반 2009

이미연 2008-01-06 남 352-5672 서교동 코끼리반 2008

변기영 2009-05-03 남 336-5781 서교동 병아리반 2009

노진일 2006-12-08 남 334-6851 망원동 방울반 2007

채우리 2009-12-25 여 332-8546 합정동 햇살반 2010

성은희 2007-03-04 여 333-9548 합정동 방울반 2007

이만열 2007-09-30 남 332-9564 망원동 방울반 2007

김현진 2007-01-07 여 333-9568 서교동 햇살반 2007

정상욱 2008-08-08 여 334-5864 동교동 코끼리반 2008

이 것이 [표1]인데 사진 올리기 귀찮아서 그냥 워크시트에 있는 것을 복사했다.

이 표를 보고 있노라면 출제자의 정신상태가 심각하게 의심스러워진다. 

여자애 이름을 상욱, 병수, 민호 따위로 짓다니

아무튼 일반적인 VBA문제처럼 폼의 조회 컨트롤을 디자인보기로 더블 클릭하여 들어가면

Private Sub cmd조회_Click()

    

End Sub

라고 뜬다.

이제 저 가운데를 채워줘야 하는데

이 문제는 For Each ~ Next문을 사용하라고 했다.

For Each ~ Next문은 일반적으로 이렇게 구성된다.

For Each 개체변수 In 컬렉션 개체

실행문

Next 개체변수

이 문법의 의미는 

"컬렉션에 포함된 개체의 수만큼 실행문에 있는 명령 코드를 반복 실행한다." 이다.

정리하면

Private Sub cmd조회_Click()  <- 조회 버튼을 클릭하면

       For Each 개체변수 In 컬렉션 개체 <- 컬렉션 개체의 수만큼 실행문의 명령코드를 반복 실행

실행문

       Next

End Sub

여기서 원아명으로 조회하라고 했으니 

개체변수는 원아명이 되고 

컬렉션 개체는 그 원아명들이 들어있는 [표1]의 B4:B19가 된다.

정리하면~

일단 개체변수인 원아명을 aa라고 하자! 

그리고~

Private Sub cmd조회_Click()

        For Each aa In Range(" B4:B19") ->  B4:B19에 있는 원아명의 수만큼 반복 : 19-4+1=16이므로 16번 반복이다!

        Next

End Sub

이제 뼈대는 대충 갖췄으니 살을 붙여야 하는데~

실행문을 반복 중에 원아조회에 쓰인 원아명을 컬렉션 개체에서 찾은 경우를 생각해 보자.

Private Sub cmd조회_Click()

    For Each aa In Range("B4:B19")

        If aa = txt원아명 Then <- aa가 txt원아명에 쓰인 원아명과 같다면!

        End If

    Next

End Sub

여기까지 잘 따라오고 있나요?

그런 걸로 여기고 계속 갑니다~

원아명을 찾았으면 표시해야죠!!

Private Sub cmd조회_Click()

    For Each aa In Range("B4:B19")

        If aa = txt원아명 Then

        lst원아정보.RowSource = "표에 있는 해당 원아정보"  <- lst원아정보에 "표에 있는 해당 원아정보"를 지정해 줌.

        End If

    Next

End Sub

"표에 있는 해당 원아정보"  요따우로 쓸 순 없으므로

"표에 있는 해당 원아정보"를 셀 범위로 지정해야 하는 문제가 발생한다!

셀 범위는 B행번호:H행번호가 되는데

이 행번호는 원아명에 따라 변하는 수이기 때문에

우리는 여기서 '조회행'이라는 것을 지정해줘야 한다.

원아명은 4행에서 19행까지 있고

실행문을 실행할 때마다 1씩 증가해야 하므로

초기값을 3으로 주면 조회행(3) = 조회행(3) + 1 = 4 

즉 4행부터 조회시작! OK!

Private Sub cmd조회_Click()

    조회행 = 3 

    For Each aa In Range("B4:B19")

        조회행 = 조회행 + 1

        If aa = txt원아명 Then

        lst원아정보.RowSource = "B" & 조회행 & ":H" & 조회행 <- & 연산자를 활용하자! 시험에 자주 나온다!

  Exit For         

        End If

    Next

End Sub

이거스로 

⑴<원아조회> 폼의 '원아명(txt원아명)'에 조회할 원아의 이름을 입력하고 <조회(cmd조회)> 버튼을 클릭하면

   워크시트의 [표1]에 해당 데이터를 찾아 리스트 상자(lst원아정보)에 표시

요 부분은 해결!

자 이제 

⑵ 해당 원아의 정보가 없는 경우에는 "조건에 일치하는 자료가 없습니다."라는 메시지 박스를 표시

이제 이 부분을 풀어보자!

우선 조건에 일치했을 경우를 따져보자.

찾는 값이 있는 경우를 스위치 = 1

찾는 값이 없는 경우를 스위치 = 0 

이라고 하고

Private Sub cmd조회_Click()

    스위치 = 0 

    조회행 = 3 

    For Each aa In Range("B4:B19")

        조회행 = 조회행 + 1

        If aa = txt원아명 Then

        lst원아정보.RowSource = "B" & 조회행 & ":H" & 조회행

    스위치 = 1 <- 조건에 일치하는 값을 찾았다.

    Exit For <- For문을 나와랏

        End If

    Next

End Sub

그리고 반복 실행 후 찾지 못했을 경우에는 스위치 = 0에 해당하여 

Private Sub cmd조회_Click()

    스위치 = 0

    조회행 = 3

    For Each aa In Range("B4:B19")

        조회행 = 조회행 + 1

        If aa = txt원아명 Then

        lst원아정보.RowSource = "B" & 조회행 & ":H" & 조회행

    스위치 = 1

    Exit For

        End If

    Next

    If 스위치 = 0 Then <- 반복 실행했지만 조건에 일치하는 값을 찾지 못했다.

        MsgBox "조건에 일치하는 자료가 없습니다."

    End If

End Sub

짜잔 완성!!

휴~ 5점짜리 문제 하나 때문에 ~

그래도 알고 넘어가는 것이 좋다!!

으하하!

원본 출처 

http://sengyeo.tistory.com/entry/%EC%BB%B4%ED%93%A8%ED%84%B0-%ED%99%9C%EC%9A%A9%EB%8A%A5%EB%A0%A5-1%EA%B8%89-%EC%8B%A4%EA%B8%B0-2011%EB%85%84-3%ED%9A%8C-%EB%AC%B8%EC%A0%9C-%EC%A4%91-VBA

삭제 될때를 대비해서 직접 퍼왔습니다.