Private Sub Worksheet_Change(ByVal Target As Range)

역시 엑셀마스터 Q&A에 올라온 글.

주민번호를 치면 자료시트에 있는 내용을 안내문 시트로 불러오는 내용이다.

Private Sub Worksheet_Change(ByVal Target As Range) 구문을 이용해서 주민번호를 입력하면 곧바로 자료시트의 내용을 불러오게 하였다.

주민번호 입력시 14글자가 아니거나 일치하는 주민번호가 없으면 메시지를 나타나게 하였다.

Sub gAein()

    Dim i As Integer

    Dim y As Integer

    Dim wsLetter As Worksheet

    Dim wsData As Worksheet

    Dim rngJumin As Range

    Dim c As Range

    Dim rngAddress As Range

    Dim rngTo As Range

    Dim rngZipcode As Range

    Dim rngName As Range

    Dim rngBank As Range

    Dim rngCount As Range

    Dim rngGold As Range

    Dim rngNum As Range

    

    Application.ScreenUpdating = False

        

    '항목별 변수 지정

    Set wsLetter = Sheets(1)                    '안내문 시트

    Set wsData = Sheets(2)                      '데이터 시트

    Set rngAddress = wsLetter.Range("D2")       '주소

    Set rngTo = wsLetter.Range("F3")            '귀하

    Set rngZipcode = wsLetter.Range("F4")       '우편번호

    Set rngName = wsLetter.Range("C8")          '납부자

    Set rngBank = wsLetter.Range("C9")          '가상계좌

    Set rngCount = wsLetter.Range("E9")         '총 건수

    Set rngGold = wsLetter.Range("F9")          '총 금액

    Set rngJumin = wsLetter.Range("E8")         '주민번호

    Set rngNum = wsLetter.Range("A12")          '연번

    Set c = wsData.Columns("C").SpecialCells(2).Find(what:=rngJumin, lookat:=xlWhole)   '주민번호와 일치하는 셀

    

    '주민번호가 일치할 때

    If Not c Is Nothing Then

        '항목 입력

        With c

            rngAddress = .Offset(, 2)

            rngTo = .Offset(, -1) & " 귀하"

            rngZipcode = .Offset(, 3)

            rngName = .Offset(, -1)

            rngBank = .Offset(0, 1)

            rngCount = ""

            rngGold = ""

               

        '주민번호가 일치하는 항목의 Row값

        i = 0

        Do While .Offset(i, 0) = .Offset(i + 1, 0)

            i = i + 1

        Loop

        i = i + 1

        

        '데이터 시트의 주민번호와 일치하는 표 복사해서 안내문 시트에 붙이기

        .Offset(, 4).Resize(i, 5).Copy

        rngNum.Offset(, 1).Select

        ActiveSheet.Paste

        End With

        

        '연번 입력

        For y = 1 To i

            rngNum.Offset(y - 1, 0) = y

        Next y

        

        '모든 항목 가운데 정렬하고 테두리 그리기

        With rngNum.Resize(i, 6)

            .HorizontalAlignment = xlCenter

            .Borders.Weight = xlThin

        End With

        

        '총 건수 및 총 금액 입력

        rngCount = i & "건"

        rngGold = Application.Sum(rngCount.Offset(3, 0).Resize(i))

        

    '주민번호가 없을 때

    Else

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

        GoTo j:

    End If

        

j:

    Application.ScreenUpdating = True

    rngJumin.Select

End Sub

Sub annaeDelete()

    Dim i As Integer

    Dim rngDelete As Range

    Dim rngAddress As Range

    Dim rngTo As Range

    Dim rngZipcode As Range

    Dim rngName As Range

    Dim rngBank As Range

    Dim rngCount As Range

    Dim rngGold As Range

        

    '항목별 변수 지정

    Set rngDelete = Range("A12")

    Set rngAddress = Range("D2")

    Set rngTo = Range("F3")

    Set rngZipcode = Range("F4")

    Set rngName = Range("C8")

    Set rngBank = Range("C9")

    Set rngCount = Range("E9")

    Set rngGold = Range("F9")

    

    '입력된 내용 지우기

    i = rngDelete.CurrentRegion.Rows.Count - 4

    rngDelete.Resize(i, 6).delete

    rngAddress = ""

    rngTo = ""

    rngZipcode = ""

    rngName = ""

    rngBank = ""

    rngCount = ""

    rngGold = ""

    

End Sub

세외수입 안내문.xlsm