[VBA] 재고 관리 엑셀 자동화 - 매출

기능

• 신규 : 새로운 데이터 매입 입력이 필요한 경우
  • 신규 데이터임을 표시하고, 빈 양식을 제공한다.
• 업데이트 : 기존 데이터를 수정하는 경우
  • 기존 데이터를 더블 클릭했을 때, 입력 폼에 데이터 반영
• 저장 : 입력한 데이터를 저장하는 경우 
  • 신규일 경우 최하단 행에 사용자가 입력한 값을 추가한다
  • 업데이트 일 경우 기존 데이터의 위치에 덮어쓰기 한다.
• 삭제 : 기존 데이터를 삭제하는 경우
  • 기존 데이터를 더블 클릭하여 입력 폼에 반영된 상태에서 삭제 버튼 선택시 해당 행 삭제
• 검색 : 품명, 매입처, 수량, 금액 등을 기준으로 검색하고자 하는 경우
  • 품명, 매입처, 수량 등을 검색할 수 있도록 검색 기능 제공
• 발생, 발행, 수금일 월별 필터 : 월별로 매출 내용을 확인 할 수 있도록 필터 기능 제공
• 거래명세표 : 범위로 선택한 아이템을 거래명세표에 입력해주는 기능
  • 선택한 아이템의 수에 따라 다른 양식의 거래명세표로 출력하는 기능

구현 방법

• 신규 : 신규 값임을 알 수 있도록 ID를 "신규"로 설정하고, 발생일에는 오늘 날짜, 다른 값은 빈 값으로 설정한다.

Sub 신규등록모드()
    [A5] = "신규"
    [B5] = Date
    [C5:J5, N5,P5] = ""
End Sub

• 업데이트 : 데이터가 있는 범위를 더블클릭한 경우 입력폼에 값을 넣는다.
  • 자동수식이 입력된 셀을 제외하고 값을 설정

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Row < 12 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    Cancel = True
    Call 수정모드
End Sub

Sub 수정모드()
    ridx = Selection.Row
    [A5] = Range("A" & ridx)
    [B5] = Range("B" & ridx)
    [C5] = Range("C" & ridx)
    [D5] = Range("D" & ridx)
    [E5] = Range("E" & ridx)
    [F5] = Range("F" & ridx)
    [G5] = Range("G" & ridx)
    [H5] = Range("H" & ridx)
    [I5] = Range("I" & ridx)
    [J5] = Range("J" & ridx)
    [K5] = Range("K" & ridx)
    [L5] = Range("L" & ridx)
    [Q5] = Range("Q" & ridx)
    [R5] = Range("R" & ridx)
End Sub

• 저장 : 필수 데이터 누락을 확인하고, 신규일 경우에는 마지막행+1에 업데이트일 경우 기존 행에 데이터를 쓴다
  • 입력 폼의 수식을 행에 맞게 입력하기 위해서 FormulaR1C1 을 복사한다.
  • 배열 수식 복사를 위해 FormulaArray 를 이용해 값을 복사해준다.
  • 데이터를 쓴 뒤에 입력폼은 초기화하고, 테두리를 그려줄 수 있도록 한다.

Sub 저장()
    If [E5] = "" Or [F5] = "" Then Exit Sub
    If [A5] = "신규" Then
        ridx = [A1048576].End(xlUp).Row + 1
        Range("A" & ridx) = WorksheetFunction.Max([A12:A1048576]) + 1
    Else
        ridx = Range("A11:A1048576").Find([A5]).Row
    End If
    Range("B" & ridx) = [B5]
    Range("C" & ridx) = [C5]
    Range("D" & ridx) = [D5]
    Range("E" & ridx) = [E5]
    Range("F" & ridx) = [F5]
    Range("G" & ridx) = [G5]
    Range("H" & ridx) = [H5]
    Range("I" & ridx) = [I5]
    Range("J" & ridx) = [J5]
    Range("K" & ridx) = [K5]
    Range("L" & ridx) = [L5]
    Range("M" & ridx & ":P" & ridx).FormulaR1C1 = [M5:P5].FormulaR1C1
    Range("N" & ridx).FormulaArray = [N5].FormulaR1C1
    Range("Q" & ridx) = [Q5]
    Range("R" & ridx) = [R5]
    Call 신규등록모드
    Call 테두리
End Sub

• 삭제 : 입력 폼에 ID 값이 숫자로 입력되어 있는 경우 해당 줄을 삭제하고 입력폼을 초기화 한다.

Sub 삭제()
    If IsNumeric([A5]) Then
        ridx = Range("A11:A1048576").Find([A5]).Row
        Rows(ridx).Delete
        Call 신규등록모드
    End If
End Sub

• 검색 : AdvancedFilter 를 통해 값을 검색할 수 있도록 한다.
  • 검색 범위 : Range("A12").CurrentRegion
  • 검색 조건 : Range("A8").CurrentRegion

Sub 검색()
    Range("A12").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("A8").CurrentRegion, Unique:=False
End Sub

• 발생, 발행, 수금일 필터 : AutoFilter를 통해 월별 데이터를 필터링 할 수 있도록 한다.
  • 세 필터의 조합도 동작할 수 있도록 처리부를 하나로 통합한다.
  • 세 값이 모두 없는 경우 AutoFilter를 해제한다.

Sub datefilter()
    With Range("B11")
        If datefilter1 = 0 And datefilter2 = 0 And datefilter3 = 0 Then
            AutoFilterMode = False
            Exit Sub
        End If
        If datefilter1 <> 0 Then .AutoFilter 2, datefilter1, xlFilterDynamic
        If datefilter2 = 0 Then
            .AutoFilter Field:=3
        ElseIf datefilter2 = 1 Then
            .AutoFilter Field:=3, Criteria1:="="
        Else
            .AutoFilter 3, datefilter2, xlFilterDynamic
        End If
        If datefilter3 = 0 Then
            .AutoFilter Field:=4
        ElseIf datefilter3 = 1 Then
            .AutoFilter Field:=4, Criteria1:="="
        Else
            .AutoFilter 4, datefilter3, xlFilterDynamic
        End If
    End With
End Sub

• 거래명세표 : 필터 처리된 후의 선택인지 확인하고 각 행의 필요 데이터를 거래명세표에 입력한다.
  • Range(Selection.Address).Rows.SpecialCells(xlCellTypeVisible).Count : 필터 된 영역일 경우 선택된 줄 수 확인
  • Selection.Rows.SpecialCells(xlCellTypeVisible) : 필터 된 데이터만 선택
  • 값 입력 후 거래명세표 시트로 이동

Sub 명세표작성()
    If Range(Selection.Address).Row < 12 Then Exit Sub
    Dim ws As Worksheet
    Dim inum As Integer
    Dim mrng As Range
    cnt = 0
    If Selection.Rows.Count = 1 Then
        inum = 1
        Set mrng = Selection
    Else
        inum = Range(Selection.Address).Rows.SpecialCells(xlCellTypeVisible).Count
        Set mrng = Selection.Rows.SpecialCells(xlCellTypeVisible)
    End If
    If inum > 16 Then
        Set ws = Sheets("거래명세표-long")
        ws.Range("B14:P50").Value = ""
    Else
        Set ws = Sheets("거래명세표")
        ws.Range("B14:P29").Value = ""
    End If
    For Each rw In mrng.Rows
        ridx = rw.Row
        If cnt = 0 Then
            [vc_value!A1] = Range("Q" & ridx)
            [vc_value!A2] = Range("E" & ridx)
        End If
        Range("C" & ridx) = Date
        vcnt = 14 + cnt
        ws.Range("B" & vcnt) = Format(Date, "yy")
        ws.Range("C" & vcnt) = Format(Month(Now), "00")
        ws.Range("D" & vcnt) = Format(Day(Now), "00")
        ws.Range("E" & vcnt) = Range("F" & ridx)
        ws.Range("M" & vcnt) = Range("I" & ridx)
        ws.Range("P" & vcnt) = Range("J" & ridx)
        cnt = cnt + 1
    Next
    ws.Activate
End Sub

결과물