[기초방] VBA 100제 #42 [ 고급필터 ]

데이터를 새롭게 추가하고 또한 검색을 하는 이벤트 문제이다.

 

각 시트에 해당하는 이벤트들은 해당시트에서 한번만 사용가능하므로 여러가지 또는 여러 영역에서 이벤트를 발생하려면

Boolean을 통해서 KEY  역할을 하게 하여 적지적소에 이벤트를 발생시켜야 한다.

 

이 구문은 이렇다.

데이터 영역과 검색 영역이 있는데, 데이터 영역에는 모든 데이터가 입력되었을 때 이벤트가 발생해야 하고,

고급필터의 검색 영역은 일부 검색어 일치에 반응해야 함으로 이를 억지로 하나로 묶어버리면 의도한대로의 이벤트가 발생하지 않는다. 그렇기 때문에 Bln으로 영역을 양분해서 각각의 상황에 맞게 이벤트를 발생시키면 된다.

If Not Intersect(rngS, Target) Is Nothing Then bln = True     '= 검색 영역
    
    If bln = False Then 
    
       If Application.CountA(rngAll) < 5 Or Intersect(rngAll, Target) Is Nothing Then Exit Sub
       데이터 영역
    else
    
       고급필터 검색 영역	
   end if

특정영역에서 찾는값이 없으면 Error를 발생시키는데, 그것을 통해서 새로운 데이터인지를 파악할 수 있다. 여기서 주의할점은 Error의 대소문자 구분이다.

if "Error" = TypeName(Application.Match([d9], rngd.Columns(2), 0)) then

최근기록 데이터 영역의 위치를 R에 넣지만 / 전체 셀 기준에는 행의 위치가 위에서 11줄 아래에 있기 때문에 +11을 더해준다.

R = Application.Match([d9], rngd.Columns(2), 0)                  
           
If Cells(R + 11, "d") = [d9] And Cells(R + 11, "e") = [e9] Then

다음은 고급필터 함수 구현이다.

[i13].Resize(rngd.Rows.Count, 5).Delete shift:=xlUp                   
rngd.AdvancedFilter xlFilterCopy, [i8:m9], [i12:m12], False       
[i13].Resize(rngd.Rows.Count, 5).Interior.Color = xlNone

기존 데이터를 지우고 고급필터를 적용하고 / 혹여나 딸려나올 음영이 칠해진 부분의 채우기를 없애는 구문이다.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngAll As Range: Set rngAll = [c9:g9]
    Dim rngd As Range: Set rngd = [c11].CurrentRegion.Offset(1)
    Dim rngX As Range: Set rngX = [c13]
    Dim rngS As Range: Set rngS = [i9:m9]
    Dim R&, bln As Boolean
 
    If Not Intersect(rngS, Target) Is Nothing Then bln = True                   '= 검색 영역에 이벤트가 걸리면 Bln을 True로 변경해라
    
    If bln = False Then                                                         '= 그렇지 않다면 / 데이터 영역이라면
    
       If Application.CountA(rngAll) < 5 Or Intersect(rngAll, Target) Is Nothing Then Exit Sub
                                                                                '= 데이터 영역에 모든 데이터가 입력되지 않거나 데이터 영역이 아니면 종료
    
       If "Error" = TypeName(Application.Match([d9], rngd.Columns(2), 0)) Or _
          "Error" = TypeName(Application.Match([e9], rngd.Columns(3), 0)) Then  '= 최근기록에 이름 또는 부서가 일치하는 게 없다면
           
           rngX.Resize(1, 5).Insert shift:=xlDown                               '= 헤드 바로 아래에 빈셀을 삽입해라
           
           Set rngX = rngX.Offset(-1, 0)
           
           rngAll.Copy rngX: Haja_Format rngX, rngd                             '= 데이터 영역의 데이터를 빈셀에 복사해라 / 노란색 음영을 칠해라
           
       Else: R = Application.Match([d9], rngd.Columns(2), 0)                    '= 그렇지 않으면 / 기존 데이터가 있으면 R 값에 rngD상에 데이터의 위치를 넣어라.
           
           If Cells(R + 11, "d") = [d9] And Cells(R + 11, "e") = [e9] Then      '= 이름과 부서가 최근기록의 이름과 부서와 일치한다면
           
               rngAll.Copy Cells(R + 11, "c"): Haja_Format Cells(R + 11, "c"), rngd   '= 데이터의 내용을 최근기록의 기존 데이터에 업데이트 해라 /  노란색 음영을 칠해라
               
           End If
     
       End If
       
    Else                                                                        '= 검색 데이터 영역이라면
   
        [i13].Resize(rngd.Rows.Count, 5).Delete shift:=xlUp                     '= 기존 데이터를 지우고
        rngd.AdvancedFilter xlFilterCopy, [i8:m9], [i12:m12], False             '= 고급 필터링 된 값을 출력하고
        [i13].Resize(rngd.Rows.Count, 5).Interior.Color = xlNone                '= 혹여나 노란색 음영이 추출되면 그 노란색 음영을 없애라
    
    End If
    
End Sub

Function Haja_Format(rngX As Range, rngd As Range)

    rngd.Offset(1).Interior.ColorIndex = xlNone
    rngX.Resize(1, 5).Interior.ColorIndex = 6
    [c9:g9].ClearContents
    
End Function

늘 말하지만 고급필터는 노력한거에 비해 뽀대가 좌르륵 흐르는 코드이니, 개인적으로 더 공부하길 바란다.

기초방42.xlsm

0.02MB