툴잇지 with 우정기기 파일 열고 닫기도 확연히 차이 날 정도로 느려졌고 VBA파일도 동작이 너무 느리네요.
안그래도 이전에 10만개 이상의 데이터를 돌리느라 시간이 좀 걸렸던 프로그램인데 지금은 답이 안보입니다.
혹시 이유를 알 수 있을까요?
이것때문에 돌아버리겠습니다.
아래는 제가 짠 코드입니다.(스압주의)
비효율적인 코드는 많습니다. 다만 이전에는 그래도 나름 잘 돌아가던게 안돌아가서 문의드립니다. ㅜㅜ
Option Compare Text
Private Sub 네이버_Click()
Dim searchNaver As String
Dim cretecNameShop As String
Dim cretecOrder As String
Dim dongsinNameShop As String
Dim codeNum As String
Dim addressNum As String
Dim sumTotal As Double
Dim countTotal As Integer
Dim countBrand As Integer
Dim lastRow
Dim i As Integer
Dim j As Integer
Dim h As Integer
Dim k As Integer
Dim L As Integer
searchNaver = ThisWorkbook.Path & "\네이버페이 선택주문조회.xlsx"
cretecNameShop = ThisWorkbook.Path & "\" & Date & "_장바구니.xlsx"
cretecOrder = ThisWorkbook.Path & "\" & Date & "_책임주문.xlsx"
dongsinNameShop = ThisWorkbook.Path & "\" & Date & "_동신 장바구니.xlsx"
j = 0
h = 0
k = 0
L = 0
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=cretecNameShop
ActiveWorkbook.Close
Workbooks.Add
Sheets("Sheet1").Cells(1, 1) = "No."
Sheets("Sheet1").Cells(1, 2) = "동신코드"
Sheets("Sheet1").Cells(1, 3) = "주문자코드"
Sheets("Sheet1").Cells(1, 4) = "바코드"
Sheets("Sheet1").Cells(1, 5) = "수량"
Sheets("Sheet1").Cells(1, 6) = "상품명"
Sheets("Sheet1").Cells(1, 7) = "규격"
Sheets("Sheet1").Cells(1, 8) = "재질"
Sheets("Sheet1").Cells(1, 9) = "브랜드"
Sheets("Sheet1").Cells(1, 10) = "단위"
Sheets("Sheet1").Cells(1, 11) = "추가배송비"
Sheets("Sheet1").Cells(1, 12) = "주문번호"
Sheets("Sheet1").Cells(1, 13) = "상품주문번호"
Sheets("Sheet1").Columns("L").Hidden = True
Sheets("Sheet1").Columns("M").Hidden = True
Sheets("Sheet1").Cells(1, 14) = "방문수령"
Sheets("Sheet1").Cells(1, 15) = "무게확인"
Sheets("Sheet1").Cells(1, 16) = "수취인"
ActiveWorkbook.SaveAs Filename:=dongsinNameShop
ActiveWorkbook.Close
Workbooks.Add
Sheets("Sheet1").Cells(1, 1) = "화물업체"
Sheets("Sheet1").Cells(1, 2) = "우편번호"
Sheets("Sheet1").Cells(1, 3) = "배송주소(주소1)"
Sheets("Sheet1").Cells(1, 4) = "상세주소(주소2)"
Sheets("Sheet1").Cells(1, 5) = "영업소"
Sheets("Sheet1").Cells(1, 6) = "실배송처명"
Sheets("Sheet1").Cells(1, 7) = "배송전화"
Sheets("Sheet1").Cells(1, 8) = "배송휴대폰"
Sheets("Sheet1").Cells(1, 9) = "현착불구분"
Sheets("Sheet1").Cells(1, 10) = "상품코드"
Sheets("Sheet1").Cells(1, 11) = "수량"
Sheets("Sheet1").Cells(1, 12) = "실배송처비고"
Sheets("Sheet1").Cells(1, 13) = "주문비고"
Sheets("Sheet1").Cells(1, 14) = "재고부족시 요청사항"
Sheets("Sheet1").Cells(1, 15) = "업체 관리전용 메모"
Sheets("Sheet1").Columns("O").Hidden = True
Sheets("Sheet1").Cells(1, 16) = "브랜드"
Sheets("Sheet1").Cells(1, 17) = "매입처 비교"
Sheets("Sheet1").Cells(1, 18) = "추가 배송비"
Sheets("Sheet1").Cells(1, 19) = "IN/OUT"
Sheets("Sheet1").Cells(1, 20) = "추가할인 확인"
ActiveWorkbook.SaveAs Filename:=cretecOrder
ActiveWorkbook.Close
Workbooks.Open Filename:=cretecNameShop
Workbooks.Open Filename:=dongsinNameShop
Workbooks.Open Filename:=cretecOrder
Workbooks.Open Filename:=searchNaver
lastRow = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To lastRow
On Error Resume Next '-------- error 발생 시 skip
Err.Clear
codeNum = Mid(Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 6), 2, Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.Search("]", Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 6)) - 2)
If codeNum = "E00-00001" Or codeNum = "E00-00002" Then
If Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i - 1, 2) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 2) Then
codeNum = Mid(Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i - 1, 6), 2, Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.Search("]", Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i - 1, 6)) - 2)
ElseIf Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i + 1, 2) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 2) Then
codeNum = Mid(Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i + 1, 6), 2, Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.Search("]", Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i + 1, 6)) - 2)
End If
End If
'If codeNum Like "*E00-00*" Then
'If Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 4) Like "*배송비*" Then
'If Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 3) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i - 1, 3) Then
'Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 6) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i - 1, 6)
'Else
'Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 6) = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(Mid(Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i + 1, 4), 2, 8), ThisWorkbook.Sheets("Sheet3").Range("A:B"), 2, 0)
'End If
'End If
'Else
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 8) = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(codeNum, ThisWorkbook.Sheets("Sheet3").Range("A:B"), 2, 0)
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 9) = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(codeNum, ThisWorkbook.Sheets("Sheet5").Range("A:B"), 2, 0)
'End If
Next i
For i = 3 To lastRow
On Error Resume Next '-------- error 발생 시 skip
Err.Clear
codeNum = Mid(Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 6), 2, Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.Search("]", Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 6)) - 2)
addressNum = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 26)
'sumTotal = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.SumIf(Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Range("I3:I" & lastRow), Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Cells(i, 9), Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Range("J3:J" & lastRow))
sumTotal = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.SumIfs(Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Range("K3:K" & lastRow), Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Range("B3:B" & lastRow), Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Cells(i, 2), Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Range("V3:V" & lastRow), "<>E0000001", Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Range("V3:V" & lastRow), "<>E0000002")
'countTotal = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.CountIf(Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Range("I3:I" & lastRow), Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Cells(i, 9))
countTotal = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.CountIfs(Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Range("B3:B" & lastRow), Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Cells(i, 2), Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Range("V3:V" & lastRow), "<>E0000001", Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Range("V3:V" & lastRow), "<>E0000002")
countBrand = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.CountIfs(Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Range("B3:B" & lastRow), Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Cells(i, 2), Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Range("H3:H" & lastRow), Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Cells(i, 8), Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Range("V3:V" & lastRow), "<>E0000001", Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Range("V3:V" & lastRow), "<>E0000002")
If codeNum = "E00-00001" Or codeNum = "E00-00002" Then
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 8) = "추가 배송비"
End If
If countTotal <> countBrand Or sumTotal < 21780 Or Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 9) = 1 Or Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 30) Like "*방문*" Or (codeNum Like "E*" And codeNum <> "E00-00001" And codeNum <> "E00-00002") Then
'If Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 29) = "선결제" Then
'End If
If codeNum Like "ED*" Then
Workbooks(Date & "_동신 장바구니.xlsx").Sheets("Sheet1").Cells(i - 1 - h - j - L, 1) = k + 1 'No.
Workbooks(Date & "_동신 장바구니.xlsx").Sheets("Sheet1").Cells(i - 1 - h - j - L, 2) = "'" & Mid(codeNum, 3, 1) & Mid(codeNum, 5, 20) '동신코드
Workbooks(Date & "_동신 장바구니.xlsx").Sheets("Sheet1").Cells(i - 1 - h - j - L, 5) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 7) '수량
Workbooks(Date & "_동신 장바구니.xlsx").Sheets("Sheet1").Cells(i - 1 - h - j - L, 9) = Mid(Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 8), 6, 30) '브랜드
Workbooks(Date & "_동신 장바구니.xlsx").Sheets("Sheet1").Cells(i - 1 - h - j - L, 11) = "추가 배송비 : " & Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(addressNum, ThisWorkbook.Sheets("Sheet2").Range("A:B"), 2, 0) '추가배송비
Workbooks(Date & "_동신 장바구니.xlsx").Sheets("Sheet1").Cells(i - 1 - h - j - L, 12) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 2) '주문번호
Workbooks(Date & "_동신 장바구니.xlsx").Sheets("Sheet1").Cells(i - 1 - h - j - L, 13) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 1) '상품주문번호
'방문수령
If Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 30) Like "*방문*" Then
Workbooks(Date & "_동신 장바구니.xlsx").Sheets("Sheet1").Cells(i - 1 - h - j - L, 14) = "방문수령"
Workbooks(Date & "_동신 장바구니.xlsx").Sheets("Sheet1").Cells(i - 1 - h - j - L, 14).Interior.ColorIndex = 17
End If
Workbooks(Date & "_동신 장바구니.xlsx").Sheets("Sheet1").Cells(i - 1 - h - j - L, 10) = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(codeNum, ThisWorkbook.Sheets("Sheet3").Range("A:E"), 5, 0) '단위
If Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 9) = 1 Then '무게확인 (화물/택배 불가)
Workbooks(Date & "_동신 장바구니.xlsx").Sheets("Sheet1").Cells(i - 1 - h - j - L, 15) = "무게확인!"
Workbooks(Date & "_동신 장바구니.xlsx").Sheets("Sheet1").Cells(i - 1 - h - j - L, 15).Interior.ColorIndex = 3
End If
Workbooks(Date & "_동신 장바구니.xlsx").Sheets("Sheet1").Cells(i - 1 - h - j - L, 16) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 4) '수취인
k = k + 1
Else
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 1) = Left(codeNum, 8) '상품코드
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 2) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 7) '수량
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 3) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 4) '수취인
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 4) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 8) '브랜드
If Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 4) = "OH" Then
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 4) = "오에이치"
End If
If Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 4) Like "(*" Then
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 4).Characters(Start:=istart, Length:=Application.WorksheetFunction.Search(") ", Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 4))).Font.Color = vbRed
End If
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 5) = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 4), ThisWorkbook.Sheets("Sheet4").Range("A:B"), 2, 0) '가격비교
If Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 5) <> "" Then
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 5).Characters(Start:=istart, Length:=Application.WorksheetFunction.Search("가격", Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 5)) - 2).Font.Color = vbRed
End If
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 6) = "추가 배송비 : " & Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(addressNum, ThisWorkbook.Sheets("Sheet2").Range("A:B"), 2, 0) '추가배송비
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 7) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 2) '주문번호
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 8) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 1) '상품주문번호
'방문수령
If Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 30) Like "*방문*" Then
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 9) = "방문수령"
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 9).Interior.ColorIndex = 17
End If
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 10) = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(codeNum, ThisWorkbook.Sheets("Sheet3").Range("A:E"), 5, 0) '단위
If Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 9) = 1 Then '무게확인 (화물/택배 불가)
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 11) = "무게확인!"
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 11).Interior.ColorIndex = 3
End If
j = j + 1
End If
ElseIf sumTotal < 24360 Then
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 1) = Left(codeNum, 8) '상품코드
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 2) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 7) '수량
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 3) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 4) '구매자
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 4) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 8) '브랜드
If Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 4) = "OH" Then
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 4) = "오에이치"
End If
If Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 4) Like "(*" Then
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 4).Characters(Start:=istart, Length:=Application.WorksheetFunction.Search(") ", Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 4))).Font.Color = vbRed
End If
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 5) = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 4), ThisWorkbook.Sheets("Sheet4").Range("A:B"), 2, 0) '가격비교
If Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 5) <> "" Then
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 5).Characters(Start:=istart, Length:=Application.WorksheetFunction.Search("가격", Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 5)) - 2).Font.Color = vbRed
End If
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 6) = "추가 배송비 : " & Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(addressNum, ThisWorkbook.Sheets("Sheet2").Range("A:B"), 2, 0) '추가배송비
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 7) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 2) '주문번호
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 8) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 1) '상품주문번호
'방문수령
If Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 30) Like "*방문*" Then
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 9) = "방문수령"
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 9).Interior.ColorIndex = 17
End If
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 10) = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(codeNum, ThisWorkbook.Sheets("Sheet3").Range("A:E"), 5, 0) '단위
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 1).Interior.ColorIndex = 44
'Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 2).Interior.ColorIndex = 44
'Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 3).Interior.ColorIndex = 44
'Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 4).Interior.ColorIndex = 44
'Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2 - h - k, 5).Interior.ColorIndex = 44
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 1) = "CJ택배" '택배사
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 2) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 26) '우편번호
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 3) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 42) '배송주소
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 4) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 43) '상세주소
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 5) = ""
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 6) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 4) '수취인
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 7) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 28) '연락처
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 8) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 29) '연락처2
'배송비 유형
If Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 24) = "착불" Or Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 25) = 2200 Then
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 9) = "착불"
Else
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 9) = "현불"
'If Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 11) <> 1 Then
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 13) = "합포장 요망."
'End If
End If
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 10) = codeNum '상품코드
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 11) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 7) '수량
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 12) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 30) '실배송처비고
'If countTotal <> 1 Then 'Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i - 1 - j - k, 3) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 3) Then
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 13) = "합포장 요망."
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 2 - j, 13) = "합포장 요망."
'End If
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 15) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 2) '업체관리전용메모(주문번호)
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 16) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 8) '브랜드
If Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 16) = "OH" Then
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 16) = "오에이치"
End If
If Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 16) Like "(*" Then
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 16).Characters(Start:=istart, Length:=Application.WorksheetFunction.Search(") ", Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 16))).Font.Color = vbRed
End If
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 17) = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 16), ThisWorkbook.Sheets("Sheet4").Range("A:B"), 2, 0)
If Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 17) <> "" Then
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 17).Characters(Start:=istart, Length:=Application.WorksheetFunction.Search("가격", Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 17)) - 2).Font.Color = vbRed
End If
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 18) = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(addressNum, ThisWorkbook.Sheets("Sheet2").Range("A:B"), 2, 0) '추가배송비
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 18).Font.Color = vbBlue
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 19) = "'" & Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(codeNum, ThisWorkbook.Sheets("Sheet3").Range("A:D"), 3, 0) & "/" & Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(codeNum, ThisWorkbook.Sheets("Sheet3").Range("A:D"), 4, 0) & " " & Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(codeNum, ThisWorkbook.Sheets("Sheet3").Range("A:E"), 5, 0) 'IN/OUT 단위
'If sumTotal > 100000 Then
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 17) = "확인필요"
'End If
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 1).Interior.ColorIndex = 44
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 2).Interior.ColorIndex = 44
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 3).Interior.ColorIndex = 44
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 4).Interior.ColorIndex = 44
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 5).Interior.ColorIndex = 44
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 6).Interior.ColorIndex = 44
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 7).Interior.ColorIndex = 44
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 8).Interior.ColorIndex = 44
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 9).Interior.ColorIndex = 44
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 10).Interior.ColorIndex = 44
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 11).Interior.ColorIndex = 44
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 12).Interior.ColorIndex = 44
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 13).Interior.ColorIndex = 44
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 14).Interior.ColorIndex = 44
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 15).Interior.ColorIndex = 44
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 16).Interior.ColorIndex = 44
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 17).Interior.ColorIndex = 44
'j = j + 1
'h = h + 1
L = L + 1
Else
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 1) = "CJ택배"
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 2) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 26) '우편번호
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 3) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 42) '배송주소
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 4) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 43) '상세주소
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 5) = ""
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 6) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 4) '수취인
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 7) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 28) '연락처
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 8) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 29) '연락처2
If Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 24) = "착불" Or Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 25) = 2200 Then
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 9) = "착불"
Else
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 9) = "현불"
'If Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 11) <> 1 Then
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 13) = "합포장 요망."
'End If
End If
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 10) = codeNum '상품코드
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 11) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 7) '수량
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 12) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 30) '실배송처비고
'If countTotal <> 1 Then 'Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i - 1 - j - k, 3) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 3) Then
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 13) = "합포장 요망."
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 2 - j, 13) = "합포장 요망."
'End If
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 15) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 2) '업체관리전용메모(주문번호)
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 16) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 8) '브랜드
If Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 16) = "OH" Then
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 16) = "오에이치"
End If
If Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 16) Like "(*" Then
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 16).Characters(Start:=istart, Length:=Application.WorksheetFunction.Search(") ", Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 16))).Font.Color = vbRed
End If
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 17) = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 16), ThisWorkbook.Sheets("Sheet4").Range("A:B"), 2, 0)
If Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 17) <> "" Then
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 17).Characters(Start:=istart, Length:=Application.WorksheetFunction.Search("가격", Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 17)) - 2).Font.Color = vbRed
End If
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 18) = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(addressNum, ThisWorkbook.Sheets("Sheet2").Range("A:B"), 2, 0) '추가배송비
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 18).Font.Color = vbBlue
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 19) = "'" & Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(codeNum, ThisWorkbook.Sheets("Sheet3").Range("A:D"), 3, 0) & "/" & Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(codeNum, ThisWorkbook.Sheets("Sheet3").Range("A:D"), 4, 0) & " " & Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(codeNum, ThisWorkbook.Sheets("Sheet3").Range("A:E"), 5, 0) 'IN/OUT 단위
If sumTotal > 110000 Then
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 20) = "확인필요"
If sumTotal > 1000000 Then
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 20).Interior.ColorIndex = 22
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 20).Font.ColorIndex = 30
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 20).Font.Bold = True
End If
End If
h = h + 1
End If
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Columns("G:H").Hidden = True
'Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Columns("J").Hidden = True
If Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 12) Like "*방문*" Then
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j - k, 12).Interior.ColorIndex = 17
End If
Next i
Workbooks(Date & "_장바구니.xlsx").Save
Workbooks(Date & "_동신 장바구니.xlsx").Save
'Workbooks(Date & "_장바구니.xlsx").Close
Workbooks(Date & "_책임주문.xlsx").Save
'Workbooks(Date & "_책임주문.xlsx").Close
Workbooks("네이버페이 선택주문조회.xlsx").Close (False)
MsgBox " " & Chr(13) & "추가배송비 기입" & Chr(13) & "직송 - 합포장 여부 확인" & Chr(13) & " "
End Sub
Private Sub Cnplus_Click()
Dim searchCretec As String
Dim searchNaver As String
Dim CJName As String
Dim lastRowNaver
Dim lastRowCretec
Dim lastRowCJ
Dim i As Integer
Dim j As Integer
Dim k As Integer
searchCretec = ThisWorkbook.Path & "\orderListDown.xls"
searchNaver = ThisWorkbook.Path & "\네이버페이 선택주문조회.xlsx"
CJName = ThisWorkbook.Path & "\운송장입력.xlsx"
k = 2
Workbooks.Open Filename:=searchCretec
Workbooks.Open Filename:=searchNaver
Workbooks.Open Filename:=CJName
lastRowCretec = Workbooks("orderListDown.xls").Sheets("공구상 쇼핑몰 주문 내역").Cells(65536, 1).End(xlUp).Row
lastRowNaver = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(Rows.Count, 1).End(xlUp).Row
Workbooks("운송장입력.xlsx").Sheets("sheet1").Rows("2:" & Rows.Count).Delete
For i = 3 To lastRowCretec
Workbooks("운송장입력.xlsx").Sheets("sheet1").Cells(k, 1) = Workbooks("orderListDown.xls").Sheets("공구상 쇼핑몰 주문 내역").Cells(i, 8)
Workbooks("운송장입력.xlsx").Sheets("sheet1").Cells(k, 2) = Workbooks("orderListDown.xls").Sheets("공구상 쇼핑몰 주문 내역").Cells(i, 6)
Workbooks("운송장입력.xlsx").Sheets("sheet1").Cells(k, 3) = Workbooks("orderListDown.xls").Sheets("공구상 쇼핑몰 주문 내역").Cells(i, 7)
Workbooks("운송장입력.xlsx").Sheets("sheet1").Cells(k, 4) = Workbooks("orderListDown.xls").Sheets("공구상 쇼핑몰 주문 내역").Cells(i, 10)
Workbooks("운송장입력.xlsx").Sheets("sheet1").Cells(k, 6) = Workbooks("orderListDown.xls").Sheets("공구상 쇼핑몰 주문 내역").Cells(i, 2)
If Workbooks("orderListDown.xls").Sheets("공구상 쇼핑몰 주문 내역").Cells(i, 16) >= 3000 Then
Workbooks("운송장입력.xlsx").Sheets("sheet1").Cells(k, 7) = 3
Else
Workbooks("운송장입력.xlsx").Sheets("sheet1").Cells(k, 7) = 2
End If
Workbooks("운송장입력.xlsx").Sheets("sheet1").Cells(k, 8) = "극소"
Workbooks("운송장입력.xlsx").Sheets("sheet1").Cells(k, 9) = 1
Workbooks("운송장입력.xlsx").Sheets("sheet1").Cells(k, 10) = Workbooks("orderListDown.xls").Sheets("공구상 쇼핑몰 주문 내역").Cells(i, 11)
k = k + 1
Next i
For j = 3 To lastRowNaver
Workbooks("운송장입력.xlsx").Sheets("sheet1").Cells(k, 1) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 4) '수취인
Workbooks("운송장입력.xlsx").Sheets("sheet1").Cells(k, 2) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 28) '연락처
Workbooks("운송장입력.xlsx").Sheets("sheet1").Cells(k, 3) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 29) '연락처2
Workbooks("운송장입력.xlsx").Sheets("sheet1").Cells(k, 4) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 27) '주소
Workbooks("운송장입력.xlsx").Sheets("sheet1").Cells(k, 6) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 2) & " " & Mid(Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 6), 11) '품목명
'운임구분
If Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 24) = "선결제" Then
Workbooks("운송장입력.xlsx").Sheets("sheet1").Cells(k, 7) = 3
Else
Workbooks("운송장입력.xlsx").Sheets("sheet1").Cells(k, 7) = 2
End If
If Workbooks("운송장입력.xlsx").Sheets("sheet1").Cells(k, 6) Like "*택배 추가 배송비" Then
Workbooks("운송장입력.xlsx").Sheets("sheet1").Cells(k, 7).Interior.ColorIndex = 13
End If
Workbooks("운송장입력.xlsx").Sheets("sheet1").Cells(k, 8) = "극소" '박스타입
Workbooks("운송장입력.xlsx").Sheets("sheet1").Cells(k, 9) = 1 '박스수량
Workbooks("운송장입력.xlsx").Sheets("sheet1").Cells(k, 10) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 30) '배송메세지
k = k + 1
Next j
lastRowCJ = Workbooks("운송장입력.xlsx").Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Workbooks("운송장입력.xlsx").Sheets("sheet1").Range("A1:J500").RemoveDuplicates Array(1, 4), xlYes
Workbooks("orderListDown.xls").Sheets("공구상 쇼핑몰 주문 내역").Rows("3:100").Delete
Workbooks("orderListDown.xls").Save
Workbooks("orderListDown.xls").Close
Workbooks("네이버페이 선택주문조회.xlsx").Close
Workbooks("운송장입력.xlsx").Save
'Workbooks("운송장입력.xlsx").Close
MsgBox "박스수량, 현불/착불 확인"
End Sub
Private Sub 단가체크_Click()
'(네이버상품가격-상품정보 공급가격)/상품정보 공급가격 *100 < -1
Dim searchNaver As String
Dim cretecProduct As String
Dim lastRow
Dim lastRowProduct
Dim i As Integer
Dim price
searchNaver = ThisWorkbook.Path & "\네이버페이 선택주문조회.xlsx"
cretecProduct = ThisWorkbook.Path & "\상품정보_다운로드"
Workbooks.Open Filename:=searchNaver
Workbooks.Open Filename:=cretecProduct
lastRow = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(Rows.Count, 1).End(xlUp).Row
lastRowProduct = Workbooks("상품정보_다운로드").Sheets("상품정보_다운로드").Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To lastRow
If Not (Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 6) Like "*E*") Then
price = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(Mid(Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 6), 2, 8), Workbooks("상품정보_다운로드").Sheets("상품정보_다운로드").Range("A2:O" & lastRowProduct), 15, 0)
If (Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 12) - price) / price * 100 <= -2 Then
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 6).Interior.ColorIndex = 14
End If
End If
Next i
Workbooks("상품정보_다운로드").Close
Workbooks("네이버페이 선택주문조회.xlsx").Save
End Sub
Private Sub 송장번호입력_Click()
Dim searchCretec As String
Dim searchNaver As String
Dim searchCJ As String
Dim lastRowNaver
Dim lastRowCretec
Dim lastRowCJ
Dim i As Integer
Dim j As Integer
Dim delCount As Integer
Dim creCount As Integer
Dim cjCount As Integer
Dim stuffName As String
Dim num As String
Dim addPrice2500 As Integer
Dim addPrice3000 As Integer
searchCretec = ThisWorkbook.Path & "\배송조회.xlsx"
searchNaver = ThisWorkbook.Path & "\네이버페이 선택주문조회.xlsx"
searchCJ = ThisWorkbook.Path & "\운송장출력상세.xlsx"
Workbooks.Open Filename:=searchNaver
Workbooks.Open Filename:=searchCretec
Workbooks.Open Filename:=searchCJ
lastRowCretec = Workbooks("배송조회.xlsx").Sheets("상품 목록").Cells(Rows.Count, 1).End(xlUp).Row
lastRowNaver = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(Rows.Count, 1).End(xlUp).Row
lastRowCJ = Workbooks("운송장출력상세.xlsx").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Workbooks("배송조회.xlsx").Sheets("상품 목록").Range("A2:S" & lastRowCretec).Sort key1:=Workbooks("배송조회.xlsx").Sheets("상품 목록").Range("R2"), order1:=xlAscending, Header:=xlNo
delCount = 0
creCount = 0
cjCount = 0
For i = 2 To lastRowCJ
num = Left(Workbooks("운송장출력상세.xlsx").Sheets("Sheet1").Cells(i, 22), 16)
For j = 3 To lastRowNaver
If num = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 2) And Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 8) = "" Then
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 8) = "'" & Left(Workbooks("운송장출력상세.xlsx").Sheets("Sheet1").Cells(i, 6), 4) & Mid(Workbooks("운송장출력상세.xlsx").Sheets("Sheet1").Cells(i, 6), 6, 4) & Right(Workbooks("운송장출력상세.xlsx").Sheets("Sheet1").Cells(i, 6), 4)
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 9) = "CJ대한통운"
'Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 8) = "택배, 등기, 소포"
'If Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 18) = "E0000001" Or Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 18) = "E0000002" Then
'Else
cjCount = cjCount + 1
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 8).Interior.ColorIndex = 12
'End If
End If
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 10) = "택배, 등기, 소포"
Next j
num = ""
Next i
For i = 2 To lastRowCretec
If Workbooks("배송조회.xlsx").Sheets("상품 목록").Cells(i, 12) = "책임택배" Then
ElseIf Workbooks("배송조회.xlsx").Sheets("상품 목록").Cells(i - 1, 14) = Workbooks("배송조회.xlsx").Sheets("상품 목록").Cells(i, 14) And Workbooks("배송조회.xlsx").Sheets("상품 목록").Cells(i - 1, 15) <> Workbooks("배송조회.xlsx").Sheets("상품 목록").Cells(i, 15) Then
Workbooks("배송조회.xlsx").Sheets("상품 목록").Cells(i - 1, 14).Interior.ColorIndex = 14
Workbooks("배송조회.xlsx").Sheets("상품 목록").Cells(i, 14).Interior.ColorIndex = 14
End If
Next i
For j = 3 To lastRowNaver
On Error Resume Next '-------- error 발생 시 skip
Err.Clear
stuffName = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.Index(Workbooks("배송조회.xlsx").Sheets("상품 목록").Range("L2:R" & lastRowCretec), Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.Match(Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 2), Workbooks("배송조회.xlsx").Sheets("상품 목록").Range("R2:R" & lastRowCretec), 0), 1)
num = Mid(Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.Index(Workbooks("배송조회.xlsx").Sheets("상품 목록").Range("L2:R" & lastRowCretec), Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.Match(Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 2), Workbooks("배송조회.xlsx").Sheets("상품 목록").Range("R2:R" & lastRowCretec), 0), 4), 1, Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.Search("/", Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.Index(Workbooks("배송조회.xlsx").Sheets("상품 목록").Range("L2:R" & lastRowCretec), Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.Match(Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 2), Workbooks("배송조회.xlsx").Sheets("상품 목록").Range("R2:R" & lastRowCretec), 0), 4)) - 1)
If num = "" Then
If Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 22) = "E0000001" Or Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 22) = "E0000002" Then
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 10) = "택배, 등기, 소포"
End If
If stuffName = "퀵" Then
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 9) = ""
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 10) = "퀵서비스"
creCount = creCount + 1
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 8).Interior.ColorIndex = 12
'delCount = delCount + 1 살려!
ElseIf stuffName = "방문" Then
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 9) = ""
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 10) = "방문수령"
creCount = creCount + 1
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 8).Interior.ColorIndex = 12
'delCount = delCount + 1 살려!
End If
Else
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 8) = "'" & num
If Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 22) = "E0000001" Or Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 22) = "E0000002" Then
Else
creCount = creCount + 1
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 8).Interior.ColorIndex = 12
End If
If stuffName = "택배 (CJ택배)" Then
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 9) = "CJ대한통운"
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 10) = "택배, 등기, 소포"
'ElseIf stuffName = "퀵" Then
'Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 7) = ""
'Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 8) = "퀵서비스"
'delCount = delCount + 1 살려!
'ElseIf stuffName = "방문" Then
'Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 7) = ""
'Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 8) = "방문수령"
'delCount = delCount + 1 살려!
ElseIf stuffName = "책임택배" Then
Else
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 9) = Mid(stuffName, 5, 2) & "택배"
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 10) = "택배, 등기, 소포"
End If
End If
'Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(j, 8) = "택배, 등기, 소포"
stuffName = ""
num = ""
Next j
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Rows(1).Delete
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Name = "발송처리"
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발송처리").Range("A2:AR" & lastRowNaver - 1).Sort key1:=Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발송처리").Range("H2"), order1:=xlDescending, Header:=xlNo
'lastRowNaver = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발송처리").Cells(Rows.Count, 6).End(xlUp).Row 살려!
'addPrice3000 = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.CountIf(Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발송처리").Range("R2:R" & lastRowNaver), "E0000001") 살려!
'addPrice2500 = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.CountIf(Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발송처리").Range("R2:R" & lastRowNaver), "E0000002") 살려!
'Workbooks("배송조회.xlsx").Close
Workbooks("운송장출력상세.xlsx").Close
Workbooks("네이버페이 선택주문조회.xlsx").Save
Workbooks("배송조회.xlsx").Save
MsgBox "현불 2Box 이상 Check!!!" & Chr(13) & "수취인 같은 주문건 2Box 이상 (같은 수취인 다른 송장번호) Check!!!"
MsgBox "숫자 미리 확인해보기!!!" & Chr(13) & creCount + cjCount & " (책임 : " & creCount & ",CJ : " & cjCount & ") 맞나요?" 'lastRowNaver - 1 - addPrice3000 - addPrice2500 + delCount & " (책임 : " & creCount & ",CJ : " & cjCount & ") 맞나요?" 살려!
'Workbooks("네이버페이 선택주문조회.xlsx").Close
End Sub
Private Sub 장바구니_Click()
Dim searchNaver As String
Dim cretecName As String
Dim lastRow
Dim i As Integer
searchNaver = ThisWorkbook.Path & "\네이버페이 선택주문조회.xlsx"
cretecName = ThisWorkbook.Path & "\" & Date & "_장바구니.xlsx"
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=cretecName
ActiveWorkbook.Close
Workbooks.Open Filename:=cretecName
Workbooks.Open Filename:=searchNaver
lastRow = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(Rows.Count, 1).End(xlUp).Row
'lastRow = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.CountIf(Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Range("I3:I" & lastRow), Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Cells(3, 9))
For i = 3 To lastRow '2 + lastRow
On Error Resume Next '-------- error 발생 시 skip
Err.Clear
codeNum = Mid(Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 6), 2, Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.Search("]", Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 6)) - 2)
addressNum = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 26)
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 8) = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(codeNum, ThisWorkbook.Sheets("Sheet3").Range("A:B"), 2, 0)
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2, 1) = Left(codeNum, 8) '상품코드
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2, 2) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 7) '수량
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2, 3) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 4) '수취인
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2, 4) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 8) '브랜드
If Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2, 4) = "OH" Then
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2, 4) = "오에이치"
End If
If Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2, 4) Like "(*" Then
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2, 4).Characters(Start:=istart, Length:=Application.WorksheetFunction.Search(") ", Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2, 4))).Font.Color = vbRed
End If
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2, 5) = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2, 4), ThisWorkbook.Sheets("Sheet4").Range("A:B"), 2, 0)
If Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2, 5) <> "" Then
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2, 5).Characters(Start:=istart, Length:=Application.WorksheetFunction.Search("가격", Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2, 5)) - 2).Font.Color = vbRed
End If
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2, 6) = "추가 배송비 : " & Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(addressNum, ThisWorkbook.Sheets("Sheet2").Range("A:B"), 2, 0) '추가배송비
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2, 7) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 2) '주문번호
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2, 8) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 1) '상품주문번호
'방문수령
If Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 30) Like "*방문*" Then
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2, 9) = "방문수령"
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2, 9).Interior.ColorIndex = 17
End If
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(i - 2, 10) = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(codeNum, ThisWorkbook.Sheets("Sheet3").Range("A:E"), 5, 0)
Next i
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Columns("G:H").Hidden = True
'Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Columns("J").Hidden = True
Workbooks(Date & "_장바구니.xlsx").Save
'Workbooks(Date & "_장바구니.xlsx").Close
Workbooks("네이버페이 선택주문조회.xlsx").Close (False)
MsgBox " " & Chr(13) & "추가배송비 기입" & Chr(13) & " "
End Sub
Private Sub 주문_Click()
Dim searchNaver As String
Dim cretecName As String
Dim lastRow
Dim i As Integer
searchNaver = ThisWorkbook.Path & "\네이버페이 선택주문조회.xlsx"
cretecName = ThisWorkbook.Path & "\" & Date & "_책임주문.xlsx"
Workbooks.Add
Sheets("Sheet1").Cells(1, 1) = "화물업체"
Sheets("Sheet1").Cells(1, 2) = "우편번호"
Sheets("Sheet1").Cells(1, 3) = "배송주소(주소1)"
Sheets("Sheet1").Cells(1, 4) = "상세주소(주소2)"
Sheets("Sheet1").Cells(1, 5) = "영업소"
Sheets("Sheet1").Cells(1, 6) = "실배송처명"
Sheets("Sheet1").Cells(1, 7) = "배송전화"
Sheets("Sheet1").Cells(1, 8) = "배송휴대폰"
Sheets("Sheet1").Cells(1, 9) = "현착불구분"
Sheets("Sheet1").Cells(1, 10) = "상품코드"
Sheets("Sheet1").Cells(1, 11) = "수량"
Sheets("Sheet1").Cells(1, 12) = "실배송처비고"
Sheets("Sheet1").Cells(1, 13) = "주문비고"
Sheets("Sheet1").Cells(1, 14) = "재고부족시 요청사항"
Sheets("Sheet1").Cells(1, 15) = "업체 관리전용 메모"
Sheets("Sheet1").Columns("O").Hidden = True
Sheets("Sheet1").Cells(1, 16) = "브랜드"
Sheets("Sheet1").Cells(1, 17) = "매입처 비교"
Sheets("Sheet1").Cells(1, 18) = "추가 배송비"
Sheets("Sheet1").Cells(1, 19) = "추가할인 확인"
ActiveWorkbook.SaveAs Filename:=cretecName
ActiveWorkbook.Close
Workbooks.Open Filename:=cretecName
Workbooks.Open Filename:=searchNaver
lastRow = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(Rows.Count, 1).End(xlUp).Row
'lastRow = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.CountIf(Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Range("I3:I" & lastRow), Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Cells(3, 9))
For i = 3 To lastRow '2 + lastRow
On Error Resume Next '-------- error 발생 시 skip
Err.Clear
codeNum = Mid(Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 6), 2, Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.Search("]", Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 6)) - 2)
addressNum = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 26)
countTotal = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.CountIf(Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Range("B3:B" & lastRow), Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Cells(i, 2))
sumTotal = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.SumIf(Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Range("B3:B" & lastRow), Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Cells(i, 2), Workbooks("네이버페이 선택주문조회.xlsx").ActiveSheet.Range("K3:K" & lastRow))
Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 8) = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(codeNum, ThisWorkbook.Sheets("Sheet3").Range("A:B"), 2, 0)
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 1) = "CJ택배" '택배사
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 2) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 26) '우편번호
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 3) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 42) '배송주소
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 4) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 43) '상세주소
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 5) = ""
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 6) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 4) '수취인
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 7) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 28) '연락처
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 8) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 29) '연락처2
If Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 24) = "착불" Or Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 25) = 2200 Then
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 9) = "착불"
Else
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 9) = "현불"
'If Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 11) <> 1 Then
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 13) = "합포장 요망."
'End If
End If
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 10) = codeNum '상품코드
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 11) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 7) '수량
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 12) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 30) '실배송처비고
If Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 12) Like "*방문*" Then
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 12).Interior.ColorIndex = 17
End If
'If countTotal <> 1 Then
'Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 13) = "합포장 요망."
'End If
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 15) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 2) '업체관리전용메모(주문번호)
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 16) = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(i, 8) '브랜드
If Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 16) = "OH" Then
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 16) = "오에이치"
End If
If Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 16) Like "(*" Then
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 16).Characters(Start:=istart, Length:=Application.WorksheetFunction.Search(") ", Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 16))).Font.Color = vbRed
End If
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 17) = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j, 16), ThisWorkbook.Sheets("Sheet4").Range("A:B"), 2, 0)
If Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 17) <> "" Then
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 17).Characters(Start:=istart, Length:=Application.WorksheetFunction.Search("가격", Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 17)) - 2).Font.Color = vbRed
End If
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 18) = Workbooks("네이버페이 선택주문조회.xlsx").Application.WorksheetFunction.VLookup(addressNum, ThisWorkbook.Sheets("Sheet2").Range("A:B"), 2, 0)
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1 - j, 18).Font.Color = vbBlue
If sumTotal > 110000 Then
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 19) = "확인필요"
If sumTotal > 1000000 Then
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 19).Interior.ColorIndex = 22
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 19).Font.ColorIndex = 30
Workbooks(Date & "_책임주문.xlsx").Sheets("Sheet1").Cells(i - 1, 19).Font.Bold = True
End If
End If
Next i
Workbooks(Date & "_책임주문.xlsx").Save
'Workbooks(Date & "_책임주문.xlsx").Close
Workbooks("네이버페이 선택주문조회.xlsx").Close (False)
MsgBox " " & Chr(13) & "추가배송비 기입" & Chr(13) & "직송 - 합포장 여부 확인" & Chr(13) & " "
End Sub
Private Sub 주문서프린트_Click()
Dim cretecShop As String
Dim printNaver As String
Dim naverOrder As String
Dim dongsinShop As String
Dim lastRow
Dim lastRow2
Dim lastRow3
Dim lastRowMast
Dim i As Integer
Dim j As Integer
Dim h As Integer
Dim k As Integer
Dim sumPrice As Double
Dim tmpPrice As Double
Dim flag As Integer
cretecShop = ThisWorkbook.Path & "\" & Date & "_장바구니.xlsx"
printNaver = ThisWorkbook.Path & "\" & Date & "_발주서 프린트.xlsx"
naverOrder = ThisWorkbook.Path & "\네이버페이 선택주문조회.xlsx"
dongsinShop = ThisWorkbook.Path & "\" & Date & "_동신 장바구니.xlsx"
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=printNaver
ActiveWorkbook.Close
Workbooks.Open Filename:=cretecShop
Workbooks.Open Filename:=printNaver
Workbooks.Open Filename:=naverOrder
Workbooks.Open Filename:=dongsinShop
lastRow = Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lastRow2 = Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Cells(Rows.Count, 1).End(xlUp).Row
lastRow3 = Workbooks(Date & "_동신 장바구니.xlsx").Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To lastRow3
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(lastRow + i - 1, 1) = "ED" & Workbooks(Date & "_동신 장바구니.xlsx").Sheets("Sheet1").Cells(i, 2) '상품코드
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(lastRow + i - 1, 2) = Workbooks(Date & "_동신 장바구니.xlsx").Sheets("Sheet1").Cells(i, 5) '수량
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(lastRow + i - 1, 3) = Workbooks(Date & "_동신 장바구니.xlsx").Sheets("Sheet1").Cells(i, 16) '수취인
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(lastRow + i - 1, 4) = "(동신) " & Workbooks(Date & "_동신 장바구니.xlsx").Sheets("Sheet1").Cells(i, 9) '브랜드
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(lastRow + i - 1, 7) = Workbooks(Date & "_동신 장바구니.xlsx").Sheets("Sheet1").Cells(i, 12) '주문번호
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(lastRow + i - 1, 8) = Workbooks(Date & "_동신 장바구니.xlsx").Sheets("Sheet1").Cells(i, 13) '상품주문번호
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(lastRow + i - 1, 9) = Workbooks(Date & "_동신 장바구니.xlsx").Sheets("Sheet1").Cells(i, 14) '방문수령
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(lastRow + i - 1, 10) = Workbooks(Date & "_동신 장바구니.xlsx").Sheets("Sheet1").Cells(i, 10) '단위
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(lastRow + i - 1, 11) = Workbooks(Date & "_동신 장바구니.xlsx").Sheets("Sheet1").Cells(i, 15) '무게확인
Next i
If Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(1, 1) = "" Then
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Rows(1).Delete
End If
lastRow = Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Range("A1:K" & lastRow).Sort key1:=Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Range("G1"), order1:=xlDescending, _
key2:=Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Range("H1"), order2:=xlDescending, Header:=xlNo
i = 2
flag = 0
For h = 1 To lastRow
j = Workbooks(Date & "_장바구니.xlsx").Application.WorksheetFunction.CountIf(Workbooks(Date & "_장바구니.xlsx").ActiveSheet.Range("G1:G" & lastRow), Workbooks(Date & "_장바구니.xlsx").ActiveSheet.Cells(h, 7))
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 1), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15, 24)).Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 1), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15, 24)).Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 1, 1), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 4, 24)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 1, 1) = "네 이 버 (Naver)"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 1, 1).Font.Bold = True
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 1, 1).Font.Size = 30
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 1, 1).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 1, 1).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 1, 1).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 5, 1), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 6, 5)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 5, 1) = "네이버 (Naver)"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 5, 1).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 5, 1).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 7, 1), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 8, 5)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 7, 1) = Left(Workbooks(Date & "_장바구니.xlsx").ActiveSheet.Cells(h, 7), 4) & "." & Mid(Workbooks(Date & "_장바구니.xlsx").ActiveSheet.Cells(h, 7), 5, 2) & "." & Mid(Workbooks(Date & "_장바구니.xlsx").ActiveSheet.Cells(h, 7), 7, 2)
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 7, 1).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 7, 1).HorizontalAlignment = xlLeft
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 9, 1), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 10, 6)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 9, 1) = "주문 번호 : " & Workbooks(Date & "_장바구니.xlsx").ActiveSheet.Cells(h, 7)
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 9, 1).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 9, 1).HorizontalAlignment = xlLeft
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 11, 1), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 12, 4)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 11, 1) = "총 품목수 : " & j
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 11, 1).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 11, 1).HorizontalAlignment = xlLeft
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 11, 11), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 12, 14)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 11, 11) = Month(Date) & "/" & Day(Date) & " Ok!!"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 11, 11).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 11, 11).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 1), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15, 1)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 1) = "순번"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 1).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 1).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 1).Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 1).Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 1).Interior.ColorIndex = 15
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 2), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15, 3)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 2) = "수취인"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 2).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 2).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 2).Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 2).Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 2).Interior.ColorIndex = 15
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 4), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15, 7)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 4) = "품명"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 4).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 4).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 4).Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 4).Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 4).Interior.ColorIndex = 15
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 8), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15, 14)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 8) = "규격"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 8).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 8).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 8).Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 8).Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 8).Interior.ColorIndex = 15
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 15), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15, 17)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 15) = "제조사"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 15).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 15).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 15).Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 15).Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 15).Interior.ColorIndex = 15
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 18), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15, 19)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 18) = "수량"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 18).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 18).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 18).Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 18).Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 18).Interior.ColorIndex = 15
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 20), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15, 21)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 20) = "금액"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 20).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 20).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 20).Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 20).Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 20).Interior.ColorIndex = 15
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 22), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15, 24)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 22) = "배송비"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 22).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 22).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 22).Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 22).Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 22).Interior.ColorIndex = 15
If j > 13 Then
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 11, 18), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 12, 24)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 11, 18) = "현재 페이지 : (" & flag + 1 & " / " & Workbooks(Date & "_발주서 프린트.xlsx").Application.WorksheetFunction.RoundUp(j / 13, 0) & " pages)"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 11, 18).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 11, 18).HorizontalAlignment = xlCenter
If (flag + 1) * 13 < j Then
For k = 1 To 13
Call loopstate(k, i, h, flag)
Next k
Else
For k = 1 To j - flag * 13
Call loopstate(k, i, h, flag)
Next k
End If
Else
For k = 1 To j
Call loopstate(k, i, h, 0)
Next k
End If
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 110, 1), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 111, 4)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 110, 1) = "구매자"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 110, 1).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 110, 1).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 110, 1).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 110, 1).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 110, 1).Interior.ColorIndex = 15
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 110, 5), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 111, 24)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 110, 5) = Workbooks(Date & "_장바구니.xlsx").Application.WorksheetFunction.VLookup(Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(h, 8), Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Range("A:AL"), 3, 0) & " / " & Workbooks(Date & "_장바구니.xlsx").Application.WorksheetFunction.VLookup(Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(h, 8), Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Range("A:AL"), 5, 0)
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 110, 5).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 110, 5).HorizontalAlignment = xlLeft
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 110, 5).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 110, 5).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 1), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 113, 4)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 1) = "수취인"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 1).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 1).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 1).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 1).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 1).Interior.ColorIndex = 15
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 5), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 113, 24)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 5) = Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(h, 3) & " / " & Workbooks(Date & "_장바구니.xlsx").Application.WorksheetFunction.VLookup(Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(h, 8), Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Range("A:AL"), 28, 0) & " / " & Workbooks(Date & "_장바구니.xlsx").Application.WorksheetFunction.VLookup(Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(h, 8), Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Range("A:AL"), 29, 0)
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 5).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 5).HorizontalAlignment = xlLeft
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 5).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 5).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 1), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 116, 4)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 1) = "배송지 주소"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 1).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 1).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 1).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 1).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 1).Interior.ColorIndex = 15
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 5), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 116, 24)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 5).MergeArea.WrapText = True
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 5) = Workbooks(Date & "_장바구니.xlsx").Application.WorksheetFunction.VLookup(Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(h, 8), Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Range("A:AL"), 27, 0)
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 5).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 5).HorizontalAlignment = xlLeft
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 5).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 5).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 1, 1), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 119, 24)).BorderAround 1
If j > 13 Then
If (flag + 1) * 13 < j Then
i = i + 1
h = h + 12
flag = flag + 1
Else
i = i + 1
'h = h + j - 1
h = h + (j - 13 * flag) - 1
flag = 0
End If
Else
i = i + 1 'Workbooks(Date & "_발주서 프린트.xlsx").Application.WorksheetFunction.RoundUp(j / 13, 0)
h = h + j - 1
End If
Next h
Workbooks(Date & "_발주서 프린트.xlsx").Application.PrintCommunication = False
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").PageSetup.FitToPagesTall = False
'Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").PageSetup.firstpagetray = "용지함2"
Workbooks(Date & "_발주서 프린트.xlsx").Application.PrintCommunication = True
'Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").PrintOut Copies:=1, Collate:=True, _
'IgnorePrintAreas:=False
Workbooks(Date & "_발주서 프린트.xlsx").Save
'Workbooks(Date & "_발주서 프린트.xlsx").Close
Workbooks(Date & "_장바구니.xlsx").Close (False)
'Workbooks(Date & "_장바구니.xlsx").Save
Workbooks.Open Filename:=cretecShop
'Workbooks("네이버페이 선택주문조회.xlsx").Save
Workbooks("네이버페이 선택주문조회.xlsx").Close
End Sub
Public Sub loopstate(k As Integer, i As Integer, h As Integer, flag As Integer)
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 1), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15 + k * 7, 1)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 1) = k + (13 * flag)
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 1).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 1).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 1).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 1).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7 + 5, 2), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15 + k * 7, 24)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7 + 5, 2) = "비 고 : " & Replace(Workbooks(Date & "_장바구니.xlsx").Application.WorksheetFunction.VLookup(Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(h + k - 1, 8), Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Range("A:AL"), 30, 0), Chr(10), "")
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7 + 5, 2).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7 + 5, 2).HorizontalAlignment = xlLeft
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7 + 5, 2).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7 + 5, 2).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 2), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 13 + k * 7, 3)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 2).MergeArea.WrapText = True
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 2) = Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(h + k - 1, 3)
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 2).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 2).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 2).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 2).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 4), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 13 + k * 7, 7)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 4).MergeArea.WrapText = True
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 4) = Workbooks(Date & "_장바구니.xlsx").Application.WorksheetFunction.VLookup(Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(h + k - 1, 8), Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Range("A:AL"), 6, 0)
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 4).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 4).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 4).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 8), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 13 + k * 7, 14)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 8).MergeArea.WrapText = True
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 8) = "'" & Workbooks(Date & "_장바구니.xlsx").Application.WorksheetFunction.VLookup(Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(h + k - 1, 8), Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Range("A:AL"), 23, 0)
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 8).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 8).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 8).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 15), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 13 + k * 7, 17)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 15) = Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(h + k - 1, 4)
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 15).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 15).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 15).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 18), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 13 + k * 7, 19)).Merge
If Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(h + k - 1, 10) = "" Then
Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(h + k - 1, 10) = " 개"
End If
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 18) = Workbooks(Date & "_장바구니.xlsx").Application.WorksheetFunction.Text(Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(h + k - 1, 2), "#,###") & " " & Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(h + k - 1, 10)
'Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 18) = Val(Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(h + k - 1, 2))
'Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 18).NumberFormat = "#,###"
'Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 18) = Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 18) & "개"
'Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 18).Text = Format(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 18), "#,###개")
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 18).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 18).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 18).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 20), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 13 + k * 7, 21)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 20) = Workbooks(Date & "_장바구니.xlsx").Application.WorksheetFunction.Text(Workbooks(Date & "_장바구니.xlsx").Application.WorksheetFunction.VLookup(Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(h + k - 1, 8), Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Range("A:AL"), 11, 0), "#,###원")
'Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 20) = Val(Workbooks(Date & "_장바구니.xlsx").Application.WorksheetFunction.VLookup(Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(h + k - 1, 8), Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Range("A:AL"), 10, 0))
'Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 20).NumberFormat = "#,###"
'Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 20) = Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 20) & "원"
'Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 20).Text = Format(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 20), "#,###원")
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 20).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 20).HorizontalAlignment = xlRight
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 20).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 20).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 22), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 13 + k * 7, 24)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 22) = Workbooks(Date & "_장바구니.xlsx").Application.WorksheetFunction.Text(Workbooks(Date & "_장바구니.xlsx").Application.WorksheetFunction.VLookup(Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(h + k - 1, 8), Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Range("A:AL"), 25, 0), "#,###원")
'Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 22) = Val(Workbooks(Date & "_장바구니.xlsx").Application.WorksheetFunction.VLookup(Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(h + k - 1, 8), Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Range("A:AL"), 31, 0))
'Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 22).NumberFormat = "#,###"
'Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 22) = Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 22) & "원"
If Workbooks(Date & "_장바구니.xlsx").Application.WorksheetFunction.VLookup(Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(h + k - 1, 8), Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Range("A:AL"), 24, 0) = "선결제" Then
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 22) = Workbooks(Date & "_장바구니.xlsx").Application.WorksheetFunction.VLookup(Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(h + k - 1, 8), Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Range("A:AL"), 24, 0) & "(" & Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 22) & ")"
Else
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 22) = Workbooks(Date & "_장바구니.xlsx").Application.WorksheetFunction.VLookup(Workbooks(Date & "_장바구니.xlsx").Sheets("Sheet1").Cells(h + k - 1, 8), Workbooks("네이버페이 선택주문조회.xlsx").Sheets("발주발송관리").Range("A:AL"), 24, 0)
End If
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 22).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 22).HorizontalAlignment = xlRight
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 22).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 22).MergeArea.Borders.ColorIndex = 1
End Sub
Private Sub 상품정보등록_Click()
Dim i As Double, j As Double
Dim lastRow
Dim lastRowOrder
Dim goodsInfo As String
'Dim magin As Double
Dim fileStr As String
Dim fileNameStr As String
Application.ScreenUpdating = False
'magin = 1.02
goodsInfo = "상품정보_다운로드"
fileStr = "상품정보_다운로드"
fileNameStr = ThisWorkbook.Path & "\" & fileStr
Workbooks.Open Filename:=fileNameStr
ThisWorkbook.Activate
lastRow = Workbooks(fileStr).Sheets(goodsInfo).Cells(Rows.Count, 1).End(xlUp).Row
lastRowOrder = ThisWorkbook.Sheets("Sheet5").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastRow
If Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 1) Like "EH*" Then
Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 6) = "(한국) " & Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 6)
ElseIf Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 1) Like "ES*" Then
Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 6) = "(상보) " & Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 6)
ElseIf Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 1) Like "EM*" Then
Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 6) = "(무창) " & Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 6)
ElseIf Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 1) Like "ED*" Then
Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 6) = "(동신) " & Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 6)
End If
If Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 39) = "Y" Then
ThisWorkbook.Sheets("Sheet5").Cells(lastRowOrder + 1, 1) = Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 1)
ThisWorkbook.Sheets("Sheet5").Cells(lastRowOrder + 1, 2) = 1
lastRowOrder = lastRowOrder + 1
End If
Next i
ThisWorkbook.Sheets("Sheet5").Range("A1:B" & lastRowOrder + 1).RemoveDuplicates Array(1), xlNo
lastRowOrder = ThisWorkbook.Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row
Workbooks(fileStr).Sheets(goodsInfo).Range("A2:A" & lastRow).Copy ThisWorkbook.Sheets("Sheet3").Range("A" & lastRowOrder + 1 & ":A" & lastRowOrder + lastRow + 1)
Workbooks(fileStr).Sheets(goodsInfo).Range("F2:F" & lastRow).Copy ThisWorkbook.Sheets("Sheet3").Range("B" & lastRowOrder + 1 & ":B" & lastRowOrder + lastRow + 1)
Workbooks(fileStr).Sheets(goodsInfo).Range("W2:X" & lastRow).Copy ThisWorkbook.Sheets("Sheet3").Range("C" & lastRowOrder + 1 & ":D" & lastRowOrder + lastRow + 1)
Workbooks(fileStr).Sheets(goodsInfo).Range("U2:U" & lastRow).Copy ThisWorkbook.Sheets("Sheet3").Range("E" & lastRowOrder + 1 & ":E" & lastRowOrder + lastRow + 1)
ThisWorkbook.Sheets("Sheet3").Range("A1:E" & lastRowOrder + lastRow + 1).RemoveDuplicates Array(1), xlNo
ThisWorkbook.Sheets("Sheet3").Range("A1:E" & lastRowOrder + lastRow + 1).Sort key1:=ThisWorkbook.Sheets("Sheet3").Range("A1"), order1:=xlAscending, Header:=xlNo
ThisWorkbook.Save
'Workbooks(orderProgram).close
'Workbooks(fileStr).Save
Workbooks(fileStr).Close (False)
Application.ScreenUpdating = True
End Sub
Private Sub 생성_Click()
Dim i As Double, j As Double
Dim lastRow
Dim lastRowClear
Dim lastRowOrder
Dim goodsInfo As String
'Dim magin As Double
Dim fileStr As String
Dim fileNameStr As String
Application.ScreenUpdating = False
'magin = 1.02
goodsInfo = "상품정보_다운로드"
fileStr = "상품정보_다운로드"
fileNameStr = ThisWorkbook.Path & "\" & fileStr
Workbooks.Open Filename:=fileNameStr
ThisWorkbook.Activate
lastRow = Workbooks(fileStr).Sheets(goodsInfo).Cells(Rows.Count, 1).End(xlUp).Row
lastRowClear = ThisWorkbook.Sheets("EP File").Cells(Rows.Count, 1).End(xlUp).Row
lastRowOrder = ThisWorkbook.Sheets("Sheet5").Cells(Rows.Count, 1).End(xlUp).Row
j = 2
ThisWorkbook.Sheets("EP File").Range("A2:AY" & lastRowClear).Clear
With Sheets("EP File")
For i = 2 To lastRow
If Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 9) <> "http://contents.cretec.kr/nctx/resource정보없음" Then
.Cells(j, 40) = Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 20) 'minimum_purchase_quantity
.Cells(j, 1) = Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 2) 'id Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 1)
If .Cells(j, 40).Value > 1 Then
If Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 8) = "정보없음" Then
.Cells(j, 2) = "[빠른발송] " & .Cells(j, 40).Text & "개 단위" & " " & Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 6).Text & " " & Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 7).Text & " " & Mid(Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 22).Text, 2, 100) 'title
Else
.Cells(j, 2) = "[빠른발송] " & .Cells(j, 40).Text & "개 단위" & " " & Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 6).Text & " " & Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 7).Text & " " & Mid(Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 8).Text, 2, 100) & " " & Mid(Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 22).Text, 2, 100) 'title
End If
Else
If Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 8) = "정보없음" Then
.Cells(j, 2) = "[빠른발송] " & Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 6).Text & " " & Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 7).Text & " " & Mid(Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 22).Text, 2, 100) 'title
Else
.Cells(j, 2) = "[빠른발송] " & Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 6).Text & " " & Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 7).Text & " " & Mid(Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 8).Text, 2, 100) & " " & Mid(Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 22).Text, 2, 100) 'title
End If
End If
If Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 2) = 1212293 Then
.Cells(j, 2) = "[빠른발송] " & Mid(Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 6).Text, 2, 100) & " " & Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 8).Text & " " & Mid(Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 7).Text, 2, 100) & " " & Mid(Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 22).Text, 2, 100) 'title
End If
'If Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 36) = "Y" Then
'.Cells(j, 2) = "[반품불가]" & .Cells(j, 2).Text
'End If
.Cells(j, 3) = WorksheetFunction.RoundDown(WorksheetFunction.Round(Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 13) * .Cells(j, 40), 0) * 1.1, 0) 'price_pc '.Cells(j, 40).Value *
.Cells(j, 4) = .Cells(j, 3) 'price_mobile
.Cells(j, 5) = .Cells(j, 3) 'normal_price
.Cells(j, 6) = "http://woojunggigi.toolpark.kr/product/product-detail.do?goods_code=" & Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 2) 'link
.Cells(j, 7) = "http://woojunggigi.toolpark.kr/mobile/product-detail.do?goods_code=" & Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 2) 'mobil_link
.Cells(j, 8) = Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 9) 'image_link
'add_image_link
.Cells(j, 10) = Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 10) 'category_name1
.Cells(j, 11) = Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 11) 'category_name2
.Cells(j, 12) = Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 12) 'category_name3
.Cells(j, 13) = Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 7) 'category_name4
.Cells(j, 14) = WorksheetFunction.VLookup(.Cells(j, 12), ThisWorkbook.Sheets("Sheet6").Range(ThisWorkbook.Sheets("Sheet6").Cells(1, 1), ThisWorkbook.Sheets("Sheet6").Cells(322, 2)), 2, 0) 'naver_category
'naver_product_id
.Cells(j, 16) = "신상품" 'condition
'import_flag
'parallel_import
'order_made
'product_flag
'adult
'goods_type
'barcode
'manufacture_define_number
.Cells(j, 25) = Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 8) 'model_number
.Cells(j, 26) = Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 7) 'brand
.Cells(j, 27) = Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 6) 'maker
'origin
'card_event
'event_words
'coupon
'partner_coupon_download
'interest_free_event
'point
'isntallation_costs
Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 8) = Replace(Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 8), "' ", "'")
.Cells(j, 36) = Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 8).Text & "|" & Replace(Replace(Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 8).Text, "-", ""), " ", "|") & _
"|" & Replace(Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 6).Text, " ", "|") & "|" & Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 7).Text & "|" & _
Replace(Replace(Replace(Replace(Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 7).Text, " ", "|"), "-", "|"), "(", "|"), ")", "") '& "|" & Mid(Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 22).Text, 2) 'search_tag
'group_id
'vendor_id
'coordi_id
'.Cells(j, 40) = Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 19) 'minimum_purchase_quantity
'review_count
If Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 25) = "Y" Then
.Cells(j, 42) = "-1" 'shipping
ElseIf Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 39) = "Y" Then
.Cells(j, 42) = "-1" 'shipping
ElseIf Workbooks(fileStr).Sheets(goodsInfo).Cells(i, 38) = "Y" Then
.Cells(j, 42) = "-1" 'shipping
Else
.Cells(j, 42) = "3000" 'shipping
End If
.Cells(j, 43) = "Y" 'delivery_grade
.Cells(j, 44) = "제주,도서산간지역 추가 배송비 발생" '"100000 이상 구매시 무료배송" 'delivery_detail
'attribute
'option_detail
'seller_id
.Cells(j, 48) = "성인" 'age_group
'gender
.Cells(j, 50) = "I" 'class
'update_time
j = j + 1
End If
Next i
End With
ThisWorkbook.Save
'Workbooks(orderProgram).close
Workbooks(fileStr).Close (False)
저장_Click
Application.ScreenUpdating = True
End Sub
Private Sub 저장_Click()
Dim newName As String
Dim rngUsed As Range
newName = ThisWorkbook.Path & "\" & "woojunggigi.txt" '"woojunggigi.xlsx"
If Dir(newName, vbDirectory) = Empty Then
Set rngUsed = Sheets("EP File").Range("A1:AY100001")
'Set oStrm = CreateObject("ADODB.Stream")
Workbooks.Add
rngUsed.Copy ActiveWorkbook.Sheets("Sheet1").Range("A1:AY100001")
Sheets("Sheet1").Name = "woojunggigi"
ActiveWorkbook.SaveAs Filename:=newName, FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Close
Else
MsgBox newName & " 파일은 존재하므로 확인해보세요"
End If
End Sub
Private Sub 비즈마켓_Click()
Dim bizList As String
Dim printBiz As String
Dim lastRow
Dim lastRowMast
Dim i As Integer
Dim j As Integer
Dim h As Integer
Dim k As Integer
Dim flag As Integer
bizList = ThisWorkbook.Path & "\발주현황.xlsx"
printBiz = ThisWorkbook.Path & "\" & Date & "_발주서 프린트.xlsx"
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=printBiz
ActiveWorkbook.Close
Workbooks.Open Filename:=bizList
Workbooks.Open Filename:=printBiz
lastRow = Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(Rows.Count, 8).End(xlUp).Row
For i = 2 To lastRow
If Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(i, 4) = "" Then
Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(i, 4) = Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(i - 1, 4)
Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(i, 5) = Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(i - 1, 5)
Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(i, 6) = Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(i - 1, 6)
End If
If Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(i, 7) = "" Then
Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(i, 7) = Left(Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(i - 1, 7), 6) & "-" & Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(i, 8)
Else
Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(i, 7) = Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(i, 7) & "-" & Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(i, 8)
End If
Next i
Workbooks("발주현황.xlsx").Sheets("출하처리조회").Range("A2:AN" & lastRow).Sort key1:=Workbooks("발주현황.xlsx").Sheets("출하처리조회").Range("AF2"), order1:=xlAscending, _
key2:=Workbooks("발주현황.xlsx").Sheets("출하처리조회").Range("AJ2"), order2:=xlAscending, _
key3:=Workbooks("발주현황.xlsx").Sheets("출하처리조회").Range("G2"), order3:=xlAscending, Header:=xlNo
i = 2
flag = 0
For h = 2 To lastRow
j = Workbooks("발주현황.xlsx").Application.WorksheetFunction.CountIfs(Workbooks("발주현황.xlsx").ActiveSheet.Range("AF2:AF" & lastRow), Workbooks("발주현황.xlsx").ActiveSheet.Cells(h, 32), Workbooks("발주현황.xlsx").ActiveSheet.Range("AJ2:AJ" & lastRow), Workbooks("발주현황.xlsx").ActiveSheet.Cells(h, 36))
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 1), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15, 24)).Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 1), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15, 24)).Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 1, 1), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 4, 24)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 1, 1) = "인터파크 비즈마켓 발 주 서"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 1, 1).Font.Bold = True
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 1, 1).Font.Size = 30
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 1, 1).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 1, 1).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 1, 1).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 5, 1), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 6, 13)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 5, 1) = " 고객사 : " & Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(h, 4)
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 5, 1).Font.Bold = True
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 5, 1).Font.Size = 24
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 5, 1).HorizontalAlignment = xlLeft
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 5, 21), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 6, 24)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 5, 21) = "발주일 : " & Left(Workbooks("발주현황.xlsx").ActiveSheet.Cells(h, 30), 4) & ". " & Mid(Workbooks("발주현황.xlsx").ActiveSheet.Cells(h, 30), 6, 2) & ". " & Mid(Workbooks("발주현황.xlsx").ActiveSheet.Cells(h, 30), 9, 2)
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 5, 21).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 5, 21).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 11, 1), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 12, 4)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 11, 1) = "총 품목수 : " & j
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 11, 1).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 11, 1).HorizontalAlignment = xlLeft
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 1), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15, 2)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 1) = "순번"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 1).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 1).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 1).Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 1).Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 1).Interior.ColorIndex = 15
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 3), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15, 4)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 3) = "품목코드"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 3).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 3).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 3).Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 3).Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 3).Interior.ColorIndex = 15
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 5), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15, 8)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 5) = "품명"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 5).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 5).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 5).Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 5).Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 5).Interior.ColorIndex = 15
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 9), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15, 15)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 9) = "규격"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 9).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 9).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 9).Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 9).Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 9).Interior.ColorIndex = 15
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 16), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15, 17)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 16) = "제조사"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 16).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 16).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 16).Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 16).Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 16).Interior.ColorIndex = 15
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 18), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15, 18)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 18) = "단위"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 18).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 18).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 18).Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 18).Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 18).Interior.ColorIndex = 15
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 19), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15, 20)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 19) = "수량"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 19).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 19).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 19).Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 19).Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 19).Interior.ColorIndex = 15
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 21), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15, 22)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 21) = "단가"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 21).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 21).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 21).Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 21).Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 21).Interior.ColorIndex = 15
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 23), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15, 24)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 23) = "부가세"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 23).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 23).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 23).Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 23).Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 14, 23).Interior.ColorIndex = 15
If j > 13 Then
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 11, 18), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 12, 24)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 11, 18) = "현재 페이지 : (" & flag + 1 & " / " & Workbooks(Date & "_발주서 프린트.xlsx").Application.WorksheetFunction.RoundUp(j / 13, 0) & " pages)"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 11, 18).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 11, 18).HorizontalAlignment = xlCenter
If (flag + 1) * 13 < j Then
For k = 1 To 13
Call LoopOrder(k, i, h, flag)
Next k
Else
For k = 1 To j - flag * 13
Call LoopOrder(k, i, h, flag)
Next k
End If
Else
For k = 1 To j
Call LoopOrder(k, i, h, 0)
Next k
End If
Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(h, 17) = "미포함"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 1), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 113, 4)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 1) = "납품처(회사명)"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 1).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 1).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 1).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 1).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 1).Interior.ColorIndex = 15
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 5), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 113, 24)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 5) = Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(h, 31) & " / " & Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(h, 32) & " / " & Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(h, 33) & " / " & Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(h, 34)
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 5).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 5).HorizontalAlignment = xlLeft
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 5).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 112, 5).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 1), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 116, 4)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 1) = "납품 장소"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 1).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 1).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 1).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 1).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 1).Interior.ColorIndex = 15
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 5), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 116, 24)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 5).MergeArea.WrapText = True
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 5) = Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(h, 36)
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 5).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 5).HorizontalAlignment = xlLeft
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 5).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 114, 5).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 1, 1), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 119, 24)).BorderAround 1
If j > 13 Then
If (flag + 1) * 13 < j Then
i = i + 1
h = h + 12
flag = flag + 1
Else
i = i + 1
'h = h + j - 1
h = h + (j - 13 * flag) - 1
flag = 0
End If
Else
i = i + 1 'Workbooks(Date & "_발주서 프린트.xlsx").Application.WorksheetFunction.RoundUp(j / 13, 0)
h = h + j - 1
End If
Next h
Workbooks(Date & "_발주서 프린트.xlsx").Application.PrintCommunication = False
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").PageSetup.FitToPagesTall = False
'Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").PageSetup.firstpagetray = "용지함2"
Workbooks(Date & "_발주서 프린트.xlsx").Application.PrintCommunication = True
Workbooks(Date & "_발주서 프린트.xlsx").Save
'Workbooks(Date & "_발주서 프린트.xlsx").Close
'Workbooks("발주현황.xlsx").Save
Workbooks("발주현황.xlsx").Close (False)
End Sub
Public Sub LoopOrder(k As Integer, i As Integer, h As Integer, flag As Integer)
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 1), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15 + k * 7, 2)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 1).MergeArea.WrapText = True
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 1) = Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(h + k - 1, 7)
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 1).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 1).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 1).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 1).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7 + 5, 3), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 15 + k * 7, 24)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7 + 5, 3) = "비 고 : " & Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(h + k - 1, 21)
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7 + 5, 3).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7 + 5, 3).HorizontalAlignment = xlLeft
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7 + 5, 3).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7 + 5, 3).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 3), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 13 + k * 7, 4)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 3).MergeArea.WrapText = True
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 3) = Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(h + k - 1, 11) '품목코드
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 3).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 3).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 3).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 3).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 5), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 13 + k * 7, 8)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 5).MergeArea.WrapText = True
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 5) = Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(h + k - 1, 12)
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 5).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 5).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 5).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 9), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 13 + k * 7, 15)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 9).MergeArea.WrapText = True
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 9) = Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(h + k - 1, 13) & " " & Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(h + k - 1, 14)
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 9).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 9).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 9).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 16), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 13 + k * 7, 17)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 16).MergeArea.WrapText = True
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 16) = Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(h + k - 1, 24)
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 16).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 16).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 16).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 18), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 13 + k * 7, 18)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 18) = Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(h + k - 1, 23)
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 18).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 18).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 18).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 19), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 13 + k * 7, 20)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 19) = Val(Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(h + k - 1, 16))
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 19).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 19).NumberFormat = "#,###"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 19).HorizontalAlignment = xlRight
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 19).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 19).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 21), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 13 + k * 7, 22)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 21) = Val(Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(h + k - 1, 18))
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 21).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 21).NumberFormat = "#,###"
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 21).HorizontalAlignment = xlRight
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 21).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 21).MergeArea.Borders.ColorIndex = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Range(Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 23), Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 13 + k * 7, 24)).Merge
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 23) = Workbooks("발주현황.xlsx").Sheets("출하처리조회").Cells(h + k - 1, 17)
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 23).Font.Size = 20
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 23).HorizontalAlignment = xlCenter
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 23).MergeArea.Borders.LineStyle = 1
Workbooks(Date & "_발주서 프린트.xlsx").Sheets("Sheet1").Cells((i - 2) * 119 + 16 + (k - 1) * 7, 23).MergeArea.Borders.ColorIndex = 1
End Sub
지금 코드를 잘 보시면 for 문으로 하나씩 돌아가며 값을 하나씩 입력하고 서식 적용하는데.. 10만개 이상 데이터라면 엑셀 서식 변경을 못해도 1000만번 돌리게됩니다.
가장 베스트는 배열로 값을 쫙 뿌리고, 입력한 값에 서식을 일괄 적용하는 형태로 수정해보시고, 메인 코드 앞에 아래 매크로도 추가해보시면 속도가 많이 개선될겁니다.
꼭 잘 해결하시길 기원합니다..^^