[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)
    [N5] = Range("N" & ridx)
    [O5] = Range("O" & ridx)
    [P5] = Range("P" & ridx)
End Sub

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

Sub 저장()
    If [D5] = "" Then Exit Sub
    If [E5] = "" 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 & ":M" & ridx).FormulaR1C1 = [매입!K5:M5].FormulaR1C1
    Range("l" & ridx) = [L5]
    Range("m" & ridx) = [M5]
    Range("n" & ridx) = [N5]
    Range("o" & ridx) = [O5]
    Range("p" & ridx) = [P5]
    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 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
    End With
End Sub

결과물