엑셀이 갑자기 느려졌습니다. (feat. xlsm)
VBA
작성자
툴잇지 with 우정기기
작성일
2022-02-04 16:54
조회
1132
오늘부터 갑자기 엑셀이 느려졌습니다.
파일 열고 닫기도 확연히 차이 날 정도로 느려졌고 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
회원등급 : 4레벨
포인트 : 166 EP
총질문 : 5 개 (마감율 : 80%)
채택답변 : 0 개
전체 18,532
| 번호 | 카테고리 | 제목 | 작성자 | 작성일 | 추천 | 조회 |
|
[📚진짜쓰는 실무엑셀] IT/오피스 '1위' 베스트셀러! 엑셀 공부, 이 교재로 마스터하세요! (315)
  오빠두엑셀
|
2022.02.03
|
추천 575
|
조회 609711
|
오빠두엑셀
|
2022.02.03 | 575 | 609711 | ||
| 공지사항 | 문서서식 |
⭐ [더 나은 커뮤니티 문화를 위한 Q&A 글 작성 규칙] ⭐
(197)
오빠두엑셀
|
2021.10.28
|
추천 310
|
조회 35541
|
오빠두엑셀
|
2021.10.28 | 310 | 35541 |
| 78582 | 함수/공식 |
New 조건 함수 의 관한 문의
  (4)
답변완료
 예쁜나야
|
15:46
|
추천 0
|
조회 36
|
예쁜나야
|
15:46 | - | 36 |
| 78580 |
New 날짜와 시간 분할 방법 알려 주세요
(1)
 계양산 지기
|
15:14
|
추천 0
|
조회 25
|
계양산 지기
|
15:14 | - | 25 | |
| 78575 | VBA |
New 엑셀 재고관리 프로그램 문의드립니다..
(1)
 SJ
|
04:23
|
추천 0
|
조회 65
|
SJ
|
04:23 | - | 65 |
| 78570 | 대시보드 |
New 대시보드에 표 형식으로 표현도 가능한가요?
(2)
룰루랄라
|
2025.05.12
|
추천 0
|
조회 68
|
룰루랄라
|
2025.05.12 | - | 68 |
| 78569 | 함수/공식 |
New 서로 다른 양식의 견적서 엑셀 파일 한곳에 정리 하기
(5)
gh****
|
2025.05.12
|
추천 0
|
조회 96
|
gh****
|
2025.05.12 | - | 96 |
| 78565 | 함수/공식 |
New 검색보고서 만들기 질문드립니다.
(9)
답변완료
만식이햄
|
2025.05.12
|
추천 0
|
조회 82
|
만식이햄
|
2025.05.12 | - | 82 |
| 78563 | VBA |
New VBA 수정 부탁드립니다. ㅠ
(3)
서비
|
2025.05.12
|
추천 0
|
조회 73
|
서비
|
2025.05.12 | - | 73 |
| 78560 | 함수/공식 |
New 어떻게 내용을 가져올수 있을지 고수님들의 해결방법들을 공유 받고 싶습니다!!....
(2)
꾸르형
|
2025.05.12
|
추천 0
|
조회 43
|
꾸르형
|
2025.05.12 | - | 43 |
| 78559 | 파워쿼리/피벗 |
New 엑셀 자동화 질문 입니다...
(2)
답변완료
안녕하세여11
|
2025.05.12
|
추천 0
|
조회 65
|
안녕하세여11
|
2025.05.12 | - | 65 |
| 78555 | 함수/공식 |
New 시트에서 원하는 열의 자료만 가져오고싶은데 아무리 해도 수식오류가 나서 도움을 구하고자 합니다
(4)
답변완료
 khs****
|
2025.05.12
|
추천 0
|
조회 41
|
khs****
|
2025.05.12 | - | 41 |
| 78554 | 함수/공식 |
New 변동하는 절대참조값 적용
(6)
답변완료
화이또!
|
2025.05.12
|
추천 0
|
조회 64
|
화이또!
|
2025.05.12 | - | 64 |
| 78553 | 대시보드 |
New 대시보드_슬라이서 자동으로 눌러지게 하는방법
(1)
검도림
|
2025.05.12
|
추천 0
|
조회 46
|
검도림
|
2025.05.12 | - | 46 |
| 78548 | 대시보드 |
New 어떤 엑셀을 적용해야하는 지 문의드립니다!
(1)
룰루랄라
|
2025.05.11
|
추천 0
|
조회 64
|
룰루랄라
|
2025.05.11 | - | 64 |
| 78546 | 함수/공식 |
New 월별 매출액 자동입력할 수 있는 방법 또는 함수 궁금합니다!
(2)
mmm1010
|
2025.05.11
|
추천 0
|
조회 84
|
mmm1010
|
2025.05.11 | - | 84 |
| 78544 | 함수/공식 |
New 중복값 제거 함수에 관하여
(1)
답변완료
김지훈(Derek)
|
2025.05.11
|
추천 0
|
조회 74
|
김지훈(Derek)
|
2025.05.11 | - | 74 |
| 78543 | 함수/공식 |
New 데이터 추출 및 정렬관련 초보질문
(5)
답변완료
김지훈(Derek)
|
2025.05.11
|
추천 0
|
조회 67
|
김지훈(Derek)
|
2025.05.11 | - | 67 |
| 78540 | 함수/공식 |
New 오빠두LIVE 219회 - 엑셀 '셀 병합' 자동화 보고서 만들기 오빠두엑셀님의 방법이 궁금합니다.
AURIC
|
2025.05.10
|
추천 0
|
조회 57
|
AURIC
|
2025.05.10 | - | 57 |
| 78536 | 함수/공식 |
New 월별로 금액적용후 최종월에 차액을 가감 함수가 필요해요
(1)
답변완료
낮은자
|
2025.05.10
|
추천 0
|
조회 62
|
낮은자
|
2025.05.10 | - | 62 |
| 78535 | 기능/도구 |
New 엑셀로 25x25 인쇄가 가능한가요?
(1)
망나
|
2025.05.10
|
추천 0
|
조회 47
|
망나
|
2025.05.10 | - | 47 |
| 78534 | 파워쿼리/피벗 |
New 쿼리에 함수 적용한 열 추가 시 24:00:00 활용한 계산 함수 필요할 때.
(2)
답변완료
신노스케
|
2025.05.10
|
추천 0
|
조회 62
|
신노스케
|
2025.05.10 | - | 62 |
| 78531 | 차트/그래프 |
New 엑셀 차트 데이터테이블
(1)
쩡
|
2025.05.09
|
추천 0
|
조회 72
|
쩡
|
2025.05.09 | - | 72 |
| 78530 | 구글시트 |
New 구글 스프레드시트 함수 관련 질문입니다.
(2)
동그이
|
2025.05.09
|
추천 0
|
조회 61
|
동그이
|
2025.05.09 | - | 61 |
| 78528 | 구글시트 |
New 구글 스프레드시트를 외부 엑셀 파일과 연동하는 방법이 궁금합니다.
(3)
머우터덩
|
2025.05.09
|
추천 0
|
조회 65
|
머우터덩
|
2025.05.09 | - | 65 |
| 78525 | 문서서식 |
New 업무가 진행이 안 될 정도로, 해당 파일이 느립니다!
(2)
빌스택스
|
2025.05.09
|
추천 0
|
조회 90
|
빌스택스
|
2025.05.09 | - | 90 |
| 78520 | 기능/도구 |
New 하이퍼링크 명칭 해제
(1)
 min
|
2025.05.09
|
추천 0
|
조회 49
|
min
|
2025.05.09 | - | 49 |
| 78518 | 피벗테이블 |
New 피벗테이블 관련 화살표 질문
이모비니
|
2025.05.09
|
추천 0
|
조회 47
|
이모비니
|
2025.05.09 | - | 47 |
| 78516 | 함수/공식 |
New 호봉산정 엑셀 문의드립니다.
(2)
답변완료
 아르카디안
|
2025.05.08
|
추천 0
|
조회 55
|
아르카디안
|
2025.05.08 | - | 55 |
| 78515 | 함수/공식 |
New 엑셀 질문 드립니다.ㅠ.ㅠ
(1)
숲의사제
|
2025.05.08
|
추천 0
|
조회 53
|
숲의사제
|
2025.05.08 | - | 53 |
| 78511 | 함수/공식 |
New 입사일자로 부터 매년 알림
(2)
국빱
|
2025.05.08
|
추천 0
|
조회 66
|
국빱
|
2025.05.08 | - | 66 |
| 78510 | 문서서식 |
New 조건부 서식 적용 문의
(2)
답변완료
장수벌레
|
2025.05.08
|
추천 0
|
조회 99
|
장수벌레
|
2025.05.08 | - | 99 |
@툴잇지 with 우정기기 님 코드가 너무 길어서.. 일일히 다 살펴보진 못했지만 우선 가장 눈에 들어오는건 서식변경 코드들이네요..
지금 코드를 잘 보시면 for 문으로 하나씩 돌아가며 값을 하나씩 입력하고 서식 적용하는데.. 10만개 이상 데이터라면 엑셀 서식 변경을 못해도 1000만번 돌리게됩니다.
가장 베스트는 배열로 값을 쫙 뿌리고, 입력한 값에 서식을 일괄 적용하는 형태로 수정해보시고, 메인 코드 앞에 아래 매크로도 추가해보시면 속도가 많이 개선될겁니다.
꼭 잘 해결하시길 기원합니다..^^
@더블유에이 님 답변감사합니다. 배열함수를 연구해 봐야겠네요 ㅜㅜ
@툴잇지 with 우정기기 님 이런 부분들이고.. 여기 홈페이지에 ArrayToRng 함수가 있습니다. 배열을 범위로 뿌려주는 함수인데.. 한번 연구해보세요