거래처관리_120_well_매트로VBA

^^

거래처관리.xlsm

 

 

Sub 저장()

Dim r As Integer

r = Sheets("거래처대장").Range("A3").End(xlDown).Row + 1

Sheets("거래처대장").Cells(r, 1) = Sheets("거래처카드").Range("E5")
Sheets("거래처대장").Cells(r, 2) = Sheets("거래처카드").Range("G5")
Sheets("거래처대장").Cells(r, 3) = Sheets("거래처카드").Range("E6")

If Sheets("거래처카드").Range("J1") = 1 Then
Sheets("거래처대장").Cells(r, 4) = "법인"
Else
Sheets("거래처대장").Cells(r, 4) = "개인"
End If

Sheets("거래처대장").Cells(r, 5) = Sheets("거래처카드").Range("E7")
Sheets("거래처대장").Cells(r, 6) = Sheets("거래처카드").Range("G7")
Sheets("거래처대장").Cells(r, 7) = Sheets("거래처카드").Range("E8")
Sheets("거래처대장").Cells(r, 8) = Sheets("거래처카드").Range("G8")
Sheets("거래처대장").Cells(r, 9) = Sheets("거래처카드").Range("E9")
Sheets("거래처대장").Cells(r, 10) = Sheets("거래처카드").Range("G9")
Sheets("거래처대장").Cells(r, 11) = Sheets("거래처카드").Range("E10")
Sheets("거래처대장").Cells(r, 12) = Sheets("거래처카드").Range("G10")
Sheets("거래처대장").Cells(r, 13) = Sheets("거래처카드").Range("E11")
Sheets("거래처대장").Cells(r, 14) = Sheets("거래처카드").Range("G11")
Sheets("거래처대장").Cells(r, 15) = Sheets("거래처카드").Range("E12")
Sheets("거래처대장").Cells(r, 16) = Sheets("거래처카드").Range("G12")
Sheets("거래처대장").Cells(r, 17) = Sheets("거래처카드").Range("E13")
Sheets("거래처대장").Cells(r, 18) = Sheets("거래처카드").Range("G13")
Sheets("거래처대장").Cells(r, 19) = Sheets("거래처카드").Range("E14")
Sheets("거래처대장").Cells(r, 20) = Sheets("거래처카드").Range("G14")
Sheets("거래처대장").Cells(r, 21) = Sheets("거래처카드").Range("E15")

End Sub

 

 

 

 

Sub 검색()

검색폼.Show

End Sub

 

 

Sub 지우기()
   
    Sheets("거래처카드").Range("E5:E14,G5:G14,E15:G15,J1").ClearContents
   
End Sub

 

 

Sub 수정()

Dim ran As Range
Dim count As Integer
Dim nw As Integer, r As Integer

count = 0

For Each ran In Range("업체코드")

If Sheets("거래처카드").Range("E5") = ran Then
r = ran.Row
count = count + 1
Exit For
End If

Next ran

If count = 0 Then
nw = MsgBox("일치하는 업체코드를 찾을 수 없습니다" & vbCrLf & _
"새로 저장하시겠습니까?", vbOKCancel, "업데이트 오류")

If nw = vbOK Then
Call 저장
Exit Sub
Else
Exit Sub
End If
End If

Sheets("거래처대장").Cells(r, 2) = Sheets("거래처카드").Range("G5")
Sheets("거래처대장").Cells(r, 3) = Sheets("거래처카드").Range("E6")

If Sheets("거래처카드").Range("J1") = 1 Then
Sheets("거래처대장").Cells(r, 4) = "법인"
Else
Sheets("거래처대장").Cells(r, 4) = "개인"
End If

Sheets("거래처대장").Cells(r, 5) = Sheets("거래처카드").Range("E7")
Sheets("거래처대장").Cells(r, 6) = Sheets("거래처카드").Range("G7")
Sheets("거래처대장").Cells(r, 7) = Sheets("거래처카드").Range("E8")
Sheets("거래처대장").Cells(r, 8) = Sheets("거래처카드").Range("G8")
Sheets("거래처대장").Cells(r, 9) = Sheets("거래처카드").Range("E9")
Sheets("거래처대장").Cells(r, 10) = Sheets("거래처카드").Range("G9")
Sheets("거래처대장").Cells(r, 11) = Sheets("거래처카드").Range("E10")
Sheets("거래처대장").Cells(r, 12) = Sheets("거래처카드").Range("G10")
Sheets("거래처대장").Cells(r, 13) = Sheets("거래처카드").Range("E11")
Sheets("거래처대장").Cells(r, 14) = Sheets("거래처카드").Range("G11")
Sheets("거래처대장").Cells(r, 15) = Sheets("거래처카드").Range("E12")
Sheets("거래처대장").Cells(r, 16) = Sheets("거래처카드").Range("G12")
Sheets("거래처대장").Cells(r, 17) = Sheets("거래처카드").Range("E13")
Sheets("거래처대장").Cells(r, 18) = Sheets("거래처카드").Range("G13")
Sheets("거래처대장").Cells(r, 19) = Sheets("거래처카드").Range("E14")
Sheets("거래처대장").Cells(r, 20) = Sheets("거래처카드").Range("G14")
Sheets("거래처대장").Cells(r, 21) = Sheets("거래처카드").Range("E15")

End Sub

 

 

Private Sub cmd검색_Click()

    Dim ran As Range
    Dim sangho As String
    Dim r As Integer
   
    sangho = Cb업체명.Value
   
    For Each ran In Range("업체명")
       
        If sangho = ran Then r = ran.Row: Exit For
   
    Next ran
   
    Sheets("거래처카드").Range("E5") = Sheets("거래처대장").Cells(r, 1)
    Sheets("거래처카드").Range("G5") = Sheets("거래처대장").Cells(r, 2)
    Sheets("거래처카드").Range("E6") = Sheets("거래처대장").Cells(r, 3)
   
    If Sheets("거래처대장").Cells(r, 4) = "법인" Then
        Sheets("거래처카드").Range("J1") = 1
    Else
        Sheets("거래처카드").Range("J1") = 2
    End If
       
    Sheets("거래처카드").Range("E7") = Sheets("거래처대장").Cells(r, 5)
    Sheets("거래처카드").Range("G7") = Sheets("거래처대장").Cells(r, 6)
    Sheets("거래처카드").Range("E8") = Sheets("거래처대장").Cells(r, 7)
    Sheets("거래처카드").Range("G8") = Sheets("거래처대장").Cells(r, 8)
    Sheets("거래처카드").Range("E9") = Sheets("거래처대장").Cells(r, 9)
    Sheets("거래처카드").Range("G9") = Sheets("거래처대장").Cells(r, 10)
    Sheets("거래처카드").Range("E10") = Sheets("거래처대장").Cells(r, 11)
    Sheets("거래처카드").Range("G10") = Sheets("거래처대장").Cells(r, 12)
    Sheets("거래처카드").Range("E11") = Sheets("거래처대장").Cells(r, 13)
    Sheets("거래처카드").Range("G11") = Sheets("거래처대장").Cells(r, 14)
    Sheets("거래처카드").Range("E12") = Sheets("거래처대장").Cells(r, 15)
    Sheets("거래처카드").Range("G12") = Sheets("거래처대장").Cells(r, 16)
    Sheets("거래처카드").Range("E13") = Sheets("거래처대장").Cells(r, 17)
    Sheets("거래처카드").Range("G13") = Sheets("거래처대장").Cells(r, 18)
    Sheets("거래처카드").Range("E14") = Sheets("거래처대장").Cells(r, 19)
    Sheets("거래처카드").Range("G14") = Sheets("거래처대장").Cells(r, 20)
    Sheets("거래처카드").Range("E15") = Sheets("거래처대장").Cells(r, 21)
   
    Unload Me
   
End Sub
Private Sub UserForm_Initialize()
   
    Cb업체명.RowSource = "업체명"

End Sub
 

 

거래처관리_수정.xlsm