2022-07-17

Sub Exclude_mapping()

Dim a, b, c, d
Dim arr2, arr3, arr1

    Excel.Application.DisplayAlerts = False
    
    a = Excel.Application.WorksheetFunction.CountA(Sheets("TA RTA List").Range("A:A"))
    b = Excel.Application.WorksheetFunction.CountA(Sheets("SIP Program").Range("A:A"))
    c = Excel.Application.WorksheetFunction.CountA(Sheets("Region Sales").Range("A:A"))

    
    Debug.Print a
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Name = "Exclude_mapping"
    Sheets("mapping规则").Rows("1:1").Copy
    Sheets("Exclude_mapping").Paste
    
    arr1 = Sheets("SIP Program").Range("A2:E" & b)
    arr2 = Sheets("Region Sales").Range("A2:G" & c)
    arr3 = Sheets("TA RTA List").Range("A2:M" & a)
    
    Debug.Print arr2(7, 3)
    
    
    For x = 1 To a - 1
        For y = 1 To c - 1
            If arr2(y, 7) <> "" And arr2(y, 6) <> "" And arr2(y, 5) <> "" Then
                If arr3(x, 2) & arr3(x, 3) = arr2(y, 6) & arr2(y, 7) Then
                    Range("Y" & x + 1) = arr3(x, 4)
                    Range("T" & x + 1) = arr3(x, 3)
                    Range("S" & x + 1) = arr3(x, 2)
                    Range("R" & x + 1) = "4A.Postal Code-EndCus/Ship To"
                    If arr2(y, 2) = "" Then
                        Range("F" & x + 1) = arr2(y, 1)
                        Range("I" & x + 1) = "Sales Leaders"
                    Else:
                        Range("F" & x + 1) = arr2(y, 2)
                        Range("I" & x + 1) = "Sales Reps"
                    End If
                    Range("G" & x + 1).NumberFormatLocal = "@"
                    Range("G" & x + 1) = arr2(y, 3)
                    Exit For
               
                End If
            ElseIf arr2(y, 7) = "" And arr2(y, 6) <> "" And arr2(y, 5) <> "" Then
                If arr3(x, 2) = arr2(y, 6) Then
                    Range("Y" & x + 1) = arr3(x, 4)
                    Range("S" & x + 1) = arr3(x, 2)
                    Range("R" & x + 1) = "4A.Postal Code-EndCus/Ship To"
                    If arr2(y, 2) = "" Then
                        Range("F" & x + 1) = arr2(y, 1)
                        Range("I" & x + 1) = "Sales Leaders"
                    Else:
                        Range("F" & x + 1) = arr2(y, 2)
                        Range("I" & x + 1) = "Sales Reps"
                    End If
                    Range("G" & x + 1).NumberFormatLocal = "@"
                    Range("G" & x + 1) = arr2(y, 3)
                    Exit For
                End If
            ElseIf arr2(y, 7) = "" And arr2(y, 6) = "" And arr2(y, 5) <> "" Then
                If arr3(x, 1) = arr2(y, 5) Then
                    Range("Y" & x + 1) = arr3(x, 4)
                    Range("S" & x + 1) = arr3(x, 1)
                    Range("R" & x + 1) = "4D.Region(State)-EndCus/Ship To"
                    If arr2(y, 2) = "" Then
                        Range("F" & x + 1) = arr2(y, 1)
                        Range("I" & x + 1) = "Sales Leaders"
                    Else:
                        Range("F" & x + 1) = arr2(y, 2)
                        Range("I" & x + 1) = "Sales Reps"
                    End If
                    Range("G" & x + 1).NumberFormatLocal = "@"
                    Range("G" & x + 1) = arr2(y, 3)
                    Exit For
                End If
            End If
        Next
    Next
    
    Range("A2 :A" & a) = "CN"
    Range("C2 :C" & a) = "HCBG"
    Range("D2 :D" & a) = "OCSD"
    Range("E2 :E" & a) = "EF"
    Range("J2 :K" & a) = "M1"
    Range("K2 :K" & a) = "POS"
    Range("P2 :P" & a) = "3C-POS-All End Customer"
    Range("N2 :N" & a) = "2C-All Sold To-Sales Org."
    Range("U2 :U" & a) = "5C-Profit Center"
    Range("V2 :V" & a) = "3140"
    Range("AA2 :AA" & a) = "2022/1/1"
    Range("AB2 :AB" & a) = "2022/12/31"
    
    For x = 2 To a
        For y = 1 To b - 1
            If Range("F" & x) = arr1(y, 2) Then
                'Range("G" & x).NumberFormatLocal = "@"
                'Range("G" & x) = arr1(y, 3)
                If Range("J" & x) = arr1(y, 5) Then
                    Range("H" & x) = arr1(y, 4)
                End If
            End If
        Next
    Next
            
    
    

End Sub