Variant 변수에 날짜데이타를 넣을 때

Option Explicit

Public wsDATA As Worksheet, wsPRODUCT As Worksheet, wsCUSTOMER As Worksheet, wsPACK As Worksheet
Public tblDATA As ListObject, tblPRODUCT As ListObject, tblCUSTOMER As ListObject, tblPACK As ListObject
Public wsINPUT As Worksheet, wsCONFIG As Worksheet

Sub UseSheet(ParamArray Args() As Variant)

    If UBound(Args) = -1 Then MsgBox "변수가 없습니다.": Exit Sub
    
    Dim arg As Variant
    
    For Each arg In Args
        Select Case arg
        Case "DATA"
            Set wsDATA = ThisWorkbook.Sheets("2023")
            Set tblDATA = wsDATA.ListObjects("DATA2023")
        Case "PRODUCT"
            Set wsPRODUCT = ThisWorkbook.Sheets("제품")
            Set tblPRODUCT = wsPRODUCT.ListObjects("제품List")
        Case "CUSTOMER"
            Set wsCUSTOMER = ThisWorkbook.Sheets("업체")
            Set tblCUSTOMER = wsCUSTOMER.ListObjects("업체List")
        Case "PACK"
            Set wsPACK = ThisWorkbook.Sheets("제품Pack")
            Set tblPACK = wsPACK.ListObjects("제품Pack")
        Case "INPUT"
            Set wsINPUT = ThisWorkbook.Sheets("입력")
        Case "CONFIG"
            Set wsCONFIG = ThisWorkbook.Sheets("설정")
        Case Else
            MsgBox "없는 테이블입니다."
        End Select
    Next arg

End Sub


Function MatchColumnIndex(Args() As Variant) As Variant
                                                                                    ' DATA표의 항목을 배열로 받아서 column index 를 1부터 시작하는 배열로 반환
    If UBound(Args) = -1 Then MsgBox "변수가 없습니다.": Exit Function

    Call UseSheet("DATA")
    
    Dim i As Integer, idxNo() As Variant
    ReDim idxNo(1 To UBound(Args) + 1)
    
    For i = 1 To UBound(Args) + 1
        idxNo(i) = WorksheetFunction.Match(Args(i - 1), tblDATA.HeaderRowRange, 0)
    Next i

    MatchColumnIndex = idxNo

End Function



Function DeDuplication(Fday As Date, Eday As Date, colName() As Variant) As Variant
    
    Call UseSheet("DATA")
    
    Dim ColIdxNo As Variant
    Dim endRow As Long
    Dim endIndex As Long
    Dim result() As String, aTemp() As String, sTemp As String
    Dim isDuplicate As Boolean
    Dim i As Long, j As Integer, k As Long
    Dim r As Long, c As Integer
    Dim vDay As Variant
    Dim tmp() As Variant
    tmp = colName
    ColIdxNo = MatchColumnIndex(tmp)
    
    endRow = tblDATA.DataBodyRange.Rows.Count
    vDay = tblDATA.ListColumns("날짜").DataBodyRange.Value
    
    c = UBound(ColIdxNo)
    
    ReDim aTemp(1 To c)                            ' 한 행의 항목을 담을 배열, 여기서 aTemp의 인덱스는 1로 시작한다.
    
    endIndex = 1
    ReDim result(endIndex)
    
    result(0) = Join(colName, "|")                  ' 0번에 제목줄을 넣는다. 중복검사시 에러발생 방지용.
    isDuplicate = False

    For i = 1 To endRow
        If vDay(i, 1) >= Fday And vDay(i, 1) <= Eday Then
            For j = 1 To c
                aTemp(j) = tblDATA.DataBodyRange.Cells(i, ColIdxNo(j)).Text      ' DATA표의 선택된 항목을 한 행씩 배열로 만들고
            Next j
            sTemp = Join(aTemp, "|")                                        ' 합친다.
            isDuplicate = False
            
            For k = endIndex To 1 Step -1
                If sTemp = result(k - 1) Then isDuplicate = True: Exit For    ' 중복검사
            Next k
            If Not isDuplicate Then
                ReDim Preserve result(endIndex)                ' 합쳐서 1차원배열로 만드는 이유. 다차원배열은 마지막 차원(2차원이면 열방향)만 preserve.
                result(endIndex) = sTemp
                endIndex = endIndex + 1
            End If
        ElseIf vDay(i, 1) > Eday Then Exit For                  ' 표가 날짜정렬된 상태이므로 더 이상은 루프 돌릴 필요 없음.
            
        End If
    Next i

    r = UBound(result)
    
    Dim Uniq As Variant
    ReDim Uniq(1 To r, 1 To c)
        
    For i = 1 To r
        aTemp = split(result(i), "|")                   ' split으로 분리했으므로 aTemp 의 인덱스는 0으로 시작한다.
        Uniq(i, 1) = left(aTemp(0),10)                 ' 날짜는 항상 첫번째에 있어야 한다.
        For j = 2 To c
            Uniq(i, j) = aTemp(j - 1)
        Next j
    Next i
Stop
    DeDuplication = Uniq
    
End Function


Sub 임시run()

    Dim 첫날 As Date, 끝날 As Date
    Dim 제목()
    Dim 결과
    
    첫날 = #12/31/2022#
    끝날 = #11/1/2023#
    
    제목 = Array("날짜", "주문번호", "업체", "제품")
    
    결과 = DeDuplication(첫날, 끝날, 제목)
    

    Dim head As Range, body As Range
    Set head = ThisWorkbook.Sheets("temp").Range("A2").Resize(1, UBound(제목) + 1)
    Set body = ThisWorkbook.Sheets("temp").Range("A3").Resize(UBound(결과), UBound(제목) + 1)
    
    ThisWorkbook.Sheets("temp").Cells.Clear
    head = 제목
    body = 결과
    body.Columns.AutoFit
    body.Cells.Font.Size = 9
    
End Sub

        
    For i = 1 To r
        aTemp = split(result(i), "|")                   ' split으로 분리했으므로 aTemp 의 인덱스는 0으로 시작한다.

        For j = 1 To c
            Uniq(i, j) = aTemp(j - 1)
        Next j
    Next i
Stop
    DeDuplication = Uniq
    
End Function